sql统计连续行为的核心是用窗口函数构造分组标识：通过行号差法（如 per_user_seq 与 global_seq 相减）生成 group_id，再按 user_id 和 group_id 聚合获取连续段起止及长度；需区分“序列连续”与“日期连续”，并可用 字符串 拼接验证结果。
统计连续行为，核心是识别“连续”——即按时间或序号 排列 后，相邻记录满足相同条件。SQL 里最常用、最可靠的方式是用窗口函数构造分组标识，再聚合。关键不在“怎么写”，而在“怎么想”：把连续段转化为可分组的逻辑块。
用行号差法识别连续登录天数
用户每天一条登录记录，要找出所有连续登录≥3 天的用户及起止日期。思路是：对每个用户按日期排序，生成行号；再给所有日期整体排序（不区分用户）也生成行号；两个行号相减，同一连续段的结果恒定——这就是“连续组 ID”。
- 先按 user_id 分组、date 排序，用 ROW_NUMBER() 得到 per_user_seq
- 再对全表 date 排序（忽略 user_id），得 global_seq
- 计算 group_id = date – INTERVAL (per_user_seq – 1) DAY，或更通用：group_id = DATE_SUB(date, INTERVAL (per_user_seq – 1) DAY)
- 按 user_id 和 group_id 分组，count() 即连续天数，MIN/MAX 得区间
处理缺失日期的“伪连续”陷阱
如果原始数据本身有日期空缺（比如用户某天没记录，不代表没登录），直接按日期相减会出错。稳妥做法是用 序号差 而非日期差：ROW_NUMBER() over (partition by user_id order by date) 减去 ROW_NUMBER() over (order by date)，只要序号差相同，就说明中间没跳过——这是真正的“序列连续”。日期是否连续，由业务定义；SQL 只保证按你给的排序逻辑识别出连续块。
连续操作类型 + 时长双维度统计
比如统计用户连续点击“加入购物车”且总时长≤2 小时的行为段。这时不能只看序号，需结合时间戳判断间隔：
– 用 LAG() 取上一行时间，算当前与前次的时间差
– 差值 > 2 小时 → 新连续段开始，用 SUM() over (rows unbounded preceding) 累计标记断点
– 最终用断点累计值作为 group_id，再聚合
- 新增列 is_new_seg = CASE WHEN timestamp – LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) > INTERVAL ‘2 HOUR’ THEN 1 ELSE 0 END
- 用 SUM(is_new_seg) OVER (PARTITION BY user_id ORDER BY timestamp) 生成 segment_id
- 按 user_id + segment_id 分组，统计 COUNT、MIN、MAX、SUM 等
小技巧：用字符串拼接快速验证连续段
开发调试时，可用 GROUP_CONCAT（mysql）或 STRING_AGG（postgresql）把连续段内的日期 / 操作拼成字符串，一眼看出是否真连续：
SELECT user_id, segment_id, STRING_AGG(date::text, ‘,’ ORDER BY date) AS dates_in_seg FROM grouped_data GROUP BY user_id, segment_id HAVING COUNT(*) >= 3;
看到 “2024-05-01,2024-05-02,2024-05-03” 就放心；若出现 “2024-05-01,2024-05-03″，说明漏了数据或逻辑有误。
