PostgreSQL如何按周统计连续两个滚动月均下单的活跃用户数量

核心思路 #

• 用PostgreSQL自带的generate_series函数生成所有需要统计的周一序列，避免手动写多个UNION拼接周维度
• 对每个统计周一，划分两个滚动时间窗口：往前4周内为当前滚动月，往前4-8周为上一滚动月
• 标记每个用户在两个窗口的下单状态，统计同时满足两个窗口都有下单的去重用户数

完整SQL #

WITH all_mondays AS (
    -- 生成覆盖所有订单时间范围的周一序列，date_trunc('week', 时间)默认返回当周周一日期
    SELECT generate_series(
        (SELECT date_trunc('week', min(created)) FROM orders),
        (SELECT date_trunc('week', max(created)) FROM orders),
        interval '1 week'
    )::date AS week_monday
),
user_window_flags AS (
    SELECT
        am.week_monday,
        o.client_id,
        -- 标记用户是否在当前滚动月（统计日往前4周内）有下单
        MAX(CASE WHEN o.created BETWEEN am.week_monday - INTERVAL '4 weeks' AND am.week_monday THEN 1 ELSE 0 END) AS in_current_window,
        -- 标记用户是否在上一滚动月（统计日往前4-8周区间）有下单，减1微秒避免区间边界重叠
        MAX(CASE WHEN o.created BETWEEN am.week_monday - INTERVAL '8 weeks' AND am.week_monday - INTERVAL '4 weeks' - INTERVAL '1 microsecond' THEN 1 ELSE 0 END) AS in_prev_window
    FROM all_mondays am
    -- 只关联统计日往前8周内的订单，减少不必要的计算量
    LEFT JOIN orders o ON o.created <= am.week_monday AND o.created >= am.week_monday - INTERVAL '8 weeks'
    GROUP BY am.week_monday, o.client_id
)
SELECT
    week_monday,
    CASE
        -- 累计数据不足两个滚动月的统计周返回-
        WHEN week_monday - INTERVAL '4 weeks' < (SELECT min(created) FROM orders) THEN '-'
        ELSE COUNT(DISTINCT CASE WHEN in_current_window = 1 AND in_prev_window = 1 THEN client_id END)::TEXT
    END AS "2monthActi"
FROM user_window_flags
GROUP BY week_monday
ORDER BY week_monday;

方案优势 #

1. 扩展性强：后续不管需要统计多长时间的周度数据，都不需要修改代码结构，自动适配时间范围
2. 性能更优：仅需扫描1次订单表，相比多个UNION的写法效率提升明显，订单量越大优势越突出
3. 逻辑清晰：用CTE拆分周维度生成、用户标记、结果统计三个步骤，便于后续调整和维护

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