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

Откуда:
Сообщений: 298
Здравствуйте!
Подскажите как реализовать запрос, в котором в зависимости от времени в одной колонке посчитать разницу значений в другой колонке.
Пример:
Таблица c данными:
GUID,    Time,         Pulse
1A, 2014-07-16 13:20:09,  75
2A, 2014-06-04 15:41:19,  90
3A, 2014-06-04 15:36:42,  65
4A, 2014-06-04 15:28:11,  56

После запроса в четвертой колонке DeltaPulse будут следующие данные:
GUID,    Time,          Pulse,  DeltaPulse
1A, 2014-07-16 13:20:09,  75,      -15
2A, 2014-06-04 15:41:19,  90,       25
3A, 2014-06-04 15:36:42,  65,       9
4A, 2014-06-04 15:28:11,  56,       0

Где DeltaPulse рассчитывается в зависимости от колонки Time таким образом:
1A  DeltaPulse = 1A - 2A
2A  DeltaPulse = 2A - 3A
3A  DeltaPulse = 3A - 4A
4A  DeltaPulse = 4A - 4A

Т.е. это разница между ближайшими значениями по времени. Таблица отсортирована по колоне Time DESC.
Самое ранее значение по времени (4A) всегда 0.
26 сен 14, 14:58    [16626359]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4813
Scend,
Версия SQL Server какая?
26 сен 14, 15:05    [16626425]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Scend
Member

Откуда:
Сообщений: 298
Версия SQL Server 2008.
26 сен 14, 15:07    [16626439]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8485
Объедините таблицу с собой со смещением в 1 запись по порядку.
26 сен 14, 15:18    [16626543]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
_stas_k_
Member

Откуда:
Сообщений: 46
declare @table0 table([GUID] varchar(2), [time] datetime, pulse int, deltapulse int)

insert into @table0([GUID], [time], pulse)
select '1A', '2014-07-16 13:20:09',  75
insert into @table0([GUID], [time], pulse)
select '2A', '2014-06-04 15:41:19',  90
insert into @table0([GUID], [time], pulse)
select '3A', '2014-06-04 15:36:42',  65
insert into @table0([GUID], [time], pulse)
select '4A', '2014-06-04 15:28:11',  56

;with tbl as (
	select ROW_NUMBER() over (order by [GUID]) as rn, [GUID], [time], pulse
	from @table0
)
select
	t1.[GUID], t1.[time], t1.pulse,
	case when t2.pulse is null then 0 else t1.pulse - t2.pulse end
from tbl t1 left join tbl t2 on t2.rn = t1.rn + 1
26 сен 14, 15:21    [16626573]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4813
_stas_k_
	case when t2.pulse is null then 0 else t1.pulse - t2.pulse end -> ISNULL(t1.pulse - t2.pulse, 0)


В остальном такой вариант придётся использовать. Можно оптимизировать, индексы сделать. Переходите на SQL 2012 используйте LAG LEAD
26 сен 14, 15:39    [16626736]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Glory
Member

Откуда:
Сообщений: 104751
select a.[GUID], a.[time], a.pulse, a.pulse - (select top 1 b.pulse from @table0 b where b.[time] < a.[time] order by b.[time] desc) as x
 from @table0 a
26 сен 14, 15:44    [16626780]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Scend
Member

Откуда:
Сообщений: 298
Спасибо! Оба варианта работают.
В варианте Glory также лучше добавить ISNULL.
26 сен 14, 16:43    [16627140]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Glory
Member

Откуда:
Сообщений: 104751
Scend
Оба варианта работают.

Только с разным потреблением ресурсов
26 сен 14, 16:44    [16627144]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Scend
Member

Откуда:
Сообщений: 298
Немного усложнив, добавил колонку 'person'. Т.е. в таблице значения для разных людей.
Пытаюсь вычислить все тоже самое:
declare @table0 table([GUID] varchar(2), [time] datetime, pulse int, person varchar(2), deltapulse int)

