Помогите с запросом на поиск непрерывных промежутков / Microsoft SQL Server

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

Гость

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

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

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

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите с запросом на поиск непрерывных промежутков

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

Помогите с запросом на поиск непрерывных промежутков

#40094675

RedVlad

Гость

-- Что бы конкретнее было, сразу таблица

Код: sql

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

declare @T table (id int, DT datetime )

insert into @T values (1,'2021-01-01')
insert into @T values (1,'2021-01-02')
insert into @T values (1,'2021-01-03')
 insert into @T values (1,'2021-01-10')
 insert into @T values (1,'2021-01-11')
 insert into @T values (1,'2021-01-12')
 insert into @T values (1,'2021-01-13')

insert into @T values (2,'2021-01-01')
insert into @T values (2,'2021-01-02')
insert into @T values (2,'2021-01-03')
insert into @T values (2,'2021-01-04')

insert into @T values (3,'2021-01-01')
insert into @T values (3,'2021-01-02')

/*
надо показать все непрерывные промежутки от 3х дней. Т.е. должно быть

1 - '2021-01-01'-'2021-01-03'
1 - '2021-01-10'-'2021-01-13'
2 - '2021-01-01'-'2021-01-04'

сделал так:
*/

Код: sql

1.
2.
3.
4.
5.
6.
7.

select  
  t1.id, xMIN = cast(min(t1.DT)-1 as date), xMax = cast(max(t1.DT)+1 as date) 
from @T t1
where 1=1
and exists( select 1 from @T t2 where t2.id=t1.id and t2.DT=t1.DT-1)
and exists( select 1 from @T t2 where t2.id=t1.id and t2.DT=t1.DT+1)
group by t1.id

/*
1 2021-01-01 2021-01-13
2 2021-01-01 2021-01-04

но это неправильно, т.к. у id=1 есть разрыв, а запрос его не учитывает.

Подскажете как надо?
*/

...

Рейтинг:

0 / 0

02.09.2021, 08:36

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

Помогите с запросом на поиск непрерывных промежутков

#40094679

RedVlad

Гость

реальная таблица, конечно, больше.
Т.е. хотелось бы посмотреть общее решение.. вот бы еще кол-во дней подряд можно было менять..

зы
Если интересно, это датчик - дата, когда заглючил/неответил. Если глючил один-два дня, ничего, бывает.
А если подряд несколько дней, то надо разбираться.
Период тоже важно "подсветить" (а не просто факт, что существует такой период), может тогда какие-то работы велись и это нормально тоже

...

Рейтинг:

0 / 0

02.09.2021, 08:52

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

Помогите с запросом на поиск непрерывных промежутков

#40094682

aleks222

Участник

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

Рейтинг: 0 / 0

Опять 25.

1. Находим начала интервалов непрерывности: начало = дата когда НЕТ предыдущей даты.
2. Находим концы интервалов непрерывности: конец = дата когда НЕТ следующей даты.
3. Начал ровно столько сколько концов.
4. Нумеруем начала и концы ПО ПОРЯДКУ.
5. Соединяем по номерам.

...

Рейтинг:

0 / 0

02.09.2021, 09:11

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

Помогите с запросом на поиск непрерывных промежутков

#40094704

Oleg_SQL

Гость

RedVlad,

Возможно, не самый лучший вариант. Но вариант ))

Код: sql

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

;WITH CTE
     AS (SELECT ROW_NUMBER() OVER(ORDER BY id, dt) AS rn, 
                      *, 
                      CASE ISNULL(DATEDIFF(DD, LAG(dt) OVER(PARTITION BY id ORDER BY id, dt), Dt), 0) WHEN 1 THEN 0 ELSE 1 END AS brk -- признак разрыва периода (не 1 день)
             FROM (SELECT DISTINCT id, dt FROM @t -- убираем двойные значения если есть, чтобы не мешали
                      UNION ALL 
                      SELECT MAX(Id)+1, '19000101' FROM @t -- добавляем фиктивную нерабочую строку для анализа последнего значения таблицы (там нет признака разрыва)
                      ) AS a
         )
     ,RSLT
     AS (SELECT *, 
                      LEAD(rn) OVER(ORDER BY rn) - 1 AS rnTo
             FROM CTE AS a
           WHERE brk = 1
         )
     SELECT a.Id, 
                a.DT AS dtFrom, 
                b.DT AS dtTo
        FROM RSLT AS a
        LEFT JOIN CTE AS b ON a.rnTo = b.rn
      WHERE a.rnTo - a.rn + 1 >= 3;

...

Рейтинг:

0 / 0

02.09.2021, 10:18

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

Помогите с запросом на поиск непрерывных промежутков

#40094710

RedVlad

Гость

aleks222,
>> upd: declare @T table (id int, DT datetime, I datetime) -- добавил столбец

Понятно, спасибо.

Получилось аля так:

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

update t set 
  t.I = t.DT 
from @T t
where not exists(select 1 from @T tt where tt.id=t.id and tt.DT=t.DT-1)

update t set 
  t.I = t.DT
from @T t
where not exists(select 1 from @T tt where tt.id=t.id and tt.DT=t.DT+1)

select 
 A.id, A.I, B.I
from 
(
select
 id, I,  N= ROW_NUMBER() OVER( ORDER BY id )  
from @T
where I is not null
) A
join 
(
select
 id, I,  N= ROW_NUMBER() OVER( ORDER BY id )  
from @T
where I is not null
) B on B.N-1=A.N
where 1.0*B.N / 2 = B.N / 2
 and abs(datediff(day,A.I,B.I)) >= 2

...

Рейтинг:

0 / 0

02.09.2021, 10:25

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

Помогите с запросом на поиск непрерывных промежутков

#40094711

RedVlad

Гость

Oleg_SQL,

Спасибо!

Попробую на реальных данных и Ваш тоже. Какой быстрее будет, тот оставлю

...

Рейтинг:

0 / 0

02.09.2021, 10:26

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

Помогите с запросом на поиск непрерывных промежутков

#40094729

RedVlad

Гость

Oleg_SQL,

Ваш лучше.

...

Рейтинг:

0 / 0

02.09.2021, 10:48

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=46&tid=1684349]:	0ms
get settings:	10ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	130ms
get topic data:	12ms
get forum data:	2ms
get page messages:	47ms
get tp. blocked users:	2ms
others:	12ms

total:	236ms