咨询按自定义公式实现留存率的SQL逻辑是否正确

核心结论 #

你的SQL逻辑完全匹配你设定的「留存率 = 留存用户数 / 流失用户数」公式：

• 分子部分：筛选出指定Android版本中，从未触发app_remove事件的去重用户数，完全对应你定义的「留存用户数」
• 分母部分：筛选出同版本Android用户中，触发过app_remove事件的去重用户数，完全对应你定义的「流失用户数」
• 最终计算逻辑为分子除以分母并保留两位小数，和公式完全一致

关于结果小于1的原因 #

结果小于1说明在你筛选的用户群体里，流失用户数多于留存用户数，这是当前数据的实际情况，并非SQL逻辑错误。

优化建议（可选） #

你的SQL逻辑正确，但两次子查询会重复扫描表，可改用更高效的写法提升性能：

写法1：条件聚合

SELECT 
  ROUND(
    COUNT(DISTINCT CASE WHEN user_pseudo_id NOT IN (
      SELECT DISTINCT user_pseudo_id FROM table WHERE event_name = 'app_remove'
    ) THEN user_pseudo_id END) 
    / 
    COUNT(DISTINCT CASE WHEN user_pseudo_id IN (
      SELECT DISTINCT user_pseudo_id FROM table WHERE event_name = 'app_remove'
    ) THEN user_pseudo_id END),
    2
  ) AS retention_rate
FROM table
WHERE 
  app_info.version IN ('2.0.0', '2.1.0', '2.1.1', '2.1.2', '2.2.0')
  AND device.operating_system = 'Android';

写法2：CTE预计算流失用户

WITH churn_users AS (
  SELECT DISTINCT user_pseudo_id 
  FROM table 
  WHERE event_name = 'app_remove'
)
SELECT 
  ROUND(
    COUNT(DISTINCT t.user_pseudo_id) FILTER (WHERE c.user_pseudo_id IS NULL)
    /
    COUNT(DISTINCT t.user_pseudo_id) FILTER (WHERE c.user_pseudo_id IS NOT NULL),
    2
  ) AS retention_rate
FROM table t
LEFT JOIN churn_users c ON t.user_pseudo_id = c.user_pseudo_id
WHERE 
  t.app_info.version IN ('2.0.0', '2.1.0', '2.1.1', '2.1.2', '2.2.0')
  AND t.device.operating_system = 'Android';

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