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

Откуда:
Сообщений: 8
Здравствуйте!
Сразу напишу, что с sql я на "ВЫ" и по отчеству.
Есть таблица, в которую каждую минуту пишутся показание счетчика воды.
Мне нужно сделать запрос такой: выборка первого и при необходимости последнего значения каждого часа (min,max,avg - мне не подходят).
Гуглил три дня. Выдает функцию (first_value), не пойму с какой стороны подойти. Если полностью выбрать всю таблицу, а потом на клиентской машине VBSом выбирать...... - думаю, что sql на много быстрее и рациональней это сделает.
Таблица:
DateAndTime | Val
-------------------------------------
2020-11-12 01:07:36.000| 2618
2020-11-12 01:08:36.000| 2618
2020-11-12 01:09:36.000| 2618
2020-11-12 01:10:36.000| 2618
2020-11-12 01:11:36.000| 2618
2020-11-12 01:12:36.000| 2618
2020-11-12 01:13:36.000| 2618
2020-11-12 01:14:36.000| 2618
2020-11-12 01:15:36.000| 2618
2020-11-12 01:16:36.000| 2618
2020-11-12 01:17:36.000| 2618
2020-11-12 01:18:36.000| 2619
2020-11-12 01:19:36.000| 2619
2020-11-12 01:20:36.000| 2619
2020-11-12 01:21:36.000| 2619
2020-11-12 01:22:36.000| 2619
2020-11-12 01:23:36.000| 2619
2020-11-12 01:24:36.000| 2619
2020-11-12 01:25:36.000| 2619
2020-11-12 01:26:36.000| 2619
2020-11-12 01:27:36.000| 2619
2020-11-12 01:28:36.000| 2619
2020-11-12 01:29:36.000| 2619
2020-11-12 01:30:36.000| 2619
2020-11-12 01:31:36.000| 2619
………………….. |………
2020-11-12 07:52:36.000| 2622
2020-11-12 07:53:36.000| 2622
2020-11-12 07:54:36.000| 2622
2020-11-12 07:55:36.000| 2622
2020-11-12 07:56:36.000| 2622
2020-11-12 07:57:36.000| 2622
2020-11-12 07:58:36.000| 2622
2020-11-12 07:59:36.000| 2622
2020-11-12 08:00:36.000| 2622
2020-11-12 08:01:36.000| 2622
2020-11-12 08:02:36.000| 2622
2020-11-12 08:03:36.000| 2622
2020-11-12 08:04:36.000| 2622
2020-11-12 08:05:36.000| 2622
2020-11-12 08:06:36.000| 2622
2020-11-12 08:07:36.000| 2622
2020-11-12 08:08:36.000| 2622
2020-11-12 08:09:36.000| 2622

Хочу получить:
DateAndTime | Val
-------------------------------------
2020-11-12 01:07:36.000| 2618
2020-11-12 02:00:12.000| 2620
………………… |……..
2020-11-12 08:00:36.000| 2622


Заранее благодарен!
18 ноя 20, 11:00    [22234419]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
court
Member

Откуда:
Сообщений: 2160
Радков Роман
Мне нужно сделать запрос такой: выборка первого и при необходимости последнего значения каждого часа
так, например
;with cte as (
	select
		*
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime) as rn1
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime desc) as rn2
	from Таблица )

select * from cte where rn1 = 1 or rn2 = 1 order by DateAndTime
18 ноя 20, 11:16    [22234432]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
aleks222
Member

Откуда:
Сообщений: 1086
court
Радков Роман
Мне нужно сделать запрос такой: выборка первого и при необходимости последнего значения каждого часа
так, например
;with cte as (
	select
		*
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime) as rn1
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime desc) as rn2
	from Таблица )

select * from cte where rn1 = 1 or rn2 = 1 order by DateAndTime


Ну чему ты учишь молодежЪ?
За такое руки сразу отрывать надо.
18 ноя 20, 12:34    [22234492]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
court
Member

Откуда:
Сообщений: 2160
aleks222
court
пропущено...
так, например
;with cte as (
	select
		*
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime) as rn1
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime desc) as rn2
	from Таблица )

select * from cte where rn1 = 1 or rn2 = 1 order by DateAndTime



Ну чему ты учишь молодежЪ?
За такое руки сразу отрывать надо.
ты про format ? :)
ну так, а partition by по 4-м функциям, как-то совсем "грустно", не ?
18 ноя 20, 12:37    [22234495]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
invm
Member

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

