连续日期聚合的典型问题是将散乱日期合并为连续区间，核心是识别相邻日期差≠1天的断点；常用row_number()+日期差构造分组锚点，mysql 5.7及sqlite等旧版本需用变量或外部脚本替代。
什么是“连续日期聚合”的典型问题
当你有一组散乱的日期（比如用户登录日、订单创建日），想合并成连续的区间（如 2024-01-01 到 2024-01-05、2024-01-07 到 2024-01-08），本质是识别日期序列中的“断点”——相邻日期差不等于 1 天的地方。这在报表、用户活跃周期分析、设备在线时段统计里很常见。
用 ROW_NUMBER() + 日期差构造分组标识
核心思路：对排序后的日期打序号，再用 date - INTERVAL ROW_NUMBER() DAY（或等价写法）生成一个“锚点值”，同一连续段的锚点值相同，从而可按此分组。
PostgreSQL / MySQL 8.0+ / SQL Server / BigQuery 都支持：
SELECT MIN(date_col) AS start_date,
MAX(date_col) AS end_date
FROM (
SELECT date_col,
date_col - INTERVAL ROW_NUMBER() OVER (ORDER BY date_col) DAY AS grp
FROM your_table
) t
GROUP BY grp
ORDER BY start_date;
-
ROW_NUMBER()按日期升序编号，从 1 开始 - 对每个日期减去它对应的序号天数，连续日期会得到相同的
grp值（例如2024-01-01 - 1 day = 2023-12-31，2024-01-02 - 2 day = 2023-12-31） - 一旦出现断点（比如跳到
2024-01-04），序号继续 +1，但日期跳跃更大，grp值就变了 - MySQL 5.7 不支持窗口函数，得用变量模拟
ROW_NUMBER()，容易出错；建议升级或换方案
SQLite 和旧版 MySQL 的替代写法
没有 ROW_NUMBER() 时，可用自关联或子查询计算“前面有多少个更小的连续日期”，但性能差、易超时。更稳妥的做法是导出数据用 Python 或 shell 处理：
sqlite3 your.db "SELECT date_col FROM events ORDER BY date_col" | \
python3 -c "
import sys
dates = [line.strip() for line in sys.stdin]
if not dates: exit()
start = end = dates[0]
for d in dates[1:]:
from datetime import datetime, timedelta
prev = datetime.strptime(end, '%Y-%m-%d')
curr = datetime.strptime(d, '%Y-%m-%d')
if curr == prev + timedelta(days=1):
end = d
else:
print(start, end)
start = end = d
print(start, end)
"
- 纯 SQL 在 SQLite 中实现可靠连续分组非常繁琐，且无法处理跨年、闰年等边界
- 如果数据量
- 注意日期格式必须统一为
YYYY-MM-DD，否则字符串比较会出错（如'2024-1-1''2024-10-1'）
容易漏掉的边界情况
真实数据里，重复日期、空值、时区混用会让结果偏移，不是加个 DISTINCT 就能解决：
- 先
WHERE date_col IS NOT NULL，再GROUP BY date_col去重——否则重复日期会干扰ROW_NUMBER()编号 - 用
DATE(date_col)统一截断时间部分，避免'2024-01-01 14:00:00'和'2024-01-01 09:00:00'被当成同一天却因精度不同被拆开 - 跨时区数据要先转成统一时区（如 UTC），否则
2024-01-01T23:00Z和2024-01-02T01:00Z实际连续，但按本地日期算就断开了
连续日期聚合看着简单，真正上线前至少得拿含断点、重复、跨月的数据跑一遍验证。
