如何批量计算每日应用用户的NURR、CURR与RURR？

NURR定义回顾 #

对于目标日期t，NURR是在t-13至t-7期间首次打开应用的用户中，在t-6至t期间打开过应用的用户的占比。

实现思路 #

要按日计算，核心是把原单日SQL中的固定基准日期替换为动态的目标日期t，为每个t单独计算留存的分子（留存用户数）和分母（基准用户数）。可以通过生成日期维度表，关联用户会话数据来批量完成计算。

完整SQL代码（以BigQuery为例） #

WITH date_range AS (
    -- 生成所有需要计算NURR的日期，这里取会话表中存在会话的所有日期
    SELECT DISTINCT session_date AS target_date
    FROM sessions
    WHERE session_date >= (SELECT MIN(first_session) FROM sessions) + 13 -- 确保有足够历史数据支撑计算
),
new_users_last_week AS (
    -- 针对每个目标日期，筛选出t-13到t-7期间首次打开应用的用户（基准用户群）
    SELECT
        dr.target_date,
        s.user_id
    FROM date_range dr
    JOIN sessions s
        ON s.first_session BETWEEN DATE_SUB(dr.target_date, INTERVAL 13 DAY) 
                               AND DATE_SUB(dr.target_date, INTERVAL 7 DAY)
    GROUP BY dr.target_date, s.user_id -- 去重每个日期对应的新用户
),
retained_users AS (
    -- 从基准用户群中，筛选出t-6到t期间有会话的用户（留存用户）
    SELECT
        nu.target_date,
        nu.user_id
    FROM new_users_last_week nu
    JOIN sessions s
        ON nu.user_id = s.user_id
        AND s.session_date BETWEEN DATE_SUB(nu.target_date, INTERVAL 6 DAY) 
                               AND nu.target_date
    GROUP BY nu.target_date, nu.user_id
)
-- 计算每个日期的NURR值
SELECT
    dr.target_date AS date,
    SAFE_DIVIDE(COUNT(DISTINCT ru.user_id), COUNT(DISTINCT nu.user_id)) AS NURR
FROM date_range dr
LEFT JOIN new_users_last_week nu ON dr.target_date = nu.target_date
LEFT JOIN retained_users ru ON dr.target_date = ru.target_date AND nu.user_id = ru.user_id
GROUP BY dr.target_date
ORDER BY dr.target_date DESC;

代码说明 #

1. date_range：生成所有需要计算NURR的日期，确保只计算有数据支撑的日期（也可替换为固定日期范围，比如GENERATE_DATE_ARRAY('2025-02-10', '2025-02-18')）。
2. new_users_last_week：对每个目标日期，锁定该日期前13至前7天内的首次会话用户，作为留存计算的基准群体。
3. retained_users：从基准群体中筛选出在目标日期前6天至目标日期内有活跃会话的用户，即留存用户。
4. 最后通过SAFE_DIVIDE计算占比，避免分母为0导致的错误，最终按日期倒序输出结果。

替代方案（窗口函数实现） #

如果偏好使用窗口函数，可通过标记用户首次会话对应的目标日期范围，再判断留存情况：

WITH user_first_window AS (
    SELECT
        user_id,
        first_session,
        -- 标记该用户首次会话对应的NURR目标日期范围
        DATE_ADD(first_session, INTERVAL 7 DAY) AS min_target_date,
        DATE_ADD(first_session, INTERVAL 13 DAY) AS max_target_date
    FROM (SELECT DISTINCT user_id, first_session FROM sessions)
),
user_retention_dates AS (
    SELECT
        u.user_id,
        u.first_session,
        u.min_target_date,
        u.max_target_date,
        s.session_date AS retention_date
    FROM user_first_window u
    JOIN sessions s
        ON u.user_id = s.user_id
        AND s.session_date BETWEEN DATE_ADD(u.first_session, INTERVAL 1 DAY) 
                               AND DATE_ADD(u.first_session, INTERVAL 19 DAY)
),
target_dates AS (
    SELECT DISTINCT session_date AS target_date FROM sessions
)
SELECT
    td.target_date AS date,
    SAFE_DIVIDE(
        COUNT(DISTINCT CASE WHEN urd.retention_date BETWEEN DATE_SUB(td.target_date, INTERVAL 6 DAY) AND td.target_date THEN urd.user_id END),
        COUNT(DISTINCT CASE WHEN urd.first_session BETWEEN DATE_SUB(td.target_date, INTERVAL 13 DAY) AND DATE_SUB(td.target_date, INTERVAL 7 DAY) THEN urd.user_id END)
    ) AS NURR
FROM target_dates td
LEFT JOIN user_retention_dates urd
    ON td.target_date BETWEEN urd.min_target_date AND urd.max_target_date
GROUP BY td.target_date
ORDER BY td.target_date DESC;

适配注意事项 #

• 不同SQL引擎的日期函数语法略有差异，比如MySQL用DATE_SUB/DATE_ADD，PostgreSQL直接用INTERVAL语法，需根据实际环境调整。
• 如果需要排除无基准用户的日期，可在最终查询中添加HAVING COUNT(DISTINCT nu.user_id) > 0。

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