Snowflake中基于28天回溯窗口动态过滤重复事件ID的计数方案

核心逻辑说明 #

要实现动态28天回溯窗口内首次出现计数，关键是对每个事件的每日出现记录，判断其在「当前日期往前28天」的窗口内是否是首次出现，而非全局首次。步骤如下：

1. 先过滤每日重复的EVENT_ID，得到每日唯一事件集合
2. 对每个事件，用窗口函数追踪其上一次出现的日期
3. 判断当前日期与上一次出现日期的间隔是否超过28天（或首次出现），满足则标记为需计数的事件
4. 在指定日期范围内统计每日符合条件的事件数量

修正后的Snowflake代码 #

-- 定义统计日期范围
WITH DATE_RANGE AS (
    SELECT
        '2023-07-12'::DATE AS START_DATE,
        '2023-07-18'::DATE AS END_DATE
),
-- 第一步：过滤每日重复的EVENT_ID，得到每日唯一事件
DAILY_UNIQUE_EVENTS AS (
    SELECT DISTINCT
        DAY_DT,
        EVENT_ID
    FROM EVENTS
),
-- 第二步：对每个事件，计算上一次出现的日期
EVENTS_WITH_LAST_OCCURRENCE AS (
    SELECT
        DAY_DT,
        EVENT_ID,
        -- 按EVENT_ID分组，按日期排序，取上一次出现的日期
        LAG(DAY_DT) OVER (PARTITION BY EVENT_ID ORDER BY DAY_DT) AS LAST_SEEN_DT
    FROM DAILY_UNIQUE_EVENTS
),
-- 第三步：标记出在28天窗口内首次出现的事件
QUALIFYING_EVENTS AS (
    SELECT
        DAY_DT,
        EVENT_ID
    FROM EVENTS_WITH_LAST_OCCURRENCE
    -- 满足以下任一条件则计入：
    -- 1. 事件首次出现（无上次记录）
    -- 2. 上次出现日期距离当前日期超过28天（即不在当前28天窗口内）
    WHERE LAST_SEEN_DT IS NULL 
       OR DAY_DT - LAST_SEEN_DT > 28
)
-- 第四步：在指定日期范围内统计每日符合条件的事件数
SELECT
    Q.DAY_DT,
    COUNT(Q.EVENT_ID) AS DISTINCT_NEW_EVENTS
FROM QUALIFYING_EVENTS Q
JOIN DATE_RANGE D
    ON Q.DAY_DT BETWEEN D.START_DATE AND D.END_DATE
GROUP BY Q.DAY_DT
ORDER BY Q.DAY_DT;

针对示例场景的验证 #

比如某EVENT_ID在6月14日首次出现：

• 7月12日的回溯窗口是6月14日~7月12日（共28天），该事件在窗口内已出现过，因此7月12日不会计数
• 7月14日的回溯窗口是6月16日~7月14日，6月14日不在窗口内，若该事件在7月14日再次出现，此时距离上次出现（6月14日）已超过28天，会被计入7月14日的统计

代码优化点说明 #

• 替换了全局首次出现的判断逻辑，改为基于每个事件的最近一次出现日期做动态判断，适配28天窗口的移动特性
• 简化了CTE层级，去掉了冗余的日期生成和范围计算逻辑，直接通过窗口函数实现核心需求
• 保留了每日去重的逻辑，符合用户初始需求

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