基于动态日期范围分组：按ID聚合90天窗口内的SQL记录

这类需求属于动态会话窗口问题，普通的固定范围窗口函数无法直接处理，因为窗口的起始点会根据前一个窗口的边界动态调整。下面是基于递归CTE的解决方案，完全匹配你的规则：

示例数据准备 #

首先是你提到的建表和插入数据的SQL：

CREATE TABLE transactions (
    RowId INT PRIMARY KEY,
    ID INT,
    Date DATE,
    Amount DECIMAL(10,2)
);

INSERT INTO transactions (RowId, ID, Date, Amount)
VALUES
(1, 133742, '2023-01-01', 100.00),
(2, 133742, '2023-02-15', 200.00),
(3, 133742, '2023-03-20', 150.00),
(4, 133742, '2023-05-01', 300.00),
(5, 133742, '2023-09-01', 250.00),
(6, 987654, '2023-01-10', 400.00);

解决方案SQL #

WITH sorted_trans AS (
    -- 按ID分组、日期排序，为每条记录分配组内序号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn
    FROM transactions
),
recursive_window_builder AS (
    -- 递归初始：每个ID的第一条记录作为第一个窗口的起点
    SELECT 
        ID,
        Date AS window_start,
        DATE_ADD(Date, INTERVAL 90 DAY) AS window_end,
        Amount AS total_amount,
        rn
    FROM sorted_trans
    WHERE rn = 1

    UNION ALL

    -- 递归迭代：处理后续记录，判断是否属于当前窗口
    SELECT 
        st.ID,
        -- 当前记录超出上一个窗口范围则开启新窗口
        CASE WHEN st.Date > rwb.window_end THEN st.Date ELSE rwb.window_start END,
        -- 新窗口的结束为当前日期+90天，否则沿用原窗口结束
        CASE WHEN st.Date > rwb.window_end THEN DATE_ADD(st.Date, INTERVAL 90 DAY) ELSE rwb.window_end END,
        -- 累加金额，新窗口则重置为当前记录金额
        CASE WHEN st.Date > rwb.window_end THEN st.Amount ELSE rwb.total_amount + st.Amount END,
        st.rn
    FROM recursive_window_builder rwb
    JOIN sorted_trans st ON st.ID = rwb.ID AND st.rn = rwb.rn + 1
),
window_aggregates AS (
    -- 提取每个窗口的最终聚合结果：识别新窗口的起始记录
    SELECT 
        ID,
        window_start AS group_start_date,
        window_end AS group_end_date,
        total_amount AS aggregated_amount
    FROM (
        SELECT 
            *,
            LAG(window_start) OVER (PARTITION BY ID ORDER BY rn) AS prev_window_start
        FROM recursive_window_builder
    ) t
    WHERE prev_window_start IS NULL OR window_start != prev_window_start
)
SELECT * FROM window_aggregates ORDER BY ID, group_start_date;

结果说明 #

执行上述SQL后，输出结果完全符合你的规则：

• ID133742的前3条记录（RowId1-3）处于2023-01-01的90天窗口内，聚合金额450
• RowId4的日期超出第一个窗口，开启新窗口，单独聚合300
• RowId5的日期远超之前窗口，单独聚合250
• ID987654的记录单独成窗口，聚合400

关键逻辑解释 #

1. 排序编号：用ROW_NUMBER()给每个ID下的记录按日期排序，确保递归处理的顺序正确
2. 递归构建窗口：从每个ID的第一条记录开始，逐行判断当前记录是否属于上一个窗口，超出则开启新窗口，否则累加金额
3. 提取聚合结果：通过LAG()函数对比当前窗口起始和上一条记录的窗口起始，识别每个新窗口的起始记录，从而得到最终的聚合分组

内容的提问来源于stack exchange，提问作者G. Maen