Вместо format можно datediff(hour, '1900', ...)
18 ноя 20, 12:54    [22234526]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
invm,
ID DateAndTime val rn1 rn2
1 2020-11-13 00:00:36.000 716 1 178
2 2020-11-13 00:59:36.000 717 178 1
3 2020-11-13 01:00:36.000 717 1 178
4 2020-11-13 01:59:36.000 717 178 1
5 2020-11-13 02:59:36.000 717 178 1
6 2020-11-13 03:00:36.000 717 1 178
7 2020-11-13 03:59:36.000 718 178 1
8 2020-11-13 04:00:36.000 718 1 178
9 2020-11-13 04:59:36.000 718 178 1
10 2020-11-13 05:00:36.000 718 1 178
11 2020-11-13 05:59:36.000 718 178 1
12 2020-11-13 06:00:36.000 718 1 178
13 2020-11-13 06:59:36.000 719 178 1
14 2020-11-13 07:00:36.000 719 1 178
15 2020-11-13 07:59:36.000 719 178 1
16 2020-11-13 08:59:36.000 719 178 1
17 2020-11-13 09:59:36.000 720 178 1
18 2020-11-13 10:00:36.000 720 1 178
19 2020-11-13 10:59:36.000 721 178 1
20 2020-11-13 11:00:36.000 721 1 178
21 2020-11-13 11:59:36.000 722 178 1
22 2020-11-13 12:00:36.000 722 1 178
23 2020-11-13 12:59:36.000 723 178 1
24 2020-11-13 13:00:36.000 723 1 178
25 2020-11-13 13:59:36.000 724 178 1
26 2020-11-13 14:00:36.000 724 1 178
27 2020-11-13 14:59:36.000 725 178 1
28 2020-11-13 15:59:36.000 725 178 1
29 2020-11-13 16:00:36.000 726 1 178
30 2020-11-13 16:59:36.000 726 178 1
31 2020-11-13 17:00:36.000 726 1 178
32 2020-11-13 17:59:36.000 727 178 1
33 2020-11-13 18:00:36.000 727 1 178
34 2020-11-13 18:59:36.000 727 178 1
35 2020-11-13 19:00:36.000 727 1 178
36 2020-11-13 19:59:36.000 727 178 1
37 2020-11-13 20:00:36.000 727 1 178
38 2020-11-13 20:59:36.000 727 178 1
39 2020-11-13 21:00:36.000 727 1 178
40 2020-11-13 21:59:36.000 729 178 1
41 2020-11-13 22:00:36.000 729 1 178
42 2020-11-13 22:59:36.000 732 178 1
43 2020-11-13 23:00:36.000 732 1 178
44 2020-11-13 23:59:36.000 732 178 1


Это результат запроса.
Выдало 44 значение за сутки, хотя должно быть 48, по два значения в час.
Некоторые показания не вивелись.
18 ноя 20, 13:32    [22234560]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
aleks222
court
пропущено...
так, например
;with cte as (
	select
		*
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime) as rn1
		,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime desc) as rn2
	from Таблица )

select * from cte where rn1 = 1 or rn2 = 1 order by DateAndTime


Ну чему ты учишь молодежЪ?
За такое руки сразу отрывать надо.


А как лучше запросить?
18 ноя 20, 14:12    [22234588]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Радков Роман,

И вы уже проверили, что для 2-х часов и для 8-ми часов есть стартовые значения в исходных данных?
18 ноя 20, 14:23    [22234598]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
env
Радков Роман,

И вы уже проверили, что для 2-х часов и для 8-ми часов есть стартовые значения в исходных данных?


Я не понял Вашего вопроса.
Выложил выборку по приведенному выше sql-запросу из имеющей таблицы за 11 ноября 2020, полностью сутки.

Извините! За 13 ноября.

Сообщение было отредактировано: 18 ноя 20, 14:56
18 ноя 20, 15:00    [22234642]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Радков Роман,

Вот и проверьте, сколько там строк для периодов 2:00 - 2:59 и 8:00 - 8:59
18 ноя 20, 15:02    [22234644]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
env
Радков Роман,

Вот и проверьте, сколько там строк для периодов 2:00 - 2:59 и 8:00 - 8:59


Проверил. 2:00 - 2:59 - 60 записей. 8:00 - 8:59 - 60 записей.
Ну все ровно мне нужно в запросе учесть то, что могут быть не все записи. В этом и идея, взять первую и последнею запись, даже если она там одна.
18 ноя 20, 15:23    [22234660]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
court
Member

