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

Откуда: большая деревня
Сообщений: 266
Есть известная задача напересечение периодов. А вот обратной задачи на форуме не нашел.

Есть таблица со статусами (flsg), началом и концом интервала. Статусов конечное количество и они известны. Требуется "наложить" интервалы между собой и получить наложения. При этом flag также является приоритетом при наложении двух периодов: если пересекаются периоды с флагом 1 и 2, то период с флагом 2 остается не тронутым, а период 1 соответствующим образом режется. Если пересекаются два периода с одинаковым flag, то их объединить в один.


-- Тестовый набор данных:
declare @intervals table(flag int, b int, e int)
insert into @intervals (flag, b, e)
select *
from (
    values 
            ( 1, 1,  20), 
            ( 3, 3,  10),
            ( 2, 10,  15),
            ( 3, 12,  14),
            ( 3, 19,  25),

            ( 1, 50,  100), 
            ( 3, 60,  80),
            ( 2, 80,  90), 
            ( 3, 85,  89)

) i (flag, b, e)


На выходе хотелось получить что-то такое:
flag b e
1 1 3
3 3 10
2 10 12
3 12 14
2 14 15
1 14 19
3 19 25

1 50 60
3 60 80
2 80 85
3 85 89
2 89 90
1 90 100


В дальнейшем по этим интервалам будет считаться сумма.
28 апр 15, 13:14    [17575126]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
Добрый Э - Эх
Guest
Разворачиваешь интервалы в точки. Пересекаешь эти точки на исходные интервалы. Формируешь новые интервалы, учитывая приоритет.
28 апр 15, 13:23    [17575189]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Не понятна логика, что значит развернуть в точки и как точки можно пересекать.
28 апр 15, 13:53    [17575393]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Добрый Э - Эх
Разворачиваешь интервалы в точки. Пересекаешь эти точки на исходные интервалы. Формируешь новые интервалы, учитывая приоритет.
Хорошо излагает, зараза! ©
28 апр 15, 13:56    [17575418]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
Добрый Э - Эх
Guest
virtuOS,

версия сервера какая?
28 апр 15, 14:03    [17575479]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SQL 2014, т.е. все новомодные штучки можно использовать.
28 апр 15, 14:09    [17575530]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
Добрый Э - Эх
Guest
virtuOS,

with
--
-- разворачиваем исходные интервалы в точки:
  points as
    (
      select b as point from @intervals
       union
      select e as point from @intervals      
    ),
--
-- заворачиваем точки в новые интервалы:
  new_intervals as
    (
      select point as new_beg
           , lead(point) over(order by point) as new_end
        from points  
    ),
--
-- расставляем интервалам флаги с учетом приоритета:
  new_intervals_with_flag as
    (
      select max(oi.flag) as flag, ni.new_beg, ni.new_end
        from new_intervals ni
        join @intervals oi
          on oi.b <= ni.new_beg
         and oi.e >= ni.new_end 
         and new_end is not null
       group by ni.new_beg, ni.new_end
    ),
--
-- "сливаем" последовательно идущие однофлаговые интервалы в единый интервал:
 merge_interval as
   (
    select flag, min(b) as b, max(e) as e
      from (
             select flag, b, e, sum(sog) over(partition by flag order by b,e) as grp_id
               from (
                      select b, e, flag,
                             case 
                               when b <= max(e) over(partition by flag
                                                         order by b, e 
                                                     rows between unbounded preceding
                                                                      and 1 preceding) 
                                 then 0 
                               else 1 
                             end as sog 
                        from new_intervals_with_flag i
                    ) v0
           ) v1
     group by flag, grp_id
   )  
--
-- Смотрим что у нас тут плучилось
select * 
  from merge_interval
 order by b


З.Ы.
чего-то sqlfidlle.com сегодня у мну не работает, поэтому без проверки...
28 апр 15, 15:29    [17576183]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Добрый Э - Эх
чего-то sqlfidlle.com сегодня у мну не работает
Вот только что проверил - работает.
28 апр 15, 15:50    [17576323]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
Добрый Э - Эх
Guest
iap,

сам-то сайт работает. но при попытке выполнить запрос говорит: "извините, случилась неведомая херня, попробуйте позже"
28 апр 15, 16:04    [17576437]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Проверил на тестовых данных в студии, это то что надо! Респект и уважуха. Буду пробовать на реальных данных.
28 апр 15, 16:05    [17576449]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
И последнее "схлопывание" видимо можно будет убрать, т.к. на этом этапе пересечений уже нет и на суммирование не повлияет.
28 апр 15, 16:10    [17576488]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды наоборот  [new]
Добрый Э - Эх
Guest
+ <=== Исправленному - верить ! :)
Добрый Э - Эх
virtuOS,

with
--
-- разворачиваем исходные интервалы в точки:
  points as
    (
      select b as point from @intervals
       union
      select e as point from @intervals      
    ),
--
-- заворачиваем точки в новые интервалы:
  new_intervals as
    (
      select point as new_beg
           , lead(point) over(order by point) as new_end
        from points  
    ),
--
-- расставляем интервалам флаги с учетом приоритета:
  new_intervals_with_flag as
    (
      select max(oi.flag) as flag, ni.new_beg as b, ni.new_end as e
        from new_intervals ni
        join @intervals oi
          on oi.b <= ni.new_beg
         and oi.e >= ni.new_end 
         and new_end is not null
       group by ni.new_beg, ni.new_end
    ),
--
-- "сливаем" последовательно идущие однофлаговые интервалы в единый интервал:
 merge_interval as
   (
    select flag, min(b) as b, max(e) as e
      from (
             select flag, b, e, sum(sog) over(partition by flag order by b,e) as grp_id
               from (
                      select b, e, flag,
                             case 
                               when b <= max(e) over(partition by flag
                                                         order by b, e 
                                                     rows between unbounded preceding
                                                                      and 1 preceding) 
                                 then 0 
                               else 1 
                             end as sog 
                        from new_intervals_with_flag i
                    ) v0
           ) v1
     group by flag, grp_id
   )  
--
-- Смотрим что у нас тут плучилось
select * 
  from merge_interval
 order by b


З.Ы.
чего-то sqlfidlle.com сегодня у мну не работает, поэтому без проверки...

оу, оказывается опечаточки закрались... вот что значит постить запросы без предварительной их проверки :)
но смотрю, ты и так разобрался что к чему... :) ;)
28 апр 15, 20:14    [17577826]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить