PostgreSQL高效实现简单用户留存/流失统计查询

实现逻辑说明 #

• 仅对原表单次扫描计算窗口值，避免多表关联、递归查询带来的性能损耗
• 真实行为记录直接按排序规则标记：用户首条行为标记为first_purchase，其余真实行为标记为retained
• 相邻行为间隔跨自然月时，按需生成间隔内每月1日0点的流失记录，标记为churn，无冗余计算

可直接运行的查询语句 #

WITH user_behavior_series AS (
    SELECT
        users,
        dates,
        CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY users ORDER BY dates) = 1 THEN 'first_purchase'
            ELSE 'retained'
        END AS tag,
        LEAD(dates) OVER (PARTITION BY users ORDER BY dates) AS next_date
    FROM users_churn
),
real_events AS (
    SELECT users, dates, tag FROM user_behavior_series
),
churn_events AS (
    SELECT
        users,
        generate_series(
            DATE_TRUNC('month', dates) + INTERVAL '1 month',
            DATE_TRUNC('month', next_date) - INTERVAL '1 month',
            INTERVAL '1 month'
        )::timestamp AS dates,
        'churn' AS tag
    FROM user_behavior_series
    WHERE next_date IS NOT NULL
      AND DATE_TRUNC('month', next_date) > DATE_TRUNC('month', dates) + INTERVAL '1 month'
)
SELECT users, dates, tag
FROM real_events
UNION ALL
SELECT users, dates, tag
FROM churn_events
ORDER BY users, dates;

结果说明 #

执行语句后输出与预期规则完全匹配：

• 用户首条行为自动标记为首次购买
• 相邻两次行为间隔超过1个自然月时，自动补齐间隔月份的流失记录
• 非首次的真实用户行为统一标记为留存
• 最终结果按用户、时间正序排列，无多余计算字段

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