求助：SQL实现事件表valid_event_flg与event_nbr列的正确生成

现有数据与需求 #

现有事件表包含record_id、person_id、event_date三列，数据如下：

(100, 1, '2023-06-01'),
(101, 1, '2023-06-15'),
(102, 1, '2023-06-30'),
(103, 1, '2023-07-30'),
(104, 1, '2023-08-30'),
(105, 1, '2023-09-30'),
(106, 2, '2023-07-01'),
(107, 2, '2023-07-30'),
(108, 2, '2023-08-01'),
(109, 2, '2023-09-30'),
(110, 2, '2023-10-30'),
(111, 2, '2023-11-03')

需要新增两个列：

• valid_event_flg：当为该person_id的首个事件，或距离该person_id的上一个有效事件至少28天时，设为1，否则为0；
• event_nbr：有效事件从1开始递增计数，无效事件设为0。

期望输出：

(100, 1, '2023-06-01', 1, 1),
(101, 1, '2023-06-15', 0, 0),
(102, 1, '2023-06-30', 1, 2),
(103, 1, '2023-07-30', 1, 3),
(104, 1, '2023-08-30', 1, 4),
(105, 1, '2023-09-30', 1, 5),
(106, 2, '2023-07-01', 1, 1),
(107, 2, '2023-07-30', 1, 2),
(108, 2, '2023-08-01', 0, 0),
(109, 2, '2023-09-30', 1, 3),
(110, 2, '2023-10-30', 1, 4),
(111, 2, '2023-11-03', 0, 0)

原代码问题 #

原代码通过关联所有历史事件判断间隔，错误匹配了任意早于当前事件28天的事件，而非追踪上一个有效事件的日期，导致有效性判断逻辑偏差。

修正后的SQL代码 #

WITH event AS (
    SELECT 
        record_id, 
        person_id, 
        event_date::DATE AS event_date,
        -- 给每个用户的事件按日期排序生成序号
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY event_date) AS rn
    FROM (VALUES
        (100, 1, '2023-06-01'),
        (101, 1, '2023-06-15'),
        (102, 1, '2023-06-30'),
        (103, 1, '2023-07-30'),
        (104, 1, '2023-08-30'),
        (105, 1, '2023-09-30'),
        (106, 2, '2023-07-01'),
        (107, 2, '2023-07-30'),
        (108, 2, '2023-08-01'),
        (109, 2, '2023-09-30'),
        (110, 2, '2023-10-30'),
        (111, 2, '2023-11-03')
    ) AS t(record_id, person_id, event_date)
),
-- 递归CTE追踪每个事件的上一个有效事件日期、有效性标记和计数
recursive_event AS (
    -- 初始：每个用户的第一个事件，标记为有效，计数1，记录当前日期为上一个有效日期
    SELECT 
        record_id,
        person_id,
        event_date,
        1 AS valid_event_flg,
        1 AS event_nbr,
        event_date AS last_valid_date
    FROM event
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归处理后续事件
    SELECT
        e.record_id,
        e.person_id,
        e.event_date,
        -- 判断当前事件与上一个有效事件的间隔是否≥28天，是则标记为1，否则0
        CASE WHEN e.event_date - r.last_valid_date >= 28 THEN 1 ELSE 0 END AS valid_event_flg,
        -- 有效则计数+1，无效则保持0
        CASE WHEN e.event_date - r.last_valid_date >= 28 THEN r.event_nbr + 1 ELSE 0 END AS event_nbr,
        -- 有效则更新上一个有效日期为当前日期，否则沿用之前的
        CASE WHEN e.event_date - r.last_valid_date >= 28 THEN e.event_date ELSE r.last_valid_date END AS last_valid_date
    FROM event e
    JOIN recursive_event r ON e.person_id = r.person_id AND e.rn = r.rn + 1
)
-- 输出结果，按用户和日期排序
SELECT record_id, person_id, event_date, valid_event_flg, event_nbr
FROM recursive_event
ORDER BY person_id, event_date;

关键逻辑说明 #

1. 先给每个用户的事件按日期排序生成序号rn，方便递归遍历；
2. 递归CTE从每个用户的第一个事件开始，初始化有效性标记、计数和上一个有效日期；
3. 后续每个事件基于上一个有效事件的日期判断：间隔≥28天则标记为有效，更新计数和上一个有效日期；否则标记为无效，计数为0，沿用之前的有效日期；
4. 最终按用户和日期排序输出，完全匹配期望结果。

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