如何合并连续的日期范围Oracle

Question

我遇到了一个问题。我不知道如何基于两个因素将连续的日期范围行合并在一起。其中一个因素对我来说没问题，但第二个因素让我头疼。

想象一下这个表格结构，有四种可能的情况。

  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

---

不确定act_level = prev_level背后的逻辑应该是什么，但可以看一下Itzik Ben-Gan的Packing Intervals。虽然它是为SQL Server编写的，但Oracle支持其中使用的所有分析函数。 - Vladimir Baranov

Answer 1

请尝试执行此查询：

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

Answer 2

以下的SQL语句回答了似乎是预期问题：识别连续的间隔（date_from，date_to）和级别lvl按emp_id并将它们合并为一行（emp_id，lvl，date_from，date_to）。

关键是为连续的（date_from，date_to）和lvl值分配不同的组ID。整个过程在2个内联视图tab0和tab1中完成：

1. tab0中的diff_levels和diff_dates在我们跨越组边界时非零。 2. tab1中的ranked_levels和ranked_dates生成差异字段的累积（从而使lvl和（date_from，date_to）组成不同的组）。 3. 最终表只是根据（ranked_levels，ranked_groups）进行分组。

with 
    -- tab0:     
    tab0 as (
        select
            emp_id,
            lvl,
            date_from,
            date_to,
            case 
                when 
                    lvl != lag(lvl, 1, lvl) 
                    over(partition by emp_id order by date_from)
                then 1
                else 0
            end diff_levels,
            date_from - lag(date_to, 1, date_from) 
            over(partition by emp_id order by date_from) - 1 diff_dates
        from src_table),
    -- tab1:
    tab1 as (
        select
            emp_id,
            lvl,
            date_from,
            date_to,
            sum(diff_levels) over(
                partition by emp_id
                order by date_from
                range between unbounded preceding and current row) ranked_levels,
            sum(diff_dates) over(
                partition by emp_id
                order by date_from 
                range between unbounded preceding and current row) ranked_dates
        from tab0)
select
    emp_id,
    lvl,
    min(date_from) date_from,
    max(date_to) date_to
from tab1
group by 
    emp_id,
    lvl,
    ranked_levels,
    ranked_dates
order by emp_id, lvl;

这个SQL语句在ORACLE 11g上进行了测试，但由于它是ANSI SQL，所以可以在任何地方运行。

我使用了之前答案中提供的小表格。

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');

- Chris