核心是用窗口函数实现动态时间窗口聚合：以COUNT(*) OVER(PARTITION BY user_id ORDER BY login_time RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)统计用户最近7天活跃次数，避免自连接；配合LAG()识别回归用户（需处理NULL）；NTILE(4)按活跃度分层需先过滤无效行为；MAX() OVER替代相关子查询提升性能，但依赖联合索引优化。
用 ROW_NUMBER() 和 COUNT() OVER 算最近7天活跃用户数
核心是避免写自连接或子查询，直接用窗口函数按用户+时间窗口聚合。比如统计每个用户在最近7天内登录次数，不能只用 GROUP BY user_id，那样会丢失“最近”这个动态范围。
常见错误是把 WHERE login_time >= CURRENT_DATE - INTERVAL '7 days' 写在外部，结果每个用户只算自己全部历史里的7天——但你想看的是“截至每一天，该用户过去7天是否活跃”，得用滑动窗口。
- 正确做法：先按
user_id和login_time排序，用COUNT(*) OVER (PARTITION BY user_id ORDER BY login_time RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) -
RANGE比ROWS更适合时间窗口，但 PostgreSQL 支持，MySQL 8.0+ 仅支持ROWS（需配合日期序列模拟） - 注意时区：如果日志是 UTC，而业务要求按本地日期算活跃，必须先用
AT TIME ZONE转换，否则窗口边界错位
用 LAG() 判断用户是否“回归”（流失后重新活跃）
“回归用户”不是简单查有没有记录，而是要确认上次活跃距今 >30 天，这次又出现了。靠 LAG(login_time) 可以拿到上一次登录时间，再和当前比。
典型坑是没处理 NULL：第一个登录记录的 LAG() 是空，直接减会得 NULL，导致整行被过滤掉；还有人用 LEAD() 反着算，逻辑容易绕晕。
- 写法示例：
login_time - LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time) > INTERVAL '30 days' - 必须加
AND LAG(login_time) IS NOT NULL过滤首条记录 - 如果数据有重复登录（同秒多次），建议先用
DISTINCT ON (user_id, DATE(login_time))或去重临时表，否则LAG()可能拉到同一秒的上一条，差值为 0
用 NTILE(4) 快速分层用户活跃度（非等频分箱）
运营常要分“高/中/低/沉默”四档，但直接用 CASE WHEN COUNT(*) > 100 是静态阈值，不同产品量级差异大。NTILE() 按活跃次数排序后均分，更适应数据分布。
问题在于它强制切块，哪怕所有用户都只登录1次，也会硬分出4组，最低组里全是1次用户——这不叫“分层”，叫“凑数”。所以得先筛掉无效行为（如仅打开APP未触发事件）。
- 先聚合每人总活跃次数：
SELECT user_id, COUNT(*) AS act_cnt FROM events WHERE event_type IN ('login', 'click', 'submit') GROUP BY user_id - 再套
NTILE(4) OVER (ORDER BY act_cnt DESC)，别忘了NULLS LAST防止空值占一档 - MySQL 不支持
NTILE的RANGE参数，且 5.7 不支持窗口函数，必须升到 8.0+
为什么 MAX(login_time) OVER (PARTITION BY user_id) 比子查询快得多
想标记每个用户的最后登录时间，很多人写 (SELECT MAX(login_time) FROM logs l2 WHERE l2.user_id = l1.user_id)，这是相关子查询，数据量一过百万就明显变慢。窗口函数天然并行，执行计划里没有 Nested Loop。
但要注意：如果表没索引，PARTITION BY user_id 依然可能扫全表；而且 MAX() 窗口结果是每行都带最大值，如果只想保留每个用户一行，还得配合 ROW_NUMBER() 去重。
- 加联合索引：
CREATE INDEX idx_user_time ON logs(user_id, login_time)，让窗口计算走索引扫描 - 如果只要“最后登录那天的完整记录”，用
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC)+WHERE rn = 1，别用MAX()后再 JOIN 回原表 - PostgreSQL 中
MAX() OVER对 NULL 值默认跳过，但若字段允许 NULL 且你希望把它当最小值参与排序，得提前COALESCE(login_time, '1970-01-01')
窗口函数不是银弹，RANGE 在 MySQL 上不可用，NTILE 在数据倾斜时分层失真，而所有这些都依赖正确的索引和非空约束——没建好索引时，窗口函数照样慢得像子查询。
