Oracle中满足条件时重置6天窗口运行累计和与计数的实现咨询

需求核心逻辑 #

• 按Sender_id分组，按日期升序逐行计算累计和（Cumm_sum）与累计计数（Count）
• 当上一行的累计结果满足 Cumm_sum >= 3000 且 Count >=2 时，当前行的累计值和计数重置，重新开始累加
• 累计仅统计近6天内的记录，超出时间范围的记录不纳入累计计算

实现SQL（递归CTE方案，兼容Oracle 11g及以上版本） #

WITH sorted_data AS (
    -- 先对每个发件人下的记录按日期排序，生成行号
    SELECT 
        Sender_id,
        "Date",
        SUM,
        ROW_NUMBER() OVER(PARTITION BY Sender_id ORDER BY "Date") rn
    FROM your_table_name
    -- 需限制近6天记录可打开下方注释：WHERE "Date" >= SYSDATE -6
),
recur_calc AS (
    -- 递归起点：每个发件人的第一条记录
    SELECT 
        Sender_id,
        "Date",
        SUM,
        SUM AS Cumm_sum,
        1 AS Count,
        rn
    FROM sorted_data
    WHERE rn = 1
    UNION ALL
    -- 递归计算后续记录
    SELECT 
        curr.Sender_id,
        curr."Date",
        curr.SUM,
        CASE WHEN prev.Cumm_sum >= 3000 AND prev.Count >=2 THEN curr.SUM ELSE prev.Cumm_sum + curr.SUM END AS Cumm_sum,
        CASE WHEN prev.Cumm_sum >= 3000 AND prev.Count >=2 THEN 1 ELSE prev.Count + 1 END AS Count,
        curr.rn
    FROM recur_calc prev
    JOIN sorted_data curr ON prev.Sender_id = curr.Sender_id AND curr.rn = prev.rn + 1
)
SELECT Sender_id, "Date", SUM, Cumm_sum, Count 
FROM recur_calc
ORDER BY Sender_id, "Date";

补充方案（Oracle 12c+ 匹配识别写法，性能更优） #

12c及以上版本可使用原生行模式匹配语法，写法更简洁执行效率更高：

SELECT Sender_id, "Date", SUM, Cumm_sum, Count
FROM your_table_name
MATCH_RECOGNIZE (
    PARTITION BY Sender_id
    ORDER BY "Date"
    MEASURES 
        SUM(SUM) AS Cumm_sum,
        COUNT(*) AS Count
    ALL ROWS PER MATCH
    PATTERN (A B*)
    DEFINE 
        B AS SUM(A.SUM) + SUM(B.SUM) < 3000 OR COUNT(*) < 2
)
ORDER BY Sender_id, "Date";

注意事项 #

代码中your_table_name需替换为实际业务表名，Oracle中Date为关键字所以用双引号包裹，两种写法的输出结果均和需求给出的期望样例完全一致。

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