Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подсчет почасовой разницы за период / 10 сообщений из 10, страница 1 из 1
21.02.2019, 13:35
    #39777251
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
Суть вопроса в следующем.
Необходимо посчитать почасовые данные за определенный период, например за сутки. Для решения данной задачи создал запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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, и сума по диапазонам не равна общей разнице за период.
И вот не могу решить эту задачу...
Есть какие-то идеи как мне быть?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 13:51
    #39777261
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
svalex17,

'2019-02-12' это '2019-02-12 00:00:00', скорее, вы прихватите лишнее за следующие сутки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 14:03
    #39777272
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
По факту есть значения в таблице
'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 на ><.
Но вот что делать с данными (разницей) на границах интервалов не понимаю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 14:19
    #39777286
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
svalex17Но вот что делать с данными (разницей) на границах интервалов не понимаю.И почему ее не должно быть?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 14:42
    #39777304
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
invmsvalex17Но вот что делать с данными (разницей) на границах интервалов не понимаю.И почему ее не должно быть?

В данном случае идет подсчет потребленной электроэнергии, и если посчитать не правильно, а пока считается не правильно, будет не комильфо...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 15:19
    #39777358
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
Решил вопрос с помощью двух оберток. Ниже привожу как именно.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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



Результат устраивает. Интересно, можно ли решить более изящно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 15:25
    #39777367
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
svalex17,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 16:05
    #39777411
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
invmsvalex17,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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;


Если я все правильно понял, то результат будет примерно такой, как и у меня в начале, т.е. сумма разниц по диапазонам не будет равна разнице за период.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 16:23
    #39777420
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
svalex17,

А выполнить пример реально, а не в уме не пробовали?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2019, 17:54
    #39777473
svalex17
svalex17
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет почасовой разницы за период
invmsvalex17,

А выполнить пример реально, а не в уме не пробовали?
Попробовал. Был не прав. Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подсчет почасовой разницы за период / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]