如何用SQL实现用户留存率条件计算及多日期批量查询

解决方案 #

你原来的多子查询写法每次都要全表扫描，50万数据量下执行效率偏低，下面的方案改用CTE+条件聚合的结构，既可以在单条SQL里完成留存率计算，也支持批量统计所有安装日期的留存数据：

注意事项 #

你表中event_date是DDMMYYYY格式，SQL日期计算时需要先转成标准日期类型避免运算错误，以下SQL里的日期函数可以根据你用的数据库类型做适配：

• PostgreSQL用TO_DATE(event_date, 'DDMMYYYY')，日期间隔用+ INTERVAL '1 day'
• MySQL用STR_TO_DATE(event_date, '%d%m%Y')，日期间隔用DATE_ADD(install_date, INTERVAL 1 DAY)
• SQL Server用CONVERT(DATE, event_date, 103)，日期间隔用DATEADD(DAY, 1, install_date)

实现SQL #

WITH new_users AS (
    -- 捞出所有版本113的新增用户，记录对应的安装日期
    SELECT 
        DISTINCT user_id,
        TO_DATE(event_date, 'DDMMYYYY') AS install_date
    FROM our_data
    WHERE event_name = 'first_open'
      AND version = '113'
      -- 如需限定统计的安装日期范围，在这里加条件即可
      -- AND TO_DATE(event_date, 'DDMMYYYY') BETWEEN '2021-09-01' AND '2021-09-30'
)
SELECT
    nu.install_date,
    COUNT(DISTINCT nu.user_id) AS day_zero,
    COUNT(DISTINCT CASE 
        WHEN TO_DATE(od.event_date, 'DDMMYYYY') = nu.install_date + INTERVAL '1 day' 
         AND od.event_name = 'session_start' 
         AND od.version = '113' 
        THEN nu.user_id END) AS day_one,
    COUNT(DISTINCT CASE 
        WHEN TO_DATE(od.event_date, 'DDMMYYYY') = nu.install_date + INTERVAL '3 day' 
         AND od.event_name = 'session_start' 
         AND od.version = '113' 
        THEN nu.user_id END) AS day_three,
    -- 直接计算留存率，乘1.0避免整数除法问题，可按需调整保留小数位数
    ROUND(COUNT(DISTINCT CASE 
        WHEN TO_DATE(od.event_date, 'DDMMYYYY') = nu.install_date + INTERVAL '1 day' 
         AND od.event_name = 'session_start' 
         AND od.version = '113' 
        THEN nu.user_id END) * 1.0 / COUNT(DISTINCT nu.user_id), 4) AS day_one_retention,
    ROUND(COUNT(DISTINCT CASE 
        WHEN TO_DATE(od.event_date, 'DDMMYYYY') = nu.install_date + INTERVAL '3 day' 
         AND od.event_name = 'session_start' 
         AND od.version = '113' 
        THEN nu.user_id END) * 1.0 / COUNT(DISTINCT nu.user_id), 4) AS day_three_retention
FROM new_users nu
LEFT JOIN our_data od 
    ON nu.user_id = od.user_id
GROUP BY nu.install_date
-- 如仅需查询2021-09-18的安装用户数据，开启下面的条件即可
-- HAVING nu.install_date = '2021-09-18'
ORDER BY nu.install_date;

扩展说明 #

如果需要统计更多天数的留存，只需要新增对应规则的CASE WHEN条件列即可，无需调整整体查询结构。批量统计全量安装日期留存时，仅需去掉安装日期的限定条件，就可以一次性得到所有批次的留存数据。

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