SQL如何按年月统计3个月周期的累计去重客户数

• 基于交易事实表transaction完成统计：按自然年、自然月维度，计算每个年月对应的连续3个自然月周期内的去重交易客户总数
• 原有方案问题：使用OVER (PARTITION BY ...)搭配UNBOUNDED PRECEDING语法无法得到正确结果，核心原因是UNBOUNDED PRECEDING定义的窗口边界是分区起点到当前行，既无法精准限定3个月的时间范围，且多数SQL引擎不支持滑动窗口下直接使用COUNT(DISTINCT)做跨范围去重，容易出现计数偏差。

通用兼容写法（适配MySQL、Hive、Spark SQL、PostgreSQL等所有主流SQL引擎） #

逻辑分三步：先对单月用户去重，避免同一用户单月多次交易重复参与计算；再关联匹配每个年月对应的3个月时间窗内的所有用户，最终去重计数，自动处理跨年场景。

-- 第一步：提取每个用户产生交易的去重年月，消除单月重复交易记录
WITH user_active_month AS (
    SELECT DISTINCT
        YEAR(trans_time) AS trans_year,
        MONTH(trans_time) AS trans_month,
        user_id
    FROM `transaction`
),
-- 第二步：提取所有有交易记录的年月作为统计基准，避免无交易年月断档导致统计遗漏
stat_dim AS (
    SELECT DISTINCT trans_year, trans_month
    FROM user_active_month
)
-- 第三步：关联匹配3个月窗口内的用户，计算去重总数
SELECT
    s.trans_year,
    s.trans_month,
    COUNT(DISTINCT u.user_id) AS user_cnt_3month
FROM stat_dim s
LEFT JOIN user_active_month u
    ON (
        -- 同年内，匹配当前月及前2个月的记录
        u.trans_year = s.trans_year
        AND u.trans_month BETWEEN s.trans_month - 2 AND s.trans_month
    )
    OR (
        -- 处理跨年场景：例如1月匹配上一年11、12月，2月匹配上一年12月
        u.trans_year = s.trans_year - 1
        AND u.trans_month >= 10 + s.trans_month
    )
GROUP BY s.trans_year, s.trans_month
ORDER BY s.trans_year, s.trans_month;

高阶引擎简化写法（适配PostgreSQL 12+、BigQuery、ClickHouse等支持数组聚合+时间范围窗口的引擎） #

如果使用的SQL引擎支持数组聚合和时间类型的范围窗口，可以用更简洁的写法减少关联计算量：

WITH user_month AS (
    SELECT DISTINCT
        DATE_TRUNC('month', trans_time) AS stat_month,
        user_id
    FROM `transaction`
),
month_user_arr AS (
    SELECT
        stat_month,
        ARRAY_AGG(DISTINCT user_id) AS user_list
    FROM user_month
    GROUP BY stat_month
)
SELECT
    EXTRACT(YEAR FROM stat_month) AS trans_year,
    EXTRACT(MONTH FROM stat_month) AS trans_month,
    (
        SELECT COUNT(DISTINCT uid)
        FROM UNNEST(
            ARRAY_CONCAT_AGG(user_list) OVER (
                ORDER BY stat_month
                RANGE BETWEEN INTERVAL '2 month' PRECEDING AND CURRENT ROW
            )
        ) AS uid
    ) AS user_cnt_3month
FROM month_user_arr
ORDER BY stat_month;

• 禁止直接在原始交易表上做窗口计算：原始表中同一用户单月可能存在多条交易记录，直接计算会导致数据量膨胀，还可能引发重复计数问题
• 不可使用UNBOUNDED PRECEDING作为窗口边界：该边界会取分区内从第一行到当前行的所有数据，会把3个月窗口外的历史用户全部计入，导致结果偏大
• 必须处理跨年逻辑：每年1月、2月的3个月统计窗口会包含上一年的11月、12月数据，漏写该判断会导致年初统计结果明显偏低

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