Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Часовая выборка показаний счетчика. / 19 сообщений из 19, страница 1 из 1
18.11.2020, 11:00
    #40019840
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Здравствуйте!
Сразу напишу, что с 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


Заранее благодарен!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 11:16
    #40019850
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман
Мне нужно сделать запрос такой: выборка первого и при необходимости последнего значения каждого часа
так, например
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
;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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 12:34
    #40019880
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
court
Радков Роман
Мне нужно сделать запрос такой: выборка первого и при необходимости последнего значения каждого часа
так, например
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
;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



Ну чему ты учишь молодежЪ?
За такое руки сразу отрывать надо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 12:37
    #40019882
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
aleks222
court
пропущено...
так, например
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
;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-м функциям, как-то совсем "грустно", не ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 12:54
    #40019896
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
court,

Вместо format можно datediff(hour, '1900', ...)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 13:32
    #40019912
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
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, по два значения в час.
Некоторые показания не вивелись.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 14:12
    #40019930
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
aleks222
court
пропущено...
так, например
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
;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



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


А как лучше запросить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 14:23
    #40019935
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман,

И вы уже проверили, что для 2-х часов и для 8-ми часов есть стартовые значения в исходных данных?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:00
    #40019960
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
env
Радков Роман,

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


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

Извините! За 13 ноября.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:02
    #40019961
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман,

Вот и проверьте, сколько там строк для периодов 2:00 - 2:59 и 8:00 - 8:59
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:23
    #40019973
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
env
Радков Роман,

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


Проверил. 2:00 - 2:59 - 60 записей. 8:00 - 8:59 - 60 записей.
Ну все ровно мне нужно в запросе учесть то, что могут быть не все записи. В этом и идея, взять первую и последнею запись, даже если она там одна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:38
    #40019984
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман
Выложил выборку по приведенному выше sql-запросу из имеющей таблицы за 11 ноября 2020, полностью сутки.
куда выложил ? на депозит ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:43
    #40019989
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман
invm,
IDDateAndTimevalrn1rn212020-11-13 00:00:36.000716117822020-11-13 00:59:36.000717178132020-11-13 01:00:36.000717117842020-11-13 01:59:36.000717178152020-11-13 02:59:36.000717178162020-11-13 03:00:36.000717117872020-11-13 03:59:36.000718178182020-11-13 04:00:36.000718117892020-11-13 04:59:36.0007181781102020-11-13 05:00:36.0007181178112020-11-13 05:59:36.0007181781122020-11-13 06:00:36.0007181178132020-11-13 06:59:36.0007191781142020-11-13 07:00:36.0007191178152020-11-13 07:59:36.0007191781162020-11-13 08:59:36.0007191781172020-11-13 09:59:36.0007201781182020-11-13 10:00:36.0007201178192020-11-13 10:59:36.0007211781202020-11-13 11:00:36.0007211178212020-11-13 11:59:36.0007221781222020-11-13 12:00:36.0007221178232020-11-13 12:59:36.0007231781242020-11-13 13:00:36.0007231178252020-11-13 13:59:36.0007241781262020-11-13 14:00:36.0007241178272020-11-13 14:59:36.0007251781282020-11-13 15:59:36.0007251781292020-11-13 16:00:36.0007261178302020-11-13 16:59:36.0007261781312020-11-13 17:00:36.0007261178322020-11-13 17:59:36.0007271781332020-11-13 18:00:36.0007271178342020-11-13 18:59:36.0007271781352020-11-13 19:00:36.0007271178362020-11-13 19:59:36.0007271781372020-11-13 20:00:36.0007271178382020-11-13 20:59:36.0007271781392020-11-13 21:00:36.0007271178402020-11-13 21:59:36.0007291781412020-11-13 22:00:36.0007291178422020-11-13 22:59:36.0007321781432020-11-13 23:00:36.0007321178442020-11-13 23:59:36.0007321781



Это результат запроса.
Выдало 44 значение за сутки, хотя должно быть 48, по два значения в час.
Некоторые показания не вивелись.ТС, вот это "178" (это, вообще, к-во замеров в час) в нумераторе, для ВСЕХ ЧАСОВ, как бы намекает, что делаешь ты что-то не так ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:46
    #40019995
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
[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 часов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 15:49
    #40019996
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман,

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

Вот пример , всё работает при правильном использовании.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 16:15
    #40020014
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
env
Радков Роман,

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

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


Не пойму где косяк, т.к. этот запрос мне предложили выше.
Он выполняется 10 секунд и выдает постоянно разное количество строк от 40 до 45.
Как так может быть?
Может есть другой вариант?
Вроде задача (теоретически) - не сложная.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 16:19
    #40020016
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Радков Роман,

Покажите текст запроса, который запускаете и структуру таблицы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2020, 16:44
    #40020034
Радков Роман
Радков Роман
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
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 строк и запрос меньше секунды.
Всем спасибо! Очень помогли.
Форумом не умею пользоваться. Здесь нужно закрывать тему?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2020, 06:50
    #40020197
old_joy
old_joy
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Часовая выборка показаний счетчика.
Решал такую задачу в 1998 году.
При вставке строки в отдельное поле вставляется час записи. Поле - в индекс. При построении суточного графика потребления тормозов не было.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Часовая выборка показаний счетчика. / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 417ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1685397]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 280ms
total:  417ms
x
x
Закрыть


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