Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос на укрупнение периодов  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Добрый день!

Возникла задача укрупнения пересекающихся периодов, то есть периоды, которые пересекаются друг с другом объединить в один, взяв за начало - начало самого раннего, а за окончание - окончание самого позднего.

Всю голову сломал как это одним запросом сделать. Есть, конечно, вариант обойти циклом - но как-то не комильфо.

Тестовые данные:
CREATE TABLE t(
	 ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,DateBegin DATETIME
	,DateEnd DATETIME
)

INSERT INTO t VALUES('20160601', '20160603')
INSERT INTO t VALUES('20160602', '20160605')
INSERT INTO t VALUES('20160604', '20160607')

INSERT INTO t VALUES('20160610', '20160613')
INSERT INTO t VALUES('20160612', '20160615')
INSERT INTO t VALUES('20160614', '20160619')
INSERT INTO t VALUES('20160619', '20160625')

INSERT INTO t VALUES('20160626', '20160628')


Хочется получить три периода на основе этих данных
20160601 - 20160607
20160610 - 20160625
20160626 - 20160628


Может как-то сюда рекурсивную CTE припахать, чтобы обходить произвольное число периодов?
18 июн 16, 16:03    [19308869]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
Oblom
Может как-то сюда рекурсивную CTE припахать, чтобы обходить произвольное число периодов?
В общем случае да.
Ибо два отрезка должны войти в объединённый отрезок не только потому,
что пересекаются между собой, а и потому, что они могут не персекаться, а между ними может быть
множество пересекающихся отрезков, самый левый из которых пересекается с первым,
а самый правый - со вторым.
ЕМНИП, это одно из возможных представлений древовидной структуры.
18 июн 16, 16:15    [19308904]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
declare @t table(
	 ID int identity(1,1) primary key clustered
	,DateBegin datetime
	,DateEnd datetime
);

insert into @t values('20160601', '20160603');
insert into @t values('20160602', '20160605');
insert into @t values('20160604', '20160607');

insert into @t values('20160610', '20160613');
insert into @t values('20160612', '20160615');
insert into @t values('20160614', '20160619');
insert into @t values('20160619', '20160625');

insert into @t values('20160626', '20160628');

with a as
(
 select
  t.DateBegin, row_number() over (order by t.DateBegin) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateBegin < t.DateBegin and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
),
b as
(
 select
  t.DateEnd, row_number() over (order by t.DateEnd) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateEnd > t.DateEnd and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
)
select
 a.DateBegin, b.DateEnd
from
 a join
 b on b.rn = a.rn;
18 июн 16, 16:57    [19309018]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
select min(DateBegin) as datebegin
		,max(Dateend) as dateend
from(
select id
,DateBegin
,DateEnd
,sum(num) over (order by id) as n
from(
select t1.id 
	,t1.DateBegin
	,t1.DateEnd
	,case when t1.id=1 or t1.datebegin between t.DateBegin and t.DateEnd then 0 else 1 end as num
from t
right join t t1 on t.id = t1.id - 1
) a
) b
group by n
18 июн 16, 17:02    [19309032]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm,

Не уловил идею. У меня по вашему скрипту вывелись все 8 отрезков
18 июн 16, 17:05    [19309040]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Oblom,

Если DateBegin, DateEnd содержат только даты без времени... можно свести к задаче описанной у Изика Бен-Ган - "островки".
Только потребуется разово создать таблицу последовательности целых чисел без пропусков или дат.
Например так:
---- создадим таблицу с последовательными датами охватывая весь требуемый диапазон, в нашем случае 2016 год
if object_id('dates','U') is not null drop table dates
;with dates (dt) as
(
	select convert(date,'20160101',120)
	union all
	select dateadd(day,1,dt)
	from dates
	where dt < '20170101'
)
select *
into dates
from dates d
option (maxrecursion 0)
go
create unique clustered index pk_dates on dates (dt)
go

---- решение
select min(dt), max(dt)
from
(
select d.dt,
	grp = dateadd(day,-dense_rank() over (order by d.dt),d.dt)
from t join dates d
	on d.dt between t.DateBegin and t.DateEnd
) t
group by grp
18 июн 16, 17:11    [19309055]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
3unknown,

Ваш пример работает, но зашивки на Id... Поменял местами строчки в таблице, и уже не работает...
18 июн 16, 17:11    [19309056]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Так отсортируйте по дате при вставке, в чем проблема.
18 июн 16, 17:15    [19309065]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Id - не принципиально, его можно создать самому, с помощью row_number() over ( order by date).
18 июн 16, 17:19    [19309072]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Oblom
У меня по вашему скрипту вывелись все 8 отрезков
Опубликованный скрипт выводит три диапазона.
Если у вас это не так - значит вы изменили или скрипт или данные.
18 июн 16, 17:36    [19309107]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
DECLARE @t TABLE
(
 ID INT IDENTITY
,DateBegin DATETIME
,DateEnd DATETIME
);
INSERT @T(DateBegin,DateEnd)VALUES
 ('20160601','20160603')
