Oracle SQL不使用GROUP BY/PARTITION BY分组连续相同数据的方法

你遇到的是经典的**间隙与孤岛（Gaps and Islands）**问题：你的原始GROUP BY语句把所有数值相同的MONIES记录合并到了一组，但忽略了时间上的连续性——1月和3月的125000虽然数值一致，但中间被2月的143000打断，属于两个独立的"数据孤岛"，需要分开统计时间段。

完全可以用纯SQL实现，不需要PL/SQL，而且这种方法对超大数据集的性能友好，现代关系型数据库（Oracle、SQL Server、PostgreSQL等）都支持。

解决方案代码 #

WITH grouped_data AS (
    SELECT 
        "DATE",
        CUSTOMER_ID,
        FAC_NUM,
        MONIES,
        -- 计算分组ID：全局行号 - 同维度分组内的行号，连续相同MONIES的行将得到相同group_id
        ROW_NUMBER() OVER (ORDER BY "DATE") 
        - ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID, FAC_NUM, MONIES ORDER BY "DATE") AS group_id
    FROM MY_TABLE
)
SELECT 
    CUSTOMER_ID,
    FAC_NUM,
    MONIES,
    MIN("DATE") AS START_DATE,
    MAX("DATE") AS END_DATE
FROM grouped_data
GROUP BY CUSTOMER_ID, FAC_NUM, MONIES, group_id
ORDER BY START_DATE;

代码逻辑解释 #

1. 构造分组ID：

   • 第一个ROW_NUMBER() OVER (ORDER BY "DATE")：给所有记录按日期排序，生成全局唯一的序号；
   • 第二个ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID, FAC_NUM, MONIES ORDER BY "DATE")：在每个(CUSTOMER_ID, FAC_NUM, MONIES)分组内，按日期生成组内序号；
   • 两者的差值在连续相同MONIES的时间段内会保持恒定，这样就把每个独立的连续时间段标记为一个唯一的group_id。

2. 分组统计：
   按CUSTOMER_ID, FAC_NUM, MONIES, group_id分组，取每组的最小日期（START_DATE）和最大日期（END_DATE），就能得到你期望的结果。

性能说明 #

这种基于窗口函数的方法是处理间隙与孤岛问题的最优方案之一，只要你的表在DATE列（或CUSTOMER_ID, FAC_NUM, MONIES, DATE组合列）上有索引，数据库就能高效地完成计算，完全适配超大数据集的需求。

关于PL/SQL的补充（不推荐） #

如果因为特殊场景必须用PL/SQL，高效的实现方式是使用游标遍历+状态跟踪：

• 按DATE排序遍历数据；
• 维护当前的CUSTOMER_ID, FAC_NUM, MONIES状态，以及当前时间段的起始日期；
• 当遇到状态变化时，输出上一个时间段的记录，并更新状态；
• 遍历结束后输出最后一个时间段的记录。

但这种方法的性能远不如纯SQL方案，尤其是大数据集，会带来大量的IO和内存开销，因此优先选择纯SQL的窗口函数解法。

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