insert into @table0([GUID], [time], pulse, person)
select '1A', '2014-07-04 13:20:09',  75, '1B'
insert into @table0([GUID], [time], pulse, person)
select '2A', '2014-06-04 15:41:19',  90, '1B'
insert into @table0([GUID], [time], pulse, person)
select '3A', '2014-06-04 15:36:42',  65, '1B'
insert into @table0([GUID], [time], pulse, person)
select '4A', '2014-06-04 15:28:11',  56, '1B'
insert into @table0([GUID], [time], pulse, person)
select '5A', '2014-05-04 15:36:42',  99, '1C'
insert into @table0([GUID], [time], pulse, person)
select '6A', '2014-05-04 15:28:11',  60, '1C'
SELECT * FROM @table0
;with tbl as (
	select ROW_NUMBER() over (order by [GUID]) as rn, [GUID], [time], pulse, person
	from @table0
)
select
	t1.[GUID], t1.[time], t1.pulse, t1.person,
	case when t2.pulse is null then 0 else t1.pulse - t2.pulse end
from tbl t1 left join tbl t2 on t2.rn = t1.rn + 1


И второй вариант:
select a.[GUID], a.[time], a.pulse, a.person, a.pulse - (select top 1 b.pulse from @table0 b where b.[time] < a.[time] order by b.[time] desc) as x
 from @table0 a

Результат получается такой:
GUID	time	          pulse person	deltapulse
1A	2014-04-07 13:20:09.000	75	1B	-15
2A	2014-04-06 15:41:19.000	90	1B	25
3A	2014-04-06 15:36:42.000	65	1B	9
4A	2014-04-06 15:28:11.000	56	1B	-43
5A	2014-04-05 15:36:42.000	99	1C	39
6A	2014-04-05 15:28:11.000	60	1C	0

Т.е. считает подряд по всем записям, что не совсем верно - в строке 4А должен быть 0, т.к. это новый человек(1B) а там стоит -43.

Как сделать, чтобы подсчет был по отдельным персонам:
GUID	time	          pulse person	deltapulse
1A	2014-04-07 13:20:09.000	75	1B	-15
2A	2014-04-06 15:41:19.000	90	1B	25
3A	2014-04-06 15:36:42.000	65	1B	9
4A	2014-04-06 15:28:11.000	56	1B	0
5A	2014-04-05 15:36:42.000	99	1C	39
6A	2014-04-05 15:28:11.000	60	1C	0

Т.е. у каждой новой персоны значение deltapulse равнялось 0.
29 сен 14, 09:02    [16632385]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3027
Scend, ну так или в ROW_NUMBER или в подзапросе сделайте и условие на person
откройте БОЛ и включите мозг
29 сен 14, 09:52    [16632497]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Glory
Member

Откуда:
Сообщений: 104751
Scend
Т.е. считает подряд по всем записям, что не совсем верно - в строке 4А должен быть 0, т.к. это новый человек(1B) а там стоит -43.

И что мешает добавить в подзапрос сравнение по person ?
29 сен 14, 10:05    [16632548]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Scend
Member

Откуда:
Сообщений: 298
Glory,
Выбираю минимальные значения для person,
select MIN (a.[time]),a.person
 from @table0 a
 group by a.person

а вот как добавить в подзапрос, чтобы для этих значений проставляло 0 не пойму.
29 сен 14, 10:15    [16632588]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Glory
Member

Откуда:
Сообщений: 104751
Scend
Выбираю минимальные значения для person,

Зачем ?
29 сен 14, 10:17    [16632602]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Разница в зависимости от времени  [new]
Scend
Member

Откуда:
Сообщений: 298
Glory,
Сделал так
select a.[GUID], a.[time], a.pulse, a.person, ISNULL(a.pulse - (select top 1 b.pulse from @table0 b where b.[time] < a.[time] and b.person = a.person order by b.[time] desc),0) as x
 from @table0 a
29 сен 14, 10:37    [16632686]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить