如何在Oracle中按日期区间分组Col1+Col2重复数据并获取起止日期

需求说明 #

需要对Col1+Col2+Quantity组合相同的记录，识别其中的连续日期区间，最终输出每组的起始日期、结束日期及对应Quantity值。

测试数据创建 #

执行以下DDL语句创建测试表tab1：

Create table tab1 as 
select 'I1' col1,'L1' col2,to_date('01-07-2024','DD-MM-YYYY') Date_col,10 quantity from dual 
union
select 'I1','L1',to_date('02-07-2024','DD-MM-YYYY'),10 from dual 
union
select 'I1','L1',to_date('03-07-2024','DD-MM-YYYY'),10 from dual 
union
select 'I1','L1',to_date('12-07-2024','DD-MM-YYYY'),10 from dual 
union
select 'I1','L1',to_date('13-07-2024','DD-MM-YYYY'),10 from dual 
union
select 'I1','L1',to_date('14-07-2024','DD-MM-YYYY'),10 from dual 
union
select 'I2','L2',to_date('05-07-2024','DD-MM-YYYY'),26 from dual 
union
select 'I2','L2',to_date('06-07-2024','DD-MM-YYYY'),26 from dual 
union
select 'I2','L2',to_date('07-07-2024','DD-MM-YYYY'),26 from dual 
union
select 'I2','L2',to_date('08-07-2024','DD-MM-YYYY'),26 from dual 
union
select 'I2','L2',to_date('10-07-2024','DD-MM-YYYY'),34 from dual 
union
select 'I2','L2',to_date('11-07-2024','DD-MM-YYYY'),34 from dual 
union
select 'I2','L2',to_date('12-07-2024','DD-MM-YYYY'),28 from dual 
union
select 'I2','L2',to_date('13-07-2024','DD-MM-YYYY'),28 from dual 
union
select 'I2','L2',to_date('14-07-2024','DD-MM-YYYY'),28 from dual 
union
select 'I2','L2',to_date('21-07-2024','DD-MM-YYYY'),20 from dual 
union
select 'I2','L2',to_date('22-07-2024','DD-MM-YYYY'),20 from dual 
union
select 'I2','L2',to_date('23-07-2024','DD-MM-YYYY'),20 from dual 
union
select 'I2','L2',to_date('24-07-2024','DD-MM-YYYY'),20 from dual;

实现思路与SQL语句 #

核心思路是利用窗口函数生成连续分组标识：

1. 按Col1、Col2、Quantity分组，对每组内的Date_col进行排序，生成行号。
2. 用Date_col减去行号对应的天数，得到一个固定值——同一连续日期区间的记录会得到相同的这个值，以此作为分组依据。
3. 最后按Col1、Col2、Quantity和这个分组标识聚合，取每组的最小日期（起始）和最大日期（结束）。

执行以下SQL即可得到期望结果：

SELECT
    Col1,
    Col2,
    TO_CHAR(MIN(Date_col), 'DD/Mon/YYYY') AS Startdate,
    TO_CHAR(MAX(Date_col), 'DD/Mon/YYYY') AS Enddate,
    Quantity
FROM (
    SELECT
        Col1,
        Col2,
        Date_col,
        Quantity,
        -- 生成连续区间的分组标识
        Date_col - ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Quantity ORDER BY Date_col) AS grp_id
    FROM tab1
) t
GROUP BY Col1, Col2, Quantity, grp_id
ORDER BY Col1, Col2, Startdate;

执行结果 #

执行上述SQL后，输出结果与期望一致：

Col1   Col2   Startdate      Enddate      Quantity
I1     L1     01/Jul/2024    03/Jul/2024  10
I1     L1     12/Jul/2024    14/Jul/2024  10
I2     L2     05/Jul/2024    08/Jul/2024  26
I2     L2     10/Jul/2024    11/Jul/2024  34
I2     L2     12/Jul/2024    14/Jul/2024  28
I2     L2     21/Jul/2024    24/Jul/2024  20

内容的提问来源于stack exchange，提问作者U12