,('20160602','20160605')
,('20160604','20160607')
,('20160610','20160613')
,('20160612','20160615')
,('20160614','20160619')
,('20160619','20160625')
,('20160626','20160628');


SELECT
 T.DateBegin
,DateEnd=
(
 SELECT MIN(TT.DateEnd)
 FROM @t TT
 WHERE TT.DateEnd>=T.DateBegin
   AND NOT EXISTS
   (
    SELECT *
    FROM @t TTT
    WHERE TTT.DateBegin<=TT.DateEnd AND TT.DateEnd<=TTT.DateEnd AND TTT.ID<>TT.ID
   )
)
FROM @t T
WHERE NOT EXISTS(SELECT * FROM @t TT WHERE TT.DateBegin<=T.DateBegin AND T.DateBegin<=TT.DateEnd AND TT.ID<>T.ID);
19 июн 16, 16:42    [19311201]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
aleks2
Guest
Oblom
invm,

Не уловил идею. У меня по вашему скрипту вывелись все 8 отрезков


Идея очень простая
1. Начал стока же скока концов. Независимо от числа перекрытий. Начала и концы можно пронумеровать по-порядку
2. Концом для некоего начала N является конец с тем же номером N.
3. Началом является начало любого отрезка, которое ничем не перекрыто.
4. Концом является конец любого отрезка, который ничем не перекрыт.
19 июн 16, 17:18    [19311307]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
Добрый Э - Эх
Guest
Oblom,

если версия сервера позволяет (2012 и выше), то можно сделать таким простым запросом:

select min(datebegin) as x_datebegin, max(dateend) as x_dateend
  from (
         select  datebegin, dateend, sum(sog) over(order by datebegin,dateend) as grp_id
           from (
                  select datebegin, dateend,
                         case 
                           when max(dateend) 
                               over(order by datebegin, dateend 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= datebegin
                           then 0 
                           else 1 
                         end as sog 
                    from t
                ) v0
       ) v1
 group by grp_id
20 июн 16, 05:44    [19312625]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
Добрый Э - Эх
Guest
Oblom,

ну или по старинке:
+ <== Любителю нафталиновых решений времен динозавров посвящается ;-) :
select v_begin.datebegin, min(v_end.dateend) as dateend
  from 
       ( -- Находим все начала диапазонов:
          select datebegin
            from t s1
           where not exists (
                               select null
                                 from t s2
                                where s2.datebegin < s1.datebegin
                                  and s2.dateend >= s1.datebegin
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select dateend
            from t s1
           where not exists (
                               select null
                                 from t s2
                                where s2.dateend > s1.dateend
                                  and s2.datebegin <= s1.dateend
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.datebegin <= v_end.dateend
 group by v_begin.datebegin
 order by v_begin.datebegin, min(v_end.dateend);
20 июн 16, 06:40    [19312652]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
aleks2
Guest
Добрый Э - Эх
Oblom,

ну или по старинке:
+ <== Любителю нафталиновых решений времен динозавров посвящается ;-) :
select v_begin.datebegin, min(v_end.dateend) as dateend
  from 
       ( -- Находим все начала диапазонов:
          select datebegin
            from t s1
           where not exists (
                               select null
                                 from t s2
                                where s2.datebegin < s1.datebegin
                                  and s2.dateend >= s1.datebegin
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select dateend
            from t s1
           where not exists (
                               select null
                                 from t s2
                                where s2.dateend > s1.dateend
                                  and s2.datebegin <= s1.dateend
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.datebegin <= v_end.dateend
 group by v_begin.datebegin
 order by v_begin.datebegin, min(v_end.dateend);


Не позорься.
20 июн 16, 13:41    [19314239]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на укрупнение периодов  [new]
Добрый Э - Эх
Guest
aleks2,

тебя забыл спросить - позориться мне или нет
20 июн 16, 14:00    [19314317]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Запрос на укрупнение периодов  [new]
982183
Member

Откуда: VL
Сообщений: 3352
Лет 20 назад, на тесте у работодателя я решил такую задачу в лоб.
Сформировал отсортированную объединённую табличку с уникальными значениями всех имеющихся массивов.
А после этого осталось проверить на сколько отличаются значения двух соседних записей.
Глупо конечно было, но работало.
24 июл 17, 04:47    [20668499]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить