ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как выбрать периоды без наложения периодов
6 сообщений из 6, страница 1 из 1
Как выбрать периоды без наложения периодов
    #39941613
iww
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iww
Гость
Есть таблица
time packet2017-02-18 11:37:49 1172017-02-18 11:38:49 1182017-02-18 13:52:27 1172017-02-18 14:14:12 1182017-02-18 15:28:13 1172017-02-18 15:44:44 1182017-02-18 15:45:06 1182017-02-18 15:45:21 1172017-02-18 15:45:23 118

Как получить таблицу
time_start(117)time_end(118)2017-02-18 11:37:49 2017-02-18 11:38:492017-02-18 13:52:27 2017-02-18 14:14:122017-02-18 15:28:13 2017-02-18 15:45:062017-02-18 15:45:21 2017-02-18 15:45:23
...
Рейтинг: 0 / 0
27.03.2020, 11:55
| Ответить | Цитировать | Написать  
Как выбрать периоды без наложения периодов
    #39941625
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
WITH 
cte1 AS (
SELECT time, 
       CASE WHEN packet != 117 OR LAG(packet) OVER (ORDER BY time) != 118
            THEN 0 ELSE 1 END new_group
FROM test ),
cte2 AS (
SELECT time, SUM(new_group) OVER (ORDER BY time) group_num
FROM cte1 )
SELECT MIN(time) time_start,
       MAX(time) time_end
FROM cte2
GROUP BY group_num;

fiddle
...
Рейтинг: 0 / 0
27.03.2020, 12:44
| Ответить | Цитировать | Написать  
Как выбрать периоды без наложения периодов
    #39942730
iww
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iww
Гость
Akina,

Спасибо работает, но как без оконных функций сделать?
...
Рейтинг: 0 / 0
01.04.2020, 05:35
| Ответить | Цитировать | Написать  
Как выбрать периоды без наложения периодов
    #39942743
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
iww
Akina,

Спасибо работает, но как без оконных функций сделать?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
declare @t table( dt datetime, i int, primary key(dt, i), unique(i, dt) );

insert @t
values
('2017-02-18 11:37:49', 117) 
,('2017-02-18 11:38:49', 118)
,('2017-02-18 13:52:27', 117)
,('2017-02-18 14:14:12', 118)
,('2017-02-18 15:28:13', 117)
,('2017-02-18 15:44:44', 118)
,('2017-02-18 15:45:06', 118)
,('2017-02-18 15:45:21', 117 )
,('2017-02-18 15:45:23', 118) 
;
select * from @t order by dt;

with t0 as ( select * from @t )
   , t1 as ( select t0.*, pdt = x.dt, pi = x.i from t0 outer apply ( select top(1) * from t0 as x where x.dt < t0.dt order by x.dt desc ) as x )
   , t2 as ( select * from t1 where pi <> i or pi is null )
 select t2.dt, t2.i, x.dt, x.i 
   from t2 
        outer apply ( select top(1) * from t2 as x where x.dt > t2.dt and x.i = 118 order by x.dt asc ) as x
   where t2.i = 117
...
Рейтинг: 0 / 0
01.04.2020, 08:51
| Ответить | Цитировать | Написать  
Как выбрать периоды без наложения периодов
    #39944212
iww
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iww
Гость
aleks222,
Если у нас появляется лишний i
('2017-02-18 11:37:49', 117),
('2017-02-18 11:37:50', 117),
('2017-02-18 11:38:50', 118),
('2017-02-18 13:52:27', 117),
('2017-02-18 14:14:12', 118),
('2017-02-18 15:28:13', 117),
('2017-02-18 15:44:44', 118),
('2017-02-18 15:45:06', 118),
('2017-02-18 15:45:00', 117),
('2017-02-18 15:45:22', 117), - этот нам то не уже не нужен он есть в большем отрезке времени
('2017-02-18 15:50:50', 118),- этот нам то не уже не нужен он есть в большем отрезке времени
('2017-02-18 15:55:50', 118);

time packet time packet
2017-02-18 11:37:49.000 117 2017-02-18 11:38:50.000 118
2017-02-18 11:37:50.000 117 2017-02-18 11:38:50.000 118
2017-02-18 13:52:27.000 117 2017-02-18 14:14:12.000 118
2017-02-18 15:28:13.000 117 2017-02-18 15:44:44.000 118
2017-02-18 15:45:00.000 117 2017-02-18 15:45:06.000 118
2017-02-18 15:45:22.000 117 2017-02-18 15:50:50.000 118
...
Рейтинг: 0 / 0
06.04.2020, 11:14
| Ответить | Цитировать | Написать  
Как выбрать периоды без наложения периодов
    #39944451
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
iww, если не нравятся cross/outer apply, можно заменить на inner/left join, делая последовательно группировки, если order by asc/desc то min/max
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select t."time", t.packet, a."time", a.packet
  
  from (select t."time", t.packet
          from t as t
         where t.packet = 117) as t         

cross apply(select top(1) g."time"
              from t as g
             where g.packet = 118 and t."time" < g."time"
             order by g."time" asc) as e
             
outer apply(select top(1) h."time"
              from t as h
             where h.packet = 117 and h."time" > e."time"
             order by h."time" asc) as d
            
cross apply(select top(1) k."time", k.packet
              from t as k
             where k.packet = 118 and k."time" between t."time" and isnull(d."time", k."time")
             order by k."time" desc) as a


fiddle
...
Рейтинг: 0 / 0
07.04.2020, 01:55
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как выбрать периоды без наложения периодов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=63&tid=1686258]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 2ms
others: 295ms
total:  427ms
созд. / загр.: 427ms
x
x
Закрыть


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