PostgreSQL 9.5 用SQL计算搜索功能月度流失数与流失率

基础信息 #

当前环境为PostgreSQL 9.5，需要统计搜索功能的月度用户流失相关指标，指标规则如下：

• churn_count：上月使用过搜索功能、当月未使用的用户数量
• churn_rate：churn_count 除以上月使用搜索功能的总用户数，结果以百分比形式展示，保留1位小数

测试表结构与模拟数据 #

CREATE TABLE yammer_events (
occurred_at TIMESTAMP,
user_id INT,
event_name VARCHAR(50)
);

INSERT INTO yammer_events (occurred_at, user_id, event_name) VALUES 
('2014-06-01 00:00:01', 1, 'search_autocomplete'),
('2014-06-01 00:00:01', 2,  'search_autocomplete'),
('2014-07-01 00:00:01', 1, 'search_run'),
('2014-07-01 00:00:02', 1, 'search_run'),
('2014-07-01 00:00:01', 2,  'search_run'),
('2014-07-01 00:00:01', 3,  'search_run'),
('2014-08-01 00:00:01', 1,  'search_run'),
('2014-08-01 00:00:01', 4,  'search_run');

预期输出结果 #

计算逻辑校验：

• 6月搜索活跃用户：用户1、用户2，共2人
• 7月搜索活跃用户：用户1、用户2、用户3，共3人
• 8月搜索活跃用户：用户1、用户4，共2人
• 7月无流失用户（上月2个用户当月均活跃）；8月流失用户为2、3，churn_count为2，churn_rate为2/3*100=66.6%

原有SQL问题说明 #

你写的SQL存在两个核心问题：

1. 把this_month.user_id IS NULL的判断写在了LEFT JOIN的ON子句中，关联阶段就会错误过滤匹配记录，导致计数逻辑失效
2. 没有统计上月活跃总用户数，无法计算流失率指标，也没有对结果做格式化处理

正确SQL实现 #

WITH monthly_active AS (
  -- 第一步：提取每个月使用过搜索功能的去重用户，月份截断到月维度
  SELECT DISTINCT
    DATE_TRUNC('month', occurred_at)::DATE AS month,
    user_id
  FROM yammer_events
  WHERE event_name LIKE 'search%'
),
monthly_user_total AS (
  -- 第二步：统计每个月的搜索活跃总用户数，用于计算流失率
  SELECT
    month,
    COUNT(user_id) AS last_month_active_total
  FROM monthly_active
  GROUP BY month
)
SELECT
  (last.month + INTERVAL '1 month')::DATE AS month,
  -- 统计上月活跃、当月无活跃记录的用户数，即流失数
  COUNT(CASE WHEN curr.user_id IS NULL THEN 1 END) AS churn_count,
  -- 计算流失率，转数值类型避免整数除法，保留1位小数
  ROUND(
    COUNT(CASE WHEN curr.user_id IS NULL THEN 1 END)::NUMERIC / MAX(mut.last_month_active_total) * 100,
    1
  ) AS churn_rate_percentage
FROM monthly_active last
-- 关联当月用户活跃记录，判断用户是否留存
LEFT JOIN monthly_active curr
  ON last.user_id = curr.user_id
  AND curr.month = last.month + INTERVAL '1 month'
-- 关联上月总活跃用户数
LEFT JOIN monthly_user_total mut
  ON last.month = mut.month
-- 过滤掉没有后续月份数据的最新一个月，避免统计不完整周期
WHERE last.month + INTERVAL '1 month' <= (SELECT MAX(month) FROM monthly_active)
GROUP BY last.month
ORDER BY last.month;

执行上述SQL即可得到和预期完全一致的结果。

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