基于重复时间周期首实例的SQL窗口函数实现方案

需求说明 #

按用户分组处理事件记录，规则如下：

• 筛选用户某类事件的首次发生记录
• 排除该首次事件当日及之后30天内的所有同类事件
• 首次事件发生30天后，重新筛选新的首次事件，同样排除该新事件后30天内的同类事件
• 对所有事件重复上述逻辑

要求兼容MSSQL、Spark SQL等多种关系型数据库，尽量使用标准SQL，避免平台特定语法，优先保证性能。

示例数据 #

CREATE TABLE events (
    UserID INT,
    EventDate DATE
);

INSERT INTO events VALUES
(1, '2022-01-02'),
(1, '2022-01-19'),
(1, '2022-02-01'),
(1, '2022-02-07'),
(1, '2022-02-08'),
(1, '2022-03-19'),
(2, '2022-01-04'),
(2, '2022-01-05'),
(2, '2022-01-06'),
(2, '2022-02-22');

解决方案：递归CTE实现 #

使用标准SQL的递归公共表表达式（CTE）来实现这个逻辑，无需循环或脚本，兼容主流数据库：

方式1：输出所有事件并标记是否保留（带Include列） #

WITH ranked_events AS (
    -- 按用户和事件日期排序，给每个事件编序号
    SELECT 
        UserID,
        EventDate,
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventDate) AS rn
    FROM events
),
recursive_selection AS (
    -- 递归基础：每个用户的第一个事件
    SELECT 
        UserID,
        EventDate,
        rn,
        EventDate + INTERVAL '30' DAY AS window_end -- 该事件的排除窗口截止日期
    FROM ranked_events
    WHERE rn = 1

    UNION ALL

    -- 递归步骤：找到当前窗口之后的第一个事件，更新新的窗口截止日期
    SELECT 
        re.UserID,
        re.EventDate,
        re.rn,
        re.EventDate + INTERVAL '30' DAY AS window_end
    FROM ranked_events re
    JOIN recursive_selection rs 
        ON re.UserID = rs.UserID
        AND re.rn > rs.rn
        AND re.EventDate > rs.window_end
    -- 确保只选当前窗口之后的第一个事件
    WHERE NOT EXISTS (
        SELECT 1 
        FROM ranked_events re2
        WHERE re2.UserID = re.UserID
        AND re2.rn > rs.rn
        AND re2.rn < re.rn
        AND re2.EventDate > rs.window_end
    )
)
-- 关联原表，标记每个事件是否被保留
SELECT 
    e.UserID,
    e.EventDate,
    CASE WHEN rs.EventDate IS NOT NULL THEN 1 ELSE 0 END AS Include
FROM events e
LEFT JOIN recursive_selection rs 
    ON e.UserID = rs.UserID
    AND e.EventDate = rs.EventDate
ORDER BY e.UserID, e.EventDate;

方式2：仅输出被保留的事件 #

WITH ranked_events AS (
    SELECT 
        UserID,
        EventDate,
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventDate) AS rn
    FROM events
),
recursive_selection AS (
    SELECT 
        UserID,
        EventDate,
        rn,
        EventDate + INTERVAL '30' DAY AS window_end
    FROM ranked_events
    WHERE rn = 1

    UNION ALL

    SELECT 
        re.UserID,
        re.EventDate,
        re.rn,
        re.EventDate + INTERVAL '30' DAY AS window_end
    FROM ranked_events re
    JOIN recursive_selection rs 
        ON re.UserID = rs.UserID
        AND re.rn > rs.rn
        AND re.EventDate > rs.window_end
    WHERE NOT EXISTS (
        SELECT 1 
        FROM ranked_events re2
        WHERE re2.UserID = re.UserID
        AND re2.rn > rs.rn
        AND re2.rn < re.rn
        AND re2.EventDate > rs.window_end
    )
)
SELECT UserID, EventDate
FROM recursive_selection
ORDER BY UserID, EventDate;

逻辑说明 #

1. ranked_events：按用户分组、事件日期排序，给每个事件分配序号，方便递归时定位事件顺序。
2. recursive_selection：
   • 基础部分：取每个用户的第一个事件作为初始保留事件，计算其排除窗口截止日期（事件日期+30天）。
   • 递归部分：关联上一轮保留的事件，找到该窗口之后的第一个事件作为新的保留事件，同时更新窗口截止日期。NOT EXISTS子句确保只选窗口后的首个事件，避免重复选中。
3. 最后通过左关联或直接查询递归结果，得到期望的输出格式。

兼容性说明 #

• 递归CTE是SQL:1999标准的一部分，MSSQL、Spark SQL（2.0及以上版本）均支持。
• 日期加法INTERVAL '30' DAY是标准语法，若部分数据库有特殊写法（如MSSQL用DATEADD(day, 30, EventDate)），可根据平台调整，核心逻辑不变。

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