如何用Oracle SQL实现连续区间的Gaps and Islands分组求和？

问题描述 #

现有如下数据集：

期望得到的结果：

规则：同一SET分组内，若当前行的START值等于上一行的END值+1，则将这些连续区间合并为一个区间，并汇总QTY得到TOTAL_QTY。

解决方案 #

可以使用Oracle窗口函数识别连续区间的分组，再对分组进行聚合计算，具体SQL如下：

WITH interval_groups AS (
    SELECT 
        set_col,
        start_val,
        end_val,
        qty,
        -- 标记连续区间分组：当前行START不等于上一行END+1时，分组编号加1
        SUM(CASE WHEN start_val = LAG(end_val) OVER (PARTITION BY set_col ORDER BY start_val) + 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY set_col ORDER BY start_val) AS group_id
    FROM 
        your_table_name -- 替换为你的实际表名
)
SELECT 
    set_col AS "SET",
    MIN(start_val) AS "START",
    MAX(end_val) AS "END",
    SUM(qty) AS TOTAL_QTY
FROM 
    interval_groups
GROUP BY 
    set_col, group_id
ORDER BY 
    set_col, "START";

逻辑说明 #

1. CTE（interval_groups）阶段：
   • 用LAG(end_val) OVER (PARTITION BY set_col ORDER BY start_val)获取同SET分组内当前行的上一行END值。
   • 通过CASE判断当前行是否属于连续区间：若START等于上一行END+1，说明和上一行同属一个区间，分组编号不变；否则新建一个分组编号，以此将所有连续区间标记为独立的group_id。
2. 聚合阶段：
   • 按set_col和group_id分组，取每组的最小START作为合并区间的起始值，最大END作为结束值，同时汇总该组的QTY得到TOTAL_QTY。

注意事项 #

• 请将SQL中的your_table_name替换为实际表名。
• 若SET、START、END是Oracle关键字，需用双引号包裹（如示例中的"SET"）。

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