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

Откуда: Харьков
Сообщений: 6
Суть вопроса в следующем.
Необходимо посчитать почасовые данные за определенный период, например за сутки. Для решения данной задачи создал запрос:
SELECT  Dev_ID, 

	DATETIMEFROMPARTS(YEAR(TS), MONTH(TS), DAY(TS), DATEPART(HOUR, TS), 0, 0, 0)    AS [TS]
	,Max([ActEnergy_Pos]) maxActEnergy_Pos
	,Min([ActEnergy_Pos]) AS minActEnergy_Pos
	,(Max([ActEnergy_Pos]) - Min([ActEnergy_Pos]))SumActEnergy 
						
FROM [dbo].[t_Input]

WHERE   (TS BETWEEN '2019-02-11' AND '2019-02-12') and ActEnergy_Pos > 0 and Dev_ID = 1
group by DATETIMEFROMPARTS(YEAR(TS), MONTH(TS), DAY(TS), DATEPART(HOUR, TS), 0, 0, 0) , Dev_ID
ORDER BY TS

Получаю, на первый взгляд, нормальный результат вида:

Dev_ID TS maxActEnergy_Pos minActEnergy_Pos SumActEnergy
1 2019-02-11 00:00:00.000 720867.88 720713.06 154.82
1 2019-02-11 01:00:00.000 721040,19 720877,25 162,94
1 2019-02-11 02:00:00.000 721210,81 721050,25 160,56
1 2019-02-11 03:00:00.000 721384,13 721223,06 161,07
1 2019-02-11 04:00:00.000 721557,13 721394,44 162,69
1 2019-02-11 05:00:00.000 721726,38 721566,06 160,32
1 2019-02-11 06:00:00.000 721892,44 721737,88 154,56
1 2019-02-11 07:00:00.000 722059,69 721903 156,69
........
По факту получается, что итоговый подсчет не верен, так как на границах диапазонов данные не учитываются, например
maxActEnergy_Pos в 00:00 = 720867.88,
minActEnergy_Pos в 01:00 = 720877,25
между ними есть еще около 10, и сума по диапазонам не равна общей разнице за период.
И вот не могу решить эту задачу...
Есть какие-то идеи как мне быть?
21 фев 19, 13:35    [21816291]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
svalex17,

'2019-02-12' это '2019-02-12 00:00:00', скорее, вы прихватите лишнее за следующие сутки.
21 фев 19, 13:51    [21816311]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
svalex17
Member

Откуда: Харьков
Сообщений: 6
По факту есть значения в таблице
'2019-02-11 01:59:00'
'2019-02-11 02:01:15'
........
'2019-02-11 23:58:33:'
'2019-02-12 00:01:23'
Прихватить лишнее не боюсь, в крайнем случае заменю BETWEEN на ><.
Но вот что делать с данными (разницей) на границах интервалов не понимаю.
21 фев 19, 14:03    [21816343]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
svalex17
Но вот что делать с данными (разницей) на границах интервалов не понимаю.
И почему ее не должно быть?
21 фев 19, 14:19    [21816381]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
svalex17
Member

Откуда: Харьков
Сообщений: 6
invm
svalex17
Но вот что делать с данными (разницей) на границах интервалов не понимаю.
И почему ее не должно быть?


В данном случае идет подсчет потребленной электроэнергии, и если посчитать не правильно, а пока считается не правильно, будет не комильфо...
21 фев 19, 14:42    [21816418]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
svalex17
Member

Откуда: Харьков
Сообщений: 6
Решил вопрос с помощью двух оберток. Ниже привожу как именно.
SELECT * FROM
(
SELECT 
TS, Dev_ID, maxActEnergy_Pos as max_val, 
LAG(maxActEnergy_Pos,1,0) OVER (ORDER BY TS) as min_val, 
maxActEnergy_Pos - (LAG(maxActEnergy_Pos,1,0) OVER (ORDER BY TS)) as diff
FROM
(
SELECT  Dev_ID,
	DATETIMEFROMPARTS(YEAR(TS), MONTH(TS), DAY(TS), DATEPART(HOUR, TS), 0, 0, 0)    AS [TS]
	,Max([ActEnergy_Pos]) maxActEnergy_Pos
				
FROM [dbo].[t_Input]

WHERE   (TS BETWEEN '2019-02-10 23:00' AND '2019-02-12 01:00') and ActEnergy_Pos > 0 and Dev_ID = 1
group by DATETIMEFROMPARTS(YEAR(TS), MONTH(TS), DAY(TS), DATEPART(HOUR, TS), 0, 0, 0) , Dev_ID
) as t
) as t2
WHERE (TS >= '2019-02-11' AND TS <'2019-02-12')
ORDER BY TS


Результат устраивает. Интересно, можно ли решить более изящно?
21 фев 19, 15:19    [21816494]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
invm
Member

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

declare @t table (dt datetime, v int);
insert into @t
values
 ('20190101 10:00:01', 1), ('20190101 10:01:01', 2), ('20190101 10:02:01', 3),
 ('20190101 11:00:01', 6), ('20190101 11:02:01', 7), ('20190101 11:03:01', 8);

select
 b.dth,
 min(a.v), max(a.v),
 max(a.v) - min(a.v)
from
 @t a cross apply
 (select dateadd(hour, datediff(hour, '1900', a.dt), '1900')) b(dth)
group by
 b.dth;

select
 b.dth,
 min(a.v), lead(min(a.v), 1, max(a.v)) over (order by b.dth),
 lead(min(a.v), 1, max(a.v)) over (order by b.dth) - min(a.v)
from
 @t a cross apply
 (select dateadd(hour, datediff(hour, '1900', a.dt), '1900')) b(dth)
group by
 b.dth;
21 фев 19, 15:25    [21816505]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
svalex17
Member

Откуда: Харьков
Сообщений: 6
invm
svalex17,

declare @t table (dt datetime, v int);
insert into @t
values
 ('20190101 10:00:01', 1), ('20190101 10:01:01', 2), ('20190101 10:02:01', 3),
 ('20190101 11:00:01', 6), ('20190101 11:02:01', 7), ('20190101 11:03:01', 8);

select
 b.dth,
 min(a.v), max(a.v),
 max(a.v) - min(a.v)
from
 @t a cross apply
 (select dateadd(hour, datediff(hour, '1900', a.dt), '1900')) b(dth)
group by
 b.dth;

select
 b.dth,
 min(a.v), lead(min(a.v), 1, max(a.v)) over (order by b.dth),
 lead(min(a.v), 1, max(a.v)) over (order by b.dth) - min(a.v)
from
 @t a cross apply
 (select dateadd(hour, datediff(hour, '1900', a.dt), '1900')) b(dth)
group by
 b.dth;

Если я все правильно понял, то результат будет примерно такой, как и у меня в начале, т.е. сумма разниц по диапазонам не будет равна разнице за период.
21 фев 19, 16:05    [21816564]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
invm
Member

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

А выполнить пример реально, а не в уме не пробовали?
21 фев 19, 16:23    [21816580]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет почасовой разницы за период  [new]
svalex17
Member

Откуда: Харьков
Сообщений: 6
invm
svalex17,

А выполнить пример реально, а не в уме не пробовали?

Попробовал. Был не прав. Спасибо.
21 фев 19, 17:54    [21816675]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить