Как объединить последовательный диапазон дат Oracle

Я столкнулся с проблемой. Я не могу понять, как объединить последовательные строки диапазона дат вместе на основе двух измерений. Один мне подходит, но второй доставляет неприятности

Представим себе таблицу в этой структуре с четырьмя возможными сценариями.

  emp_id  |  level  |  date_from   |   date_to    
--------------------------------------------------
    1     |   A     |  7/31/2015   |  3/31/2016
    1     |   A     |  4/1/2016    |  1/1/3000

    2     |   A     |  7/31/2015   |  1/1/3000

    3     |   A     |  5/31/2015   |  12/31/2015
    3     |   B     |  1/1/2016    |  3/31/2016
    3     |   A     |  4/1/2016    |  6/30/2016
    3     |   B     |  7/1/2016    |  1/1/3000

    4     |   A     |  5/31/2015   |  12/31/2015
    4     |   A     |  1/1/2016    |  6/30/2016
    4     |   B     |  7/1/2016    |  1/1/3000

Я хочу объединить только те строки, которые имеют последовательные диапазоны дат и act_level = prev_level

Я пытался сделать что-то подобное

SELECT emp_id
, level
, date_from
, date_to
--
, CASE
    WHEN lag(level) over (partition by emp_id order by date_from) = level THEN 
         CASE
             WHEN lag(date_to) over (partition by emp_id, level order by date_from) = date_from-1 
               THEN lag(date_from) over (partition by code_employee, level_name order by date_from)
             ELSE NULL
         END
    ELSE 
         CASE
             WHEN lag(level) over (partition by emp_id order by date_from) = level
                     OR
                  lead(level) over (partition by emp_id order by date_from) = level
                THEN NULL
             ELSE date_from
         END
  END date_from_new
, date_to as date_to_new
--
FROM src_table
--
WHERE 1=1

это дает мне почти те результаты, которые я хочу:

  emp_id  |  level  |  date_from   |   date_to   |  d_from_new | d_from_to 
--------------------------------------------------------------------------
    1     |   A     |  7/31/2015   |  3/31/2016  |           | 3/31/2016
    1     |   A     |  4/1/2016    |  1/1/3000   | 7/31/2015 | 1/1/3000

    2     |   A     |  7/31/2015   |  1/1/3000   | 7/31/2015 | 1/1/3000

    3     |   A     |  5/31/2015   |  12/31/2015 | 5/31/2015 | 12/31/2015
    3     |   B     |  1/1/2016    |  3/31/2016  |  1/1/2016 | 3/31/2016
    3     |   A     |  4/1/2016    |  6/30/2016  |  4/1/2016 | 6/30/2016  
    3     |   B     |  7/1/2016    |  1/1/3000   |  7/1/2016 | 1/1/3000 

    4     |   A     |  5/31/2015   |  12/31/2015 |           | 12/31/2015
    4     |   A     |  1/1/2016    |  6/30/2016  | 5/31/2015 | 6/30/2016
    4     |   B     |  7/1/2016    |  1/1/3000   | 7/1/2016  | 1/1/3000

Я просто отфильтрую результат для d_from_new (date_from_new) не нулевых значений. Но я не уверен, что произойдет, если будет, например, в 3 раза тот же уровень с последовательным диапазоном дат или в 8 раз.

И, честно говоря, мне этот запрос не нравится :)

Есть ли у вас какое-нибудь решение, "благоприятное для парфюмерии" и "приятное для глаз"?

Mr.P 04.10.2016 источник

comment

Не уверен, какая логика должна стоять за act_level = prev_level, но взгляните на Интервалы упаковки Автор: Ицик Бен-Ган. Он написан для SQL Server, но Oracle поддерживает все используемые там аналитические функции. - Vladimir Baranov 04.10.2016

Ответы (1)

arrow_upward
1
arrow_downward

Пожалуйста, попробуйте этот запрос:

select emp_id, lvl, min(date_from) df, max(date_to) dt
  from (
    select s2.*, rn - sum(marker) over (order by rn) as grp
      from (
        select s1.*,
               row_number() over (order by emp_id, date_from) rn,
               case when lag(lvl) over (partition by emp_id order by date_from) 
                         = lvl
                     and lag(date_to) over (partition by emp_id order by date_from) + 1 
                         = date_from
                    then 1
                    else 0
               end marker
          from src_table s1 ) s2 )
  group by emp_id, lvl, grp
  order by emp_id, min(date_from)

В первом подзапросе S1 я добавил маркер, где 1 присваивается, если предыдущий уровень соответствует, а даты идут подряд. Во втором подзапросе этот маркер используется для построения столбца GRP, который имеет одинаковые значения для всех совпадающих строк. Этот столбец используется в запросе окончательной группировки, чтобы найти минимум date_from и максимум date_to. Пожалуйста, выполняйте внутренние запросы отдельно, чтобы увидеть, что происходит на каждом этапе. Проверено, если имеется более двух последовательных строк.

Данные испытаний и вывод:

create table src_table (emp_id number(6), lvl varchar2(2), date_from date, date_to date);
insert into src_table values (1, 'A', date '2015-07-31', date '2016-03-31');
insert into src_table values (1, 'A', date '2016-04-01', date '3000-01-01');
insert into src_table values (2, 'A', date '2015-07-31', date '3000-01-01');
insert into src_table values (3, 'A', date '2015-05-31', date '2015-12-31');
insert into src_table values (3, 'B', date '2016-01-01', date '2016-03-31');
insert into src_table values (3, 'A', date '2016-04-01', date '2016-06-30');
insert into src_table values (3, 'B', date '2016-07-01', date '3000-01-01');
insert into src_table values (4, 'A', date '2015-05-31', date '2015-12-31');
insert into src_table values (4, 'A', date '2016-01-01', date '2016-06-30');
insert into src_table values (4, 'B', date '2016-07-01', date '3000-01-01');

 EMP_ID LVL DF          DT
------- --- ----------- -----------
      1 A   2015-07-31  3000-01-01
      2 A   2015-07-31  3000-01-01
      3 A   2015-05-31  2015-12-31
      3 B   2016-01-01  2016-03-31
      3 A   2016-04-01  2016-06-30
      3 B   2016-07-01  3000-01-01
      4 A   2015-05-31  2016-06-30
      4 B   2016-07-01  3000-01-01

8 rows selected

Ponder Stibbons 05.10.2016

Как объединить последовательный диапазон дат Oracle

Ответы (1)

Похожие вопросы