Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / как свернуть таблицу по полю время (группировка по последовательному времени) / 4 сообщений из 4, страница 1 из 1
20.07.2020, 14:45
    #39981834
Владимир asd
Владимир 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, 15:43
    #39981872
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как свернуть таблицу по полю время (группировка по последовательному времени)
Владимир 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, 20:09
    #39981999
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как свернуть таблицу по полю время (группировка по последовательному времени)
Код: 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
| Ответить | Цитировать | Написать  
21.07.2020, 14:03
    #39982238
Владимир asd
Владимир 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
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / как свернуть таблицу по полю время (группировка по последовательному времени) / 4 сообщений из 4, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 399ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685852]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 279ms
total:  399ms
x
x
Закрыть


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