PostgreSQL集成Redash订阅月度活跃数统计SQL问题修复方法

修正后可直接使用的SQL如下：

WITH past_12_months AS (
    -- 生成过去12个月的连续月份序列，格式统一为YYYY-MM
    SELECT to_char(month_series, 'YYYY-MM') AS active_month
    FROM generate_series(
        date_trunc('month', '{{ today }}'::timestamp - interval '12 months'),
        date_trunc('month', '{{ today }}'::timestamp),
        interval '1 month'
    ) AS month_series
),
valid_subscriptions AS (
    SELECT 
        id AS subscription_id,
        unit_id AS unit,
        date_trunc('month', created_at) AS create_month,
        -- 未取消的订阅用当前时间作为取消时间兜底
        date_trunc('month', COALESCE(canceled_at, '{{ today }}'::timestamp)) AS cancel_month
    FROM [DATABASE HERE]
    -- 提前过滤12个月前就已取消的无效订阅，减少计算量
    WHERE COALESCE(canceled_at, '{{ today }}'::timestamp) >= ('{{ today }}'::timestamp - interval '12 months')
)
SELECT 
    vs.unit,
    pm.active_month,
    COUNT(vs.subscription_id) AS subscriptions
FROM past_12_months pm
LEFT JOIN valid_subscriptions vs 
    ON to_char(vs.create_month, 'YYYY-MM') <= pm.active_month 
    AND to_char(vs.cancel_month, 'YYYY-MM') >= pm.active_month
GROUP BY vs.unit, pm.active_month
-- 不需要展示无对应unit的空行可保留该行，否则直接删除
HAVING vs.unit IS NOT NULL
ORDER BY pm.active_month, vs.unit

问题修复说明 #

• 针对「未覆盖订阅所有活跃月份」问题：通过PostgreSQL自带的generate_series函数生成过去12个月的完整月份列表，再关联订阅表判断月份是否处于订阅有效期内，每个符合规则的活跃月份都会生成对应记录，不再仅统计创建月份
• 针对「同月份重复记录」问题：原SQL分组字段使用了精确到时分秒的原始created_at字段，修正后分组直接使用格式化后的active_month和unit字段，同一unit同一月份只会产出一条统计结果

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