как свернуть таблицу по полю время (группировка по последовательному времени) / Microsoft SQL Server

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / как свернуть таблицу по полю время (группировка по последовательному времени)

4 сообщений из 4, страница 1 из 1

как свернуть таблицу по полю время (группировка по последовательному времени)

#39981834

Владимир asd

Гость

не могу справиться с запросом, подскажите.

есть таблица значений параметров с меткой времени:
время - параметр - значение
необходимо выбрать из таблицы данные которые вышли за уставку и сколько минут параметр был не в норме
но не просто за определенный период времени, а сгруппировать по времени только минуты идущие подряд:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

declare @tb1 table(id_dt smalldatetime, s int, val int)
insert into @tb1 (id_dt, s, val) values 
	('2020-17-07 00:00',1,11),
	('2020-17-07 00:01',1,12),
	('2020-17-07 00:02',1,13),
	('2020-17-07 00:03',1,2),
	('2020-17-07 00:04',1,17),
	('2020-17-07 00:00',2,-1),
	('2020-17-07 00:01',2, 3)

--просто количество минут для каждого параметра мне ясно:
select s 'параметр', COUNT(s) 'минут вне диапазона', AVG(val) 'ср' from @tb1 where val>10 group by s 
union 
select s 'параметр', COUNT(s) 'минут вне диапазона', AVG(val) 'ср' from @tb1 where val< 0 group by s 

/*
--а как мне получить результат:
id_dt, параметр, count_minute, avg_val
2020-07-17 00:00:00, 1, 3, 12
2020-07-17 00:00:04, 1, 1, 17
2020-07-17 00:00:01, 2, 1, -1
*/

...

Рейтинг:

0 / 0

20.07.2020, 14:45

| Ответить | Цитировать | Написать

как свернуть таблицу по полю время (группировка по последовательному времени)

#39981872

court

Участник

Сообщения: 4 692

Рейтинг: 0 / 0

Владимир asd,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

;with cte as (
	select 
		*
		,inv	=dateadd(minute, - row_number()over(partition by s order by id_dt), id_dt) 
	from @tb1
	where val not between 0 and 10)

select 
	s
	,min(id_dt) as start_dt
	,max(id_dt) as finish_dt
	,datediff(minute, min(id_dt), max(id_dt))+1 as count_minute
	,avg(val) as avg_val 
from cte 
group by
	s
	,inv
order by 1,2

...

Рейтинг:

0 / 0

20.07.2020, 15:43

| Ответить | Цитировать | Написать

как свернуть таблицу по полю время (группировка по последовательному времени)

#39981999

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

declare @tb1 table(id_dt smalldatetime, s int, val int)
insert into @tb1 (id_dt, s, val) values 
	('20200717 00:00',1,11),
	('20200717 00:01',1,12),
	('20200717 00:02',1,13),
	('20200717 00:03',1,2),
	('20200717 00:04',1,17),
	('20200717 00:00',2,-1),
	('20200717 00:01',2, 3)
;
with t as ( select *, next = isnull( lead(val) over(partition by s order by id_dt), 0), prev = isnull(lag(val) over(partition by s order by id_dt), 0) from @tb1 )
   , b as (	select *, n = ROW_NUMBER() over(partition by s order by id_dt) from t where (val > 10 or val < 0) and prev between 0 and 10 )
   , e as (	select *, n = ROW_NUMBER() over(partition by s order by id_dt) from t where (val > 10 or val < 0) and next between 0 and 10 )
 select b.s, b.id_dt, e.id_dt, count_minute = datediff(minute, b.id_dt, e.id_dt) + 1, a.avg_val
   from b inner join e on b.s = e.s and b.n = e.n
        cross apply (select avg_val = AVG(val) from t where t.s = b.s and t.id_dt between b.id_dt and e.id_dt ) as a

...

Рейтинг:

0 / 0

20.07.2020, 20:09

| Ответить | Цитировать | Написать

как свернуть таблицу по полю время (группировка по последовательному времени)

#39982238

Владимир asd

Гость

court,
aleks222

спасибо огромное!!!
я на с++, поэтому иногда просто мозг заклинивает как вы вращаете данные)))
SQL использую редко, но язык мне нравиться.

court,
трюк с отниманием даты номера строк меня впечатлил!)
в программе буду использовать ваш вариант.

aleks222,
при добавлении строк со временем не подряд, происходит сбой в результате - Извините это мой косяк, не озвучил что такое в принципе возможно!

Код: 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.
26.
27.

например если добавить время на 23 часа:
declare @tb1 table(id_dt smalldatetime, s int, val int)
insert into @tb1 (id_dt, s, val) values 
	('2020-17-07 00:00',1,11),
	('2020-17-07 00:01',1,12),
	('2020-17-07 00:02',1,13),
	('2020-17-07 00:03',1,2),
	('2020-17-07 00:04',1,17),
	('2020-17-07 00:05',1,17),
	('2020-17-07 23:04',1,17),
	('2020-17-07 23:05',1,17),
	('2020-17-07 00:00',2,-1),
	('2020-17-07 00:01',2, 3)

результат будет:

s	id_dt	id_dt	count_minute	avg_val
1	2020-07-17 00:00:00	2020-07-17 00:02:00	3	12
1	2020-07-17 00:04:00	2020-07-17 23:05:00	1382	17
2	2020-07-17 00:00:00	2020-07-17 00:00:00	1	-1

вариант court выдает правильно:
s	start_dt	finish_dt	count_minute	avg_val
1	2020-07-17 00:00:00	2020-07-17 00:02:00	3	12
1	2020-07-17 00:04:00	2020-07-17 00:05:00	2	17
1	2020-07-17 23:04:00	2020-07-17 23:05:00	2	17
2	2020-07-17 00:00:00	2020-07-17 00:00:00	1	-1

спасибо большое за отклик и помощь!!!!

адаптировал и вставил в свой код - работает как часы!)))

(здесь на форуме есть какие нибудь плюсики в благодарность?)

...

Рейтинг:

0 / 0

21.07.2020, 14:03

| Ответить | Цитировать | Написать

4 сообщений из 4, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=39982238&tid=1685852]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	49ms
get topic data:	10ms
get forum data:	3ms
get page messages:	42ms
get tp. blocked users:	1ms
others:	271ms

total:	408ms