不能直接混用;因执行顺序冲突:GROUP BY先聚合(行粒度丢失),窗口函数后计算(需原始行粒度),必须通过子查询、CTE或SUM(聚合) OVER()等分步实现。

GROUP BY 和窗口函数能直接混用吗
不能。在标准 SQL(如 PostgreSQL、MySQL 8.0+、SQL Server)中,GROUP BY 和窗口函数可以共存,但必须分清执行顺序:窗口函数在 GROUP BY 之后计算(即先聚合,再开窗),除非你把窗口函数放在子查询或 CTE 中对原始明细数据操作。
月活留存率的核心是「以某月为起点,看后续月份是否仍有活跃」,它天然依赖用户级行为明细(比如 user_id, event_date),而不是聚合后的结果。所以正确做法通常是:先用窗口函数标记每个用户的首次活跃月(或当月是否留存),再用 GROUP BY 汇总比率。
- 错误写法:
SELECT month, COUNT(DISTINCT user_id), COUNT(DISTINCT user_id) OVER (PARTITION BY user_id ORDER BY month)...——GROUP BY month后user_id已丢失粒度,窗口函数无意义 - 正确路径:先用 CTE 或子查询算出每个用户每月的「是否为新客」「是否在 N 月后仍活跃」,再按月份
GROUP BY统计
如何用窗口函数定义「次月留存」逻辑
次月留存 = 在 M 月首次活跃的用户中,有多少人在 M+1 月也产生了行为。关键在于识别「首次活跃月」和「跨月连续性」,这需要两个窗口函数配合:
-
MIN(event_date) OVER (PARTITION BY user_id)得到每个用户的首活日期(用于圈定「新客群」) -
LEAD(event_date) OVER (PARTITION BY user_id ORDER BY event_date)判断下一次活跃是否落在「首活月+1个月内」 - 注意:用
DATE_TRUNC('month', event_date)(PostgreSQL)或YEAR(event_date)*100 + MONTH(event_date)(MySQL)统一成「年月」维度,避免日级干扰
示例片段(PostgreSQL):
WITH user_first AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_date)) AS first_month
FROM events
GROUP BY user_id
),
monthly_active AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_date) AS active_month
FROM events
),
cohort AS (
SELECT m.user_id, m.active_month, f.first_month,
(m.active_month = f.first_month) AS is_new,
(m.active_month = f.first_month + INTERVAL '1 month') AS is_retained_next
FROM monthly_active m
JOIN user_first f ON m.user_id = f.user_id
)
SELECT
first_month,
COUNT(DISTINCT user_id) FILTER (WHERE is_new) AS new_users,
COUNT(DISTINCT user_id) FILTER (WHERE is_retained_next) * 1.0 /
NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE is_new), 0) AS retention_rate
FROM cohort
GROUP BY first_month;
为什么不能只靠 GROUP BY + 聚合函数算留存
因为留存是「条件转移概率」,不是静态计数。比如:仅用 GROUP BY DATE_TRUNC('month', event_date) 只能得到每月 DAU/MAU,无法回答「6月新客里有多少人7月还来了」。
-
COUNT(DISTINCT user_id)是快照值,不携带时间关系 - 想关联不同月份的同一用户,必须保留
user_id维度贯穿计算全程,而GROUP BY会折叠该维度 - 窗口函数(如
LAG/LEAD)或自连接(ON a.user_id = b.user_id AND b.month = a.month + 1)才是建模用户行为流的必要工具
MySQL 5.7 没有窗口函数怎么办
只能用自连接或相关子查询模拟,性能差但可行。重点是别硬套语法,而是还原业务逻辑:
- 先建新客表:
SELECT user_id, MIN(DATE_FORMAT(event_date, '%Y-%m')) AS first_month FROM events GROUP BY user_id - 再左连次月行为:
LEFT JOIN events e2 ON e1.user_id = e2.user_id AND DATE_FORMAT(e2.event_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(e1.first_month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m') - 最后
GROUP BY e1.first_month统计非空e2.user_id占比 - 注意:字符串拼接月份易出错,务必验证
DATE_FORMAT和STR_TO_DATE的格式一致性,否则连接为空
真正麻烦的不是写法,而是当用户量超百万时,自连接可能触发笛卡尔积或全表扫描——这时候就得提前物化中间结果(比如建临时表存 monthly_active),而不是在单条 SQL 里硬扛。











