基于事件表，如何用Redshift/PostgreSQL统计每月新闻通讯订阅总人数？

当然可以用SQL实现这个需求，而且Redshift和PostgreSQL都能完美支持。我会结合你的业务逻辑，一步步拆解并给出适配的查询语句。

首先，核心需求是计算每个月末的有效订阅人数——也就是当月新订阅的用户，加上之前已经订阅且当月没有退订（或者退订后又重新订阅）的用户。要实现这个，我们需要追踪每个用户在每个月末的最终订阅状态，而不是只看当月的单次行为。

思路拆解 #

1. 追踪用户的状态变化周期：每个用户的订阅状态会随时间变化（订阅→退订→重新订阅等），我们需要记录每个状态的起始和结束时间。
2. 生成完整的月份序列：确保即使某个月没有任何订阅/退订行为，也能展示该月的订阅人数（等于上月末的人数）。
3. 匹配月份与用户状态：判断每个用户在每个月末是否处于订阅状态，最后统计总数。

完整SQL查询（兼容Redshift/PostgreSQL） #

WITH user_action_timeline AS (
    SELECT
        user_id,
        timestamp,
        action,
        -- 获取当前行为的下一个行为时间，用于确定状态的结束节点
        LEAD(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS next_action_time
    FROM events
),
-- 生成所有需要统计的月份（递归方式，兼容Redshift）
RECURSIVE months AS (
    SELECT MIN(date_trunc('month', timestamp)) AS month 
    FROM events
    UNION ALL
    SELECT month + INTERVAL '1 month' 
    FROM months
    WHERE month + INTERVAL '1 month' <= (SELECT MAX(date_trunc('month', timestamp)) FROM events)
)
SELECT
    m.month::DATE AS month,
    COUNT(DISTINCT uat.user_id) AS subscribers
FROM months m
LEFT JOIN user_action_timeline uat
    ON uat.action = 'subscribed'
    -- 订阅行为发生在当月或之前
    AND uat.timestamp <= (m.month + INTERVAL '1 month' - INTERVAL '1 day')
    -- 要么没有后续退订行为，要么退订发生在当月之后
    AND (uat.next_action_time IS NULL OR uat.next_action_time > (m.month + INTERVAL '1 month' - INTERVAL '1 day'))
GROUP BY m.month
ORDER BY m.month;

逻辑说明 #

• user_action_timeline：用LEAD()窗口函数为每个订阅行为绑定后续的行为时间（如果是退订，就是订阅状态的结束时间；如果没有后续行为，状态持续到当前）。
• months：通过递归CTE生成从最早行为月份到最晚行为月份的所有连续月份，避免遗漏无行为的月份。
• 主查询：将每个月份与用户的订阅周期匹配——只要用户的订阅状态覆盖到当月最后一天，就计入当月订阅人数。

验证示例数据 #

拿你给出的例子测试：

• 2017-01：用户1、2、3的订阅状态均覆盖1月末 → 计数3
• 2017-02：新增用户4，加上之前3人 → 计数4
• 2017-03：用户1的退订发生在3月15日（早于3月末），不计入；用户2退订后重新订阅，状态覆盖3月末；用户3、4持续订阅 → 最终计数3，完全符合你的预期。

Redshift注意事项 #

如果你的Redshift版本对递归CTE支持有限，也可以用generate_series生成月份序列（注意参数格式）：

-- Redshift替代月份生成方式
WITH months AS (
    SELECT (DATE_TRUNC('month', min_ts) + (n || ' months')::INTERVAL) AS month
    FROM (
        SELECT MIN(timestamp) AS min_ts, MAX(timestamp) AS max_ts FROM events
    ) t,
    (SELECT ROW_NUMBER() OVER () - 1 AS n
     FROM events
     LIMIT (SELECT DATE_PART('year', max_ts) - DATE_PART('year', min_ts))*12 + DATE_PART('month', max_ts) - DATE_PART('month', min_ts) + 1) AS nums
)
SELECT month::DATE FROM months ORDER BY month;

这个查询可以直接复用，调整表名和字段名即可适配你的实际业务表。

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