LeetCode 1107. 每日新用户统计
题目描述
题意分析
统计指定日期区间内，每天有多少用户第一次登录。一个用户只在其全部历史中的首次登录日算作新用户，之后再次登录不能重复计入；其他类型的活动也不能当作登录。
本篇查询以
2019-06-30为截止日，使用向前推九十天到当日的闭区间，即2019-04-01至2019-06-30，两端都包含。输出日期login_date和当天新用户数user_count，没有新用户的日期不需要补零行。
解法：SQL 查询建模
核心思路
[!blue]
先把“首次登录”确定下来，再判断这个首次日期是否落在统计区间。内部查询只筛选
activity = 'login'，但保留这些登录行为的全部历史；按user_id分组，对日期取MIN，得到每个用户唯一的首登日期。行为条件可以提前应用，因为其他活动本来就不参与首次登录的定义；日期条件却不能提前截断原始记录。若删掉区间之前的登录历史，老用户在区间内的一次重登就可能变成剩余记录中的最早日期，被错误识别为新用户。
内部聚合完成后，一行已经代表一个登录用户及其真正首登日期。外层将日期条件作用于这个聚合结果，只保留首登日处于闭区间的用户，既排除首登更早的用户，也排除截止日之后才首登的记录。
最后按
login_date分组，使用COUNT(*)统计每一天留下的行数。因为内层已经压成每用户一行，同一用户多次或同日重复登录都不会重复计数，不需要在外层再做一次用户去重。整个查询经历了两次不同粒度的聚合：先按用户归并历史，再按首登日期汇总用户。两层不能交换，也不能用每天的登录去重人数代替新用户数。
解题步骤
- 从完整历史中筛出登录行为。
- 按用户分组，取最早登录日期，形成一用户一行的结果。
- 对首登日期应用截止日及前推九十天的闭区间条件。
- 按首登日分组，对保留的用户行计数并输出指定列。
代码实现
SELECT
login_date,
COUNT(*) AS user_count
FROM (
-- 先对完整登录历史取最早日期，每个用户只保留一行。
SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
-- 先筛登录行为，其他活动不参与首次登录日期统计。
WHERE activity = 'login'
GROUP BY user_id
) first_login
-- 日期条件作用于首登日，不能提前裁掉历史记录。
WHERE login_date BETWEEN DATE_SUB('2019-06-30', INTERVAL 90 DAY) AND '2019-06-30'
-- 按首登日期汇总，内层的一行对应一个用户。
GROUP BY login_date;
复杂度分析
- 时间复杂度：取决于数据库聚合计划。设原记录数为
n，采用哈希聚合时可按期望 $O(n)$ 理解；若执行器需要排序或额外物化，还需计入相应开销。- 空间复杂度：哈希聚合需记录每个登录用户的最早日期，约为 $O(u)$，
u是不同登录用户数；日期汇总及数据库中间结果另计。
关键点总结
[!green]
- 先按行为筛选，再按用户确定全历史首登日，最后按日期筛选和计数。
- 内层一行对应一个用户，外层
COUNT(*)因而就是新增用户数。- 日期条件应用于首次日期，不能先让历史范围变短。
易错点总结
[!yellow]
- 直接统计每天去重后的登录用户，得到的是当天登录人数，不是首次登录人数。
- 在原始表上先限制日期再求最小值，会把老用户的区间内重登误当首登。
- 非登录行为也参与最小日期计算，会把其他活动时间误当成首次登录时间。
- 不按用户先聚合，重复登录记录会在按天计数时重复贡献。
- 忽略查询上界，只判断与截止日的天差不大于九十，可能把截止日之后的日期也纳入；当前 SQL 用闭区间同时限制两端。
相似题目
| 题目 | 难度 | 关联与区别 |
|---|---|---|
| 550. 游戏玩法分析 IV | 中等 | 同样先确定用户的首次登录时间，原题统计次日留存，本题按首次登录日期统计新增用户。 |
| 1070. 产品销售分析 III | 中等 | 同样先在完整登录或销售历史中找每组最早日期，再做日期筛选或统计，不能先截断日期范围导致首次时间改变。 |
转载与许可
许可
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处！