Откуда:
Сообщений: 2160
Радков Роман
Выложил выборку по приведенному выше sql-запросу из имеющей таблицы за 11 ноября 2020, полностью сутки.
куда выложил ? на депозит ?
18 ноя 20, 15:38    [22234678]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
court
Member

Откуда:
Сообщений: 2160
Радков Роман
invm,
IDDateAndTimevalrn1rn2
12020-11-13 00:00:36.0007161178
22020-11-13 00:59:36.0007171781
32020-11-13 01:00:36.0007171178
42020-11-13 01:59:36.0007171781
52020-11-13 02:59:36.0007171781
62020-11-13 03:00:36.0007171178
72020-11-13 03:59:36.0007181781
82020-11-13 04:00:36.0007181178
92020-11-13 04:59:36.0007181781
102020-11-13 05:00:36.0007181178
112020-11-13 05:59:36.0007181781
122020-11-13 06:00:36.0007181178
132020-11-13 06:59:36.0007191781
142020-11-13 07:00:36.0007191178
152020-11-13 07:59:36.0007191781
162020-11-13 08:59:36.0007191781
172020-11-13 09:59:36.0007201781
182020-11-13 10:00:36.0007201178
192020-11-13 10:59:36.0007211781
202020-11-13 11:00:36.0007211178
212020-11-13 11:59:36.0007221781
222020-11-13 12:00:36.0007221178
232020-11-13 12:59:36.0007231781
242020-11-13 13:00:36.0007231178
252020-11-13 13:59:36.0007241781
262020-11-13 14:00:36.0007241178
272020-11-13 14:59:36.0007251781
282020-11-13 15:59:36.0007251781
292020-11-13 16:00:36.0007261178
302020-11-13 16:59:36.0007261781
312020-11-13 17:00:36.0007261178
322020-11-13 17:59:36.0007271781
332020-11-13 18:00:36.0007271178
342020-11-13 18:59:36.0007271781
352020-11-13 19:00:36.0007271178
362020-11-13 19:59:36.0007271781
372020-11-13 20:00:36.0007271178
382020-11-13 20:59:36.0007271781
392020-11-13 21:00:36.0007271178
402020-11-13 21:59:36.0007291781
412020-11-13 22:00:36.0007291178
422020-11-13 22:59:36.0007321781
432020-11-13 23:00:36.0007321178
442020-11-13 23:59:36.0007321781



