Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
Есть некая таблица вида:
id data_time param_id param_val
1 17.02.2012 1:43 1 43
2 18.02.2012 0:42 1 46
3 18.02.2012 0:41 2 33
4 01.03.2012 23:41 1 47
5 16.03.2012 23:40 1 49
6 28.03.2012 22:40 1 55
... ... ... ...


Требуется получить (допустим только param_id=1) максимальную и минимальную дату за каждый месяц и соответствующий параметр.
data_time_max param_val_0 data_time_min param_val_1
17.02.2012 1:43 43 18.02.2012 0:42 46
01.03.2012 23:41 47 28.03.2012 22:40 55
27 фев 12, 10:19    [12155139]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
какая версия сервера?
какого именно "вида табличка" - какие поля уникальны?
27 фев 12, 10:54    [12155300]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
Зайцев Фёдор,

CREATE TABLE astral_db.dbo.flowmeter_data (
  id bigint IDENTITY,
  flowmeter_parameter_id bigint NOT NULL,
  device_id bigint NOT NULL,
  [data] varbinary(100) NOT NULL,
  date_time datetime NOT NULL,
  CONSTRAINT PK_flowmeter_data PRIMARY KEY (id),
  CONSTRAINT FK_flowmeter_data_device FOREIGN KEY (device_id) REFERENCES dbo.device (id),
  CONSTRAINT FK_flowmeter_data_flowmeter_parameter FOREIGN KEY (flowmeter_parameter_id) REFERENCES dbo.flowmeter_parameter (id)
)


Версия сервера Microsoft SQL Server Express Edition (64-bit) 10.50.1600.1
27 фев 12, 11:15    [12155433]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
мин и макс дата
Guest
что-то типа такого

declare @t table (id int identity(1,1), data_time datetime, param_id int, param_val int)

insert into @t(data_time, param_id, param_val) values
('20120217 01:43', 1, 43), 
('20120218 00:42', 1, 46), 
('20120218 00:41', 2, 33 ),
('20120301 23:41', 1, 47 ),
('20120316 23:40', 1, 49 ),
('20120328 22:40', 1, 55 )


; with
	cte as (
		select 
			min(t.data_time) mind
			, max(t.data_time) maxd 
		from 
			@t t
		group by
			year(t.data_time)
			, month(t.data_time)
	)
select
	c.mind
	, max(case when t.data_time = c.mind then t.param_val else null end)
	, c.maxd
	, max(case when t.data_time = c.maxd then t.param_val else null end)
from
	@t t
inner join cte c on c.mind = t.data_time or c.maxd = t.data_time
group by
	c.mind
	, c.maxd
27 фев 12, 16:29    [12158389]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
мин и макс дата,

Это круто! Сам до такого ни в жизнь не додумался бы!
Как только разберусь как это работает - выложу описание.
27 фев 12, 22:27    [12160672]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
 with
	cte as (
		select 
			min(t.date_time) as mind, max(t.date_time) as maxd
		from 
			astral_db.dbo.flowmeter_data as t 
		where t.device_id=3 AND t.flowmeter_parameter_id=3
		group by
			year(t.date_time), month(t.date_time)
	)
 select * from (
select
	c.mind,
	max(case when t.date_time = c.mind and t.device_id=3 AND t.flowmeter_parameter_id=3 then sys.fn_sqlvarbasetostr(t.[data]) else null end) as minda,
	c.maxd,
	max(case when t.date_time = c.maxd and t.device_id=3 AND t.flowmeter_parameter_id=3 then sys.fn_sqlvarbasetostr(t.[data]) else null end) as maxda
from
	astral_db.dbo.flowmeter_data as t 
inner join cte as c on (c.mind = t.date_time or c.maxd = t.date_time) 
group by
	c.mind , c.maxd
	) as ttt order by mind


Выполняется 10 сек. =(
3 мар 12, 17:48    [12188736]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
конечно ибо

group by year(t.date_time), month(t.date_time)


заставляет сканить нафик все
3 мар 12, 18:08    [12188763]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
Crimean
конечно ибо

group by year(t.date_time), month(t.date_time)


заставляет сканить нафик все


Если верить SQL Server Management Studio, то 98% тратится на join
5 мар 12, 14:42    [12196474]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
Red2
 with
	cte as (
		select 
			min(t.date_time) as mind, max(t.date_time) as maxd
		from 
			astral_db.dbo.flowmeter_data as t 
		where t.device_id=3 AND t.flowmeter_parameter_id=3
		group by
			year(t.date_time), month(t.date_time)
	)
 select * from (
select
	c.mind,
	max(case when t.date_time = c.mind and t.device_id=3 AND t.flowmeter_parameter_id=3 then sys.fn_sqlvarbasetostr(t.[data]) else null end) as minda,
	c.maxd,
	max(case when t.date_time = c.maxd and t.device_id=3 AND t.flowmeter_parameter_id=3 then sys.fn_sqlvarbasetostr(t.[data]) else null end) as maxda
from
	astral_db.dbo.flowmeter_data as t 
inner join cte as c on (c.mind = t.date_time or c.maxd = t.date_time) 
group by
	c.mind , c.maxd
	) as ttt order by mind


Выполняется 10 сек. =(

а так?
select yy, mm, min(date_time) as mind, max(date_time) as maxd
, max(minda_) as minda, max(maxda_) as maxda
from 
(
select year(t.date_time) as yy, month(t.date_time) as mm, t.date_time,
case when t.date_time = min(t.date_time)over(partition by year(t.date_time), month(t.date_time))
          and t.device_id=3 AND t.flowmeter_parameter_id=3 
     then sys.fn_sqlvarbasetostr(t.[data]) else null end as minda_,
case when t.date_time = max(t.date_time)over(partition by year(t.date_time), month(t.date_time))
          and t.device_id=3 AND t.flowmeter_parameter_id=3 
     then sys.fn_sqlvarbasetostr(t.[data]) else null end as maxda_,
from astral_db.dbo.flowmeter_data as t 
) as ttt group by yy, mm
PS не проверял, но вроде бы в синтаксисе нигде не наврал.
5 мар 12, 15:21    [12196879]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
qwrqwr
а так?
select yy, mm, min(date_time) as mind, max(date_time) as maxd
, max(minda_) as minda, max(maxda_) as maxda
from 
(
select year(t.date_time) as yy, month(t.date_time) as mm, t.date_time,
case when t.date_time = min(t.date_time)over(partition by year(t.date_time), month(t.date_time))
          and t.device_id=3 AND t.flowmeter_parameter_id=3 
     then sys.fn_sqlvarbasetostr(t.[data]) else null end as minda_,
case when t.date_time = max(t.date_time)over(partition by year(t.date_time), month(t.date_time))
          and t.device_id=3 AND t.flowmeter_parameter_id=3 
     then sys.fn_sqlvarbasetostr(t.[data]) else null end as maxda_,
from astral_db.dbo.flowmeter_data as t 
) as ttt group by yy, mm
PS не проверял, но вроде бы в синтаксисе нигде не наврал.


А так ругается на запятую перед последним from, не фильтрует по "t.device_id=3 AND t.flowmeter_parameter_id=3" и отрабатывает за 27 сек. :(
11 мар 12, 17:15    [12226781]     Ответить | Цитировать Сообщить модератору
 Re: help!: Как выбрать мин и макс дату и соотв. значение за каждый месяц?  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Red2,

удалить запятую мешает авторское право?
11 мар 12, 17:26    [12226871]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить