SQL统计分组内活跃用户留存率_利用聚合与时间窗口-SQL-PHP中文网

分组内留存率必须按分组维度（如channel、user_id）重新计算每个用户的首次登录日，否则沿用全局首次日期会导致分母混入其他组用户而失真；正确做法是在子查询或窗口函数中PARTITION BY channel, user_id，确保首日与回访均绑定同一分组。

sql统计分组内活跃用户留存率_利用聚合与时间窗口

分组内留存率不能直接套用全局留存SQL，必须先按分组切片，再对每组独立跑首次登录→回访判断逻辑，否则分母混入其他组用户，结果完全失真。

为什么GROUP BY分组后直接算留存会出错

常见错误是写成 SELECT channel, COUNT(DISTINCT user_id) AS new_cnt, COUNT(DISTINCT CASE WHEN login_date = DATE_ADD(first_login, INTERVAL 1 DAY) THEN user_id END) AS day1_retained FROM ... GROUP BY channel ——但这里 first_login 如果没在子查询里按 channel + user_id 重新计算，就会沿用全局首次登录日，导致某渠道新用户被算进其他渠道的分母。

根本问题在于：留存率的“首日”必须是该分组内用户的首次行为日，不是全量用户的首次行为日。

分组维度（如 channel、device_type、region）必须参与 MIN(login_date) 的 PARTITION BY 或 GROUP BY
如果用窗口函数，PARTITION BY channel, user_id 才能保证每个用户在本渠道内的首次登录被正确识别
如果用子查询，GROUP BY channel, user_id 是底线，漏掉 channel 就等于没分组

用CTE分步实现分组内首日+回访匹配

推荐结构清晰、各数据库兼容性好的写法，避免窗口函数版本依赖：

以渠道（channel）为分组，计算各渠道次日留存：

WITH daily_login AS (
  SELECT DISTINCT user_id, channel, DATE(login_time) AS login_date
  FROM user_behavior 
  WHERE event_type = 'login'
),
first_by_channel AS (
  SELECT user_id, channel, MIN(login_date) AS first_date
  FROM daily_login
  GROUP BY channel, user_id
),
retention_match AS (
  SELECT 
    f.channel,
    f.user_id,
    f.first_date,
    d.login_date AS day1_date
  FROM first_by_channel f
  LEFT JOIN daily_login d 
    ON f.user_id = d.user_id 
    AND d.channel = f.channel 
    AND d.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
)
SELECT 
  channel,
  COUNT(*) AS new_user_cnt,
  COUNT(day1_date) AS retained_cnt,
  ROUND(COUNT(day1_date) * 100.0 / COUNT(*), 2) AS day1_retention_rate
FROM retention_match
GROUP BY channel;

关键点：

daily_login 先去重，避免同用户同渠道单日多次登录干扰 MIN()
first_by_channel 的 GROUP BY channel, user_id 是分组留存的锚点，不可省略
LEFT JOIN 条件中必须显式带上 d.channel = f.channel，否则跨渠道误匹配
MySQL 用 DATE_ADD(... INTERVAL 1 DAY)；PostgreSQL 改为 f.first_date + INTERVAL '1 day'

窗口函数写法（MySQL 8.0+/PostgreSQL）更紧凑但需防坑

适合已确认数据库版本、且想减少JOIN层级的场景：

WITH ranked AS (
  SELECT 
    user_id,
    channel,
    DATE(login_time) AS login_date,
    MIN(DATE(login_time)) OVER (PARTITION BY channel, user_id) AS first_date,
    DATEDIFF(DATE(login_time), MIN(DATE(login_time)) OVER (PARTITION BY channel, user_id)) AS day_diff
  FROM user_behavior 
  WHERE event_type = 'login'
)
SELECT 
  channel,
  COUNT(DISTINCT CASE WHEN day_diff = 0 THEN user_id END) AS new_user_cnt,
  COUNT(DISTINCT CASE WHEN day_diff = 1 THEN user_id END) AS retained_cnt,
  ROUND(
    COUNT(DISTINCT CASE WHEN day_diff = 1 THEN user_id END) * 100.0 / 
    NULLIF(COUNT(DISTINCT CASE WHEN day_diff = 0 THEN user_id END), 0), 
    2
  ) AS day1_retention_rate
FROM ranked
WHERE day_diff IN (0, 1)
GROUP BY channel;

注意：