Это результат запроса.
Выдало 44 значение за сутки, хотя должно быть 48, по два значения в час.
Некоторые показания не вивелись.
ТС, вот это "178" (это, вообще, к-во замеров в час) в нумераторе, для ВСЕХ ЧАСОВ, как бы намекает, что делаешь ты что-то не так ...
18 ноя 20, 15:43    [22234684]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
[quot Радков Роман#22234560],
ID DateAndTime val rn1 rn2
1 2020-11-13 00:00:36.000 716 1 178
2 2020-11-13 00:59:36.000 717 178 1
3 2020-11-13 01:00:36.000 717 1 178
4 2020-11-13 01:59:36.000 717 178 1
5 2020-11-13 02:59:36.000 717 178 1
6 2020-11-13 03:00:36.000 717 1 178
7 2020-11-13 03:59:36.000 718 178 1
8 2020-11-13 04:00:36.000 718 1 178
9 2020-11-13 04:59:36.000 718 178 1
10 2020-11-13 05:00:36.000 718 1 178
11 2020-11-13 05:59:36.000 718 178 1
12 2020-11-13 06:00:36.000 718 1 178
13 2020-11-13 06:59:36.000 719 178 1
14 2020-11-13 07:00:36.000 719 1 178
15 2020-11-13 07:59:36.000 719 178 1
16 2020-11-13 08:59:36.000 719 178 1
17 2020-11-13 09:59:36.000 720 178 1
18 2020-11-13 10:00:36.000 720 1 178
19 2020-11-13 10:59:36.000 721 178 1
20 2020-11-13 11:00:36.000 721 1 178
21 2020-11-13 11:59:36.000 722 178 1
22 2020-11-13 12:00:36.000 722 1 178
23 2020-11-13 12:59:36.000 723 178 1
24 2020-11-13 13:00:36.000 723 1 178
25 2020-11-13 13:59:36.000 724 178 1
26 2020-11-13 14:00:36.000 724 1 178
27 2020-11-13 14:59:36.000 725 178 1
28 2020-11-13 15:59:36.000 725 178 1
29 2020-11-13 16:00:36.000 726 1 178
30 2020-11-13 16:59:36.000 726 178 1
31 2020-11-13 17:00:36.000 726 1 178
32 2020-11-13 17:59:36.000 727 178 1
33 2020-11-13 18:00:36.000 727 1 178
34 2020-11-13 18:59:36.000 727 178 1
35 2020-11-13 19:00:36.000 727 1 178
36 2020-11-13 19:59:36.000 727 178 1
37 2020-11-13 20:00:36.000 727 1 178
38 2020-11-13 20:59:36.000 727 178 1
39 2020-11-13 21:00:36.000 727 1 178
40 2020-11-13 21:59:36.000 729 178 1
41 2020-11-13 22:00:36.000 729 1 178
42 2020-11-13 22:59:36.000 732 178 1
43 2020-11-13 23:00:36.000 732 1 178
44 2020-11-13 23:59:36.000 732 178 1


Вот выборка.
Нет начало 2 часов, нет начало 8 часов, нет начало 9 часов, нет начало 15 часов.
18 ноя 20, 15:46    [22234690]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Радков Роман,

Где-то вы в запросе накосячили.

Вот пример, всё работает при правильном использовании.
18 ноя 20, 15:49    [22234692]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
env
Радков Роман,

Где-то вы в запросе накосячили.

Вот пример, всё работает при правильном использовании.


Не пойму где косяк, т.к. этот запрос мне предложили выше.
Он выполняется 10 секунд и выдает постоянно разное количество строк от 40 до 45.
Как так может быть?
Может есть другой вариант?
Вроде задача (теоретически) - не сложная.
18 ноя 20, 16:15    [22234719]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Радков Роман,

Покажите текст запроса, который запускаете и структуру таблицы
18 ноя 20, 16:19    [22234721]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
Радков Роман
Member

Откуда:
Сообщений: 8
env
Радков Роман,

Покажите текст запроса, который запускаете и структуру таблицы


Стыдно выкладывать свой косяк:( Но я только учусь:)

Это мне предложили:
;with cte as (
select
*
,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime) as rn1
,row_number()over(partition by format(DateAndTime, 'yyyy\-MM\-dd\-HH') order by DateAndTime desc) as rn2
from Таблица )

select * from cte where rn1 = 1 or rn2 = 1 order by DateAndTime

А так, как у меня в таблице не одни сутки и не один счетчик, я добавил еще свои условия.

with cte as (
select
*
,row_number()over(partition by format([DateAndTime], 'yyyy\-MM\-dd\-HH') order by [DateAndTime]) as rn1
,row_number()over(partition by format([DateAndTime], 'yyyy\-MM\-dd\-HH') order by [DateAndTime] desc) as rn2
from [DBASU].[dbo].[FTMKpodpSOT]
select [DateAndTime],val
from cte
where (rn1 = 1 or rn2 = 1) AND TagIndex = 0 AND [DateAndTime] >= CONVERT(DATETIME,'14.11.2020',104) AND [DateAndTime] < DATEADD(DAY,1,CONVERT(DATETIME,'14.11.2020',104))
order by [DateAndTime]

Вот это и сломало весь запрос. Перенес условие в with и все заработало.

with cte as (
select
*
,row_number()over(partition by format([DateAndTime], 'yyyy\-MM\-dd\-HH') order by [DateAndTime]) as rn1
,row_number()over(partition by format([DateAndTime], 'yyyy\-MM\-dd\-HH') order by [DateAndTime] desc) as rn2
from [DBASU].[dbo].[FTMKpodpSOT]
where TagIndex = 0 AND [DateAndTime] >= CONVERT(DATETIME,'17.11.2020',104) AND [DateAndTime] < DATEADD(DAY,1,CONVERT(DATETIME,'17.11.2020',104)))
select [DateAndTime],val
from cte
where rn1 = 1 or rn2 = 1
order by [DateAndTime]

Все 48 строк и запрос меньше секунды.
Всем спасибо! Очень помогли.
Форумом не умею пользоваться. Здесь нужно закрывать тему?
18 ноя 20, 16:44    [22234753]     Ответить | Цитировать Сообщить модератору
 Re: Часовая выборка показаний счетчика.  [new]
old_joy
Member

Откуда:
Сообщений: 178
Решал такую задачу в 1998 году.
При вставке строки в отдельное поле вставляется час записи. Поле - в индекс. При построении суточного графика потребления тормозов не было.
19 ноя 20, 06:50    [22235055]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить