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

Откуда: Барнаул
Сообщений: 61
Ребят выручайте, голову сломал. Не могу придумать как сделать.

Есть следующая таблица "Отпуска":


Сотрудник ДатаНачало ДатаОкончания
Петров 05.01.2000 10.01.2000
Петров 11.01.2000 20.01.2000
Петров 21.01.2000 23.01.2000
Сидоров 01.01.2000 10.01.2000
Петров 24.01.2000 30.01.2000
Петров 01.01.2000 04.01.2000
Сидоров 20.01.2000 30.01.2000

Нужно сгруппировавший по дате так, что если ДатаОкончания (первого) = ДатаНачало+1 (второго) то объединяем. На выходе должно быть так:


Сотрудник ДатаНачало ДатаОкончания
Петров 01.01.2000 30.01.2000
Сидоров 01.01.2000 10.01.2000
Сидоров 20.01.2000 30.01.2000
25 апр 13, 14:22    [14230100]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Proteus
Member

Откуда:
Сообщений: 1348
WITH s AS
 (SELECT 'Петров' ID ,to_date('05.01.2000', 'dd.mm.yyyy') b ,to_date('10.01.2000', 'dd.mm.yyyy') e    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('11.01.2000', 'dd.mm.yyyy')        ,to_date('20.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('21.01.2000', 'dd.mm.yyyy')        ,to_date('23.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('10.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('24.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('04.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('20.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual)
SELECT id, MIN(d) b, MAX(d) e
  FROM (SELECT ID
              ,b + COLUMN_VALUE d
              ,COUNT(*) OVER(PARTITION BY ID ORDER BY b + COLUMN_VALUE) rn
          FROM s
              ,TABLE(CAST(MULTISET (SELECT LEVEL - 1 r
                             FROM dual
                           CONNECT BY LEVEL <= e - b + 1) AS
                          sys.OdciNumberList)))
 GROUP BY id, d - rn
 ORDER BY 1,2
25 апр 13, 14:42    [14230253]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Gambit_d.s,

поишите по start-of-group
25 апр 13, 14:45    [14230297]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Добрый Э - Эх
Guest
Proteus,

сначала плодим кучу записей. после - группируем. я, конечно, понимаю, что серваку делать нехер. но уж пусть лучше простаивает, чем в пустую греет воздух. :)
25 апр 13, 15:02    [14230446]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Proteus
Member

Откуда:
Сообщений: 1348
Добрый Э - Эх,

А по другому не всегда может получаться. Например если это не отпуска а произвольные периоды которые могут пересекаться. Как в этом случае ты будешь определять периоды.

Но для данной задачи по совету
orawish

поишите по start-of-group

можно и обойтись без размножения.
SELECT ID, MIN(b), MAX(e)
  FROM (SELECT ID
              ,b
              ,e
              ,SUM(startofgroup) OVER(PARTITION BY ID ORDER BY b) g
          FROM (SELECT s.*
                      ,decode(b
                             ,LAG(e) OVER(PARTITION BY ID ORDER BY b) + 1
                             ,0
                             ,1) startofgroup
                  FROM s))
 GROUP BY id, g
 ORDER BY 1, 2;
25 апр 13, 15:11    [14230530]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Gambit_d.s
Member

Откуда: Барнаул
Сообщений: 61
Спасибо, всем огромное! Буду разбираться
26 апр 13, 05:57    [14233099]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Gambit_d.s
Спасибо, всем огромное! Буду разбираться

до кучи ~то же, в профиль..
WITH s AS
 (SELECT 'Петров' ID ,to_date('05.01.2000', 'dd.mm.yyyy') b ,to_date('10.01.2000', 'dd.mm.yyyy') e    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('11.01.2000', 'dd.mm.yyyy')        ,to_date('20.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('21.01.2000', 'dd.mm.yyyy')        ,to_date('23.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('10.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('24.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('04.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('20.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual
) ,t as (
select s.* 
      ,case when id = lag(id) over (order by id ,b) 
             and b  = lag(e)  over (order by id ,b)+1 
            then 0 else 1 end as st_of_gr
  from s
) ,t2 as (
  select t.* 
        ,sum(st_of_gr) over(order by id,b) gr
    from t
) select min(id) ,min(b) ,max(e)
    from t2
group by gr
/
26 апр 13, 13:28    [14234883]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Добрый Э - Эх
Guest
Proteus
А по другому не всегда может получаться.
Тебе сразу ссылку дать или сам поискать попробуешь? Были тут темы: и сворачивали периоды, и разворачивали, и пересечения "сливали", и на подынтервальчики делили.
26 апр 13, 13:52    [14235049]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Добрый Э - Эх
Guest
Proteus, а, пожалуй, дам сразу ссылку... Начни читать с этого топика:
Если там чего-то не найдешь, дам ещё ссылок...
26 апр 13, 13:57    [14235094]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Добрый Э - Эх
Guest
Proteus,
Пример работы с периодами "в картинках". Пересечения, полные вхождения - всё присутствует, всё отрабатывается...
26 апр 13, 14:00    [14235115]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Добрый Э - Эх,

К чему ты клонишь?
Что всегда при изменениях надо делать слияния/разделения интервалов?
А если представить что индервалы могут быть длиной по несколько тысяч строк и представляют диапазоны сущностей, переходящих от одного владельца к другому + при этом достаточно часто редактируются?
26 апр 13, 14:10    [14235175]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
Добрый Э - Эх
Guest
dbms_photoshop,

я ни к чему не клоню. Я показываю Протеусу, как можно "склеить" диапазоны, без генерации всех его значений. А если начать представлять, то будет как с Ирой из Сибири - получим задачу по "осферечиванию" коня в вакууме, не имеющую никакого отношения к исходной задачи ТС
26 апр 13, 16:24    [14236026]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов  [new]
stax..
Guest
Gambit_d.s,
по старинке
WITH s AS
 (SELECT 'Петров' ID ,to_date('05.01.2000', 'dd.mm.yyyy') b ,to_date('10.01.2000', 'dd.mm.yyyy') e    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('11.01.2000', 'dd.mm.yyyy')        ,to_date('20.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('21.01.2000', 'dd.mm.yyyy')        ,to_date('23.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('10.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('24.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Петров'        ,to_date('01.01.2000', 'dd.mm.yyyy')        ,to_date('04.01.2000', 'dd.mm.yyyy')    FROM dual  UNION ALL
  SELECT 'Сидоров'        ,to_date('20.01.2000', 'dd.mm.yyyy')        ,to_date('30.01.2000', 'dd.mm.yyyy')    FROM dual
) 
select id,min(B) "start",max(e) end from 
(select 
    s.*
   ,e-sum(E-B+1) over (partition by ID order by B) gr
 from s
) 
group by 
  id,gr
/

SQL> /

ID      start      END
------- ---------- ----------
Сидоров 01.01.2000 10.01.2000
Петров  01.01.2000 30.01.2000
Сидоров 20.01.2000 30.01.2000


.....
stax
26 апр 13, 18:45    [14236667]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить