如何通过Snowflake SQL统计当月及前三个月均出现的ID数量并按月分组

原SQL错误点 #

• DATEADD函数参数使用错误：Snowflake中DATEADD语法为DATEADD(时间单位, 偏移量, 待计算日期)，原代码参数顺序完全颠倒，且错误使用GETDATE()（当前系统时间）作为计算基准，而非每行数据的dddate字段。
• 月份匹配逻辑错误：使用MONTH()函数只返回1-12的月份数字，跨年份时会出现匹配混乱，比如2020年1月的前三个月属于2019年，仅用月份数字无法正确匹配。
• JOIN逻辑缺失月份关联：仅关联了ID相等，没有关联「前三个月的月份和当前统计月份的对应关系」，会导致ID只要历史出现过就会被误算。
• 字段引用错误：外层查询引用ID字段，但CTE中对应的字段名是CURR_ID，直接运行会报字段不存在错误。

正确实现方案 #

先对每个ID每月的出现情况做去重聚合，再用滑动窗口函数判断前三个月的出现状态，逻辑简洁且性能更好：

WITH id_month_distinct AS (
    -- 先聚合得到每个ID每个月的唯一出现记录，消除单月多次出现的重复数据
    SELECT DISTINCT
        DATE_TRUNC('MONTH', dddate) AS stat_month,
        id
    FROM my_table
    WHERE dddate >= '2019-01-01' AND dddate <= '2020-01-31'
),
id_month_check AS (
    SELECT
        stat_month,
        id,
        -- 统计该ID在当前月往前推1-3个月的区间内的出现次数
        COUNT(id) OVER (
            PARTITION BY id 
            ORDER BY stat_month 
            RANGE BETWEEN INTERVAL '3 months' PRECEDING AND INTERVAL '1 month' PRECEDING
        ) AS prev_3month_cnt
    FROM id_month_distinct
)
SELECT
    stat_month AS 统计月份,
    COUNT(DISTINCT id) AS 符合条件去重ID数
FROM id_month_check
WHERE 
    -- 限定统计范围为2019年4月到2020年1月
    stat_month >= '2019-04-01' AND stat_month <= '2020-01-01'
    -- 满足条件：当月已出现（在id_month_distinct中存在） + 前三个月至少出现1次
    AND prev_3month_cnt >= 1
GROUP BY 1
ORDER BY 1

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