Oracle按Product_ID分组对比Amount相邻值并合并连续相同区间方案

这个需求属于SQL中典型的孤岛（Islands）问题，需要分组识别连续相同的数值区间，具体实现逻辑如下：

注意事项 #

你贴出的期望输出存在笔误，和源数据对应不上，修正后的正确输出应该为：

完整SQL代码 #

WITH 
-- 第一步：将月份字符串转为日期类型，保证排序逻辑正确
t1 AS (
    SELECT 
        Org_ID,
        Product_ID,
        Order_Month,
        Amount,
        TO_DATE(Order_Month, 'MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') AS month_date
    FROM your_table_name
),
-- 第二步：标记当前行金额和上一行是否相同，相同记0、不同记1
t2 AS (
    SELECT 
        t1.*,
        CASE WHEN Amount = LAG(Amount, 1) OVER (PARTITION BY Org_ID, Product_ID ORDER BY month_date) 
             THEN 0 
             ELSE 1 
        END AS is_new_group
    FROM t1
),
-- 第三步：对标记做累加，得到每个连续相同金额的分组ID
t3 AS (
    SELECT 
        t2.*,
        SUM(is_new_group) OVER (PARTITION BY Org_ID, Product_ID ORDER BY month_date) AS group_id
    FROM t2
)
-- 第四步：按分组聚合，拼接月份区间
SELECT 
    Org_ID,
    Product_ID,
    MIN(Order_Month) || ' to ' || MAX(Order_Month) AS Order_Month,
    Amount
FROM t3
GROUP BY Org_ID, Product_ID, Amount, group_id
ORDER BY Org_ID, Product_ID, MIN(month_date);

逻辑说明 #

• 第一层CTEt1：处理月份格式，因为字符串格式的JAN-2021无法直接排序，需要先转为Oracle日期类型，同时指定英文月份的解析参数，避免数据库语言环境不同导致报错。
• 第二层CTEt2：用LAG窗口函数取同机构、同产品下上一行的金额，判断当前行是否是新的金额区间的起点。
• 第三层CTEt3：对区间起点标记做累加，连续相同金额的行累加值相同，就得到了每个连续区间的唯一分组ID。
• 最终聚合：按机构、产品、金额、分组ID聚合，取区间最小和最大月份拼接即可得到要求的结果。

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