日期、去重、连续区间与 Top-N
前置知识：窗口函数与窗口框架
本章目录
本章学习目标
- 明确日期与观察截止边界
- 推导连续活跃区间
- 用窗口完成去重与组内 Top-N
时间口径与日期差
DATE 是日期，DATETIME 是没有内置时区标签的日历时间。课程所有时间按同一业务时区解释；多地区真实数据需先统一时区再按日统计。DATE(event_time) 只是取日历日期，并不会自动解决业务时区。月份使用左闭右开范围，避免三十一日午夜上界遗漏白天事件。
DATEDIFF(后日期,前日期) 算自然日差，不是精确经过的二十四小时数。晚上十一点注册、次日一点活跃会得到一天；留存可以采用这种口径，时长分析则需另选 TIMESTAMPDIFF。指标名需说明自然日或精确小时，不能为了计算方便在两种定义之间随意切换。
事件去重与版本去重
events 每行一次事件，同用户同日十次事件只有一个用户日期。日活先按 user_id 与日期去重，或按日期 COUNT(DISTINCT user_id)；COUNT(*) 是事件量。这个压缩不是把合法重复行为认定为坏数据，而是把事件粒度转换成日活粒度。
真正重复导入的数据可能需按业务键保留最新版本，用 ROW_NUMBER 按更新时间与唯一编号倒序选一行。但只有事件时间时，可以确定首个事件，不能宣称它是更正确的版本。去重规则应记录键、优先级、原因与删除数量，本章只在查询结果去重，不删除来源记录。
连续区间的纸笔推导
某用户一月一、二、四、五日活跃，按日期编号一、二、三、四。每个日期减去对应编号天数，前两天形成相同锚点，后两天形成另一个。因为连续日期与序号同步增加，差不变；缺口改变差。按用户和锚点分组，最小日、最大日与行数便是起止和连续长度。
必须先一用户一天去重，同一天多个事件会让行号前进但日期不前进，破坏规律。交易日连续不能用自然日直接判断，周末与假期需用交易日历序号替代。自然日区间可检查结束减开始加一等于行数，发现不等时回查是否遗漏去重或顺序定义。
Top-N 的含义与时点
每用户最高两笔支付订单用 ROW_NUMBER 按净额降序、编号升序，外层取序号不大于二。前两档金额保留并列则用 DENSE_RANK，窗口里只按净额排序，最终显示再按编号。两个问题结果行数可能不同，不能把一套答案机械应用到另一个。
综合顺序是统一时间范围、去重到分析粒度、再排名或分组。检查截止日后的事件是否进入、一天是否重复计数以及同金额处理。事后全历史最高值不能当成预测时点已知的特征；做量化或数据科学建模，要按时间切分并记录每列最晚可用时间。技术技巧始终服务于业务口径。
从结果到验证
阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。
先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。
完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。
完整示例与实际输出
WITH days AS (SELECT DISTINCT user_id,DATE(event_time) day FROM events WHERE event_time<'2026-02-01'),
numbered AS (SELECT user_id,day,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY day) rn FROM days),
islands AS (SELECT user_id,day,DATE_SUB(day,INTERVAL rn DAY) anchor FROM numbered)
SELECT user_id,MIN(day) start_day,MAX(day) end_day,COUNT(*) active_days
FROM islands GROUP BY user_id,anchor ORDER BY user_id,start_day;
WITH t AS (SELECT order_id,user_id,amount-refund_amount net_amount,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount-refund_amount DESC,order_id) rn
FROM orders WHERE user_id IS NOT NULL AND status IN ('paid','refunded'))
SELECT order_id,user_id,net_amount,rn FROM t WHERE rn<=2 ORDER BY user_id,rn;
实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.
user_id start_day end_day active_days 1 2026-01-01 2026-01-02 2 2 2026-01-01 2026-01-02 2 2 2026-01-04 2026-01-04 1 2 2026-01-08 2026-01-08 1 3 2026-01-02 2026-01-02 1 3 2026-01-05 2026-01-05 1 3 2026-01-09 2026-01-09 1 4 2026-01-02 2026-01-03 2 4 2026-01-05 2026-01-05 1 4 2026-01-09 2026-01-09 1 5 2026-01-03 2026-01-04 2 6 2026-01-03 2026-01-04 2 6 2026-01-06 2026-01-06 1 6 2026-01-10 2026-01-10 1 7 2026-01-04 2026-01-04 1 7 2026-01-07 2026-01-07 1 7 2026-01-11 2026-01-11 1 8 2026-01-04 2026-01-05 2 8 2026-01-07 2026-01-07 1 8 2026-01-11 2026-01-11 1 9 2026-01-05 2026-01-06 2 10 2026-01-05 2026-01-06 2 10 2026-01-08 2026-01-08 1 10 2026-01-12 2026-01-12 1 11 2026-01-06 2026-01-06 1 11 2026-01-09 2026-01-09 1 11 2026-01-13 2026-01-13 1 12 2026-01-06 2026-01-07 2 12 2026-01-09 2026-01-09 1 12 2026-01-13 2026-01-13 1 13 2026-01-07 2026-01-08 2 14 2026-01-07 2026-01-08 2 14 2026-01-10 2026-01-10 1 14 2026-01-14 2026-01-14 1 15 2026-01-08 2026-01-08 1 15 2026-01-11 2026-01-11 1 15 2026-01-15 2026-01-15 1 16 2026-01-08 2026-01-09 2 16 2026-01-11 2026-01-11 1 16 2026-01-15 2026-01-15 1 17 2026-01-09 2026-01-10 2 18 2026-01-09 2026-01-10 2 18 2026-01-12 2026-01-12 1 18 2026-01-16 2026-01-16 1 19 2026-01-10 2026-01-10 1 19 2026-01-13 2026-01-13 1 19 2026-01-17 2026-01-17 1 20 2026-01-10 2026-01-11 2 20 2026-01-13 2026-01-13 1 20 2026-01-17 2026-01-17 1 21 2026-01-11 2026-01-12 2 22 2026-01-11 2026-01-12 2 22 2026-01-14 2026-01-14 1 22 2026-01-18 2026-01-18 1 23 2026-01-12 2026-01-12 1 23 2026-01-15 2026-01-15 1 23 2026-01-19 2026-01-19 1 24 2026-01-12 2026-01-13 2 24 2026-01-15 2026-01-15 1 24 2026-01-19 2026-01-19 1 25 2026-01-13 2026-01-14 2 26 2026-01-13 2026-01-14 2 26 2026-01-16 2026-01-16 1 26 2026-01-20 2026-01-20 1 27 2026-01-14 2026-01-14 1 27 2026-01-17 2026-01-17 1 27 2026-01-21 2026-01-21 1 28 2026-01-14 2026-01-15 2 28 2026-01-17 2026-01-17 1 28 2026-01-21 2026-01-21 1 29 2026-01-15 2026-01-16 2 30 2026-01-15 2026-01-16 2 30 2026-01-18 2026-01-18 1 30 2026-01-22 2026-01-22 1 order_id user_id net_amount rn 1 1 48.00 1 25 1 48.00 2 2 2 240.00 1 26 2 240.00 2 3 3 12.50 1 51 3 12.50 2 4 4 48.00 1 28 4 48.00 2 5 5 240.00 1 29 5 240.00 2 6 6 12.50 1 30 6 12.50 2 7 7 48.00 1 31 7 48.00 2 8 8 240.00 1 32 8 240.00 2 57 9 12.50 1 33 9 6.25 2 10 10 48.00 1 34 10 48.00 2 35 11 240.00 1 59 11 240.00 2 12 12 12.50 1 13 13 48.00 1 37 13 48.00 2 14 14 240.00 1 38 14 240.00 2 15 15 12.50 1 39 15 12.50 2 16 16 48.00 1 40 16 48.00 2 17 17 240.00 1 41 17 240.00 2 42 18 12.50 1 19 19 48.00 1 43 19 48.00 2 20 20 240.00 1 44 20 120.00 2 21 21 12.50 1 46 22 48.00 1 22 22 24.00 2 23 23 240.00 1 47 23 240.00 2 24 24 12.50 1 48 24 12.50 2
五道自测：先回答，再核对
01 · DATEDIFF 算小时吗？
查看提示
日期部分。
查看参考答案
不是，算自然日日期差。
02 · 同人同日十事件是十日活吗？
查看提示
用户日期粒度。
查看参考答案
不是，一个用户日期，需要去重或 COUNT DISTINCT。
03 · 日期减行号为何形成岛？
查看提示
两者同增一。
查看参考答案
连续时差不变，缺口改变锚点。
04 · 交易日连续能按自然日算吗？
查看提示
周末。
查看参考答案
不能，要用交易日历编号。
05 · 前两档金额最多两条吗？
查看提示
保留并列。
查看参考答案
可能超过两条；ROW_NUMBER 前二才限制两条。
两项动手任务
任务 1 · 计算一月日活，同日重复只计一次。
提示
左闭右开。
完整参考解答与检查方式
SELECT DATE(event_time) day,COUNT(DISTINCT user_id) dau FROM events WHERE event_time>='2026-01-01' AND event_time<'2026-02-01' GROUP BY DATE(event_time) ORDER BY day; 无事件日期需另补日历才能显示零。任务 2 · 取每用户最高两档支付净额并保留并列。
提示
DENSE_RANK 不追加唯一键。
完整参考解答与检查方式
WITH r AS (SELECT user_id,order_id,amount-refund_amount net_amount,DENSE_RANK() OVER(PARTITION BY user_id ORDER BY amount-refund_amount DESC) dr FROM orders WHERE status IN ('paid','refunded') AND user_id IS NOT NULL) SELECT user_id,order_id,net_amount,dr FROM r WHERE dr<=2 ORDER BY user_id,dr,order_id; 最终排序不打散排名并列。官方资料与掌握状态
完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。
动手任务记录
掌握状态与复习
关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。
记录尽量保存在当前浏览器，建议定期导出备份。