Oracle查询需求：按连续Type分组并按日期排序（日期区间无重叠）

需求说明 #

将数据按连续的TYPE分组，合并每组的日期区间（取组内最早的FR_DATE和最晚的TO_DATE），汇总INCOME金额，确保日期区间不重叠且按时间顺序排列。

原始数据 #

TYPE    FR_DATE             TO_DATE             INCOME
A       04/14 01:04:15      04/14 22:10:15      100 
A       04/15 03:02:22      04/16 18:10:44      200
B       04/17 05:13:22      04/17 20:11:44      300
B       04/18 01:24:22      04/20 23:13:44      100
A       04/22 05:11:22      04/22 19:25:44      400
A       04/23 02:12:22      04/24 17:43:44      200
B       04/25 02:19:22      04/28 18:32:44      500

期望结果 #

TYPE    FR_DATE             TO_DATE             INCOME_SUM
A       04/14 01:04:15      04/16 18:10:44      300 
B       04/17 05:13:22      04/20 23:13:44      400 
A       04/22 05:11:22      04/24 17:43:44      600
B       04/25 02:19:22      04/28 18:32:44      500 

解决方案 #

可以通过Oracle窗口函数生成分组标识，再聚合计算得到结果，具体SQL如下：

WITH grouped_data AS (
    SELECT 
        t.*,
        -- 生成连续同TYPE的分组ID：当当前TYPE与上一条不同时，分组ID加1
        SUM(CASE WHEN LAG(TYPE) OVER(ORDER BY FR_DATE) = TYPE THEN 0 ELSE 1 END) 
            OVER(ORDER BY FR_DATE) AS group_id
    FROM your_table t
)
SELECT 
    TYPE,
    MIN(FR_DATE) AS FR_DATE,
    MAX(TO_DATE) AS TO_DATE,
    SUM(INCOME) AS INCOME_SUM
FROM grouped_data
GROUP BY group_id, TYPE
ORDER BY FR_DATE;

逻辑说明 #

1. 生成分组标识：使用LAG(TYPE) OVER(ORDER BY FR_DATE)获取当前记录的上一条TYPE值，通过CASE判断是否和当前TYPE一致，不一致则标记为新分组，用SUM窗口函数累加得到唯一的group_id，实现连续同TYPE的记录归为一组。
2. 聚合计算：按group_id和TYPE分组，取每组最早的FR_DATE、最晚的TO_DATE，汇总INCOME金额。
3. 排序：按FR_DATE排序保证结果的时间顺序。

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