SQL中怎样利用窗口函数识别用户行为路径的断点？

LAG()配合时间差计算是识别业务断点最可靠方法，需用LAG(event_time)按业务时间排序、显式对齐时区、依业务定义阈值生成segment_id，再以user_id和segment_id双重分区进行路径拼接与首尾标定。

LAG() + 时间差判断断点最可靠

窗口函数本身不识别业务断点，LAG() 是唯一能主动探测“时间空档”的入口。直接用 ORDER BY event_time 排序后计算行间差值，才是生产环境通用解法。

• 必须用 LAG(event_time) 而非 LAG(event_id) —— 物理主键顺序 ≠ 业务时间顺序，尤其在分布式写入或批量补数时极易出错
• MySQL 用 TIMESTAMPDIFF(HOUR, lag_time, event_time)，PostgreSQL 用 EXTRACT(EPOCH FROM (event_time - lag_time)) / 3600，别混用单位
• 阈值不是技术参数，是业务定义：Web 用户会话中断 30 分钟算新会话，IoT 设备心跳超 5 分钟就算离线，硬套统一值会导致分组漂移
• 时区必须显式对齐：CONVERT_TZ(event_time, '+08:00', '+00:00') 或 event_time AT TIME ZONE 'UTC'，否则跨时区数据一算就错

用累计和生成 segment_id 切分逻辑段

单纯标记断点没用，关键要把连续段打上稳定 ID，后续所有聚合、排序、路径拼接都得靠它隔离。

• 先用 CASE WHEN hours_gap > 30 THEN 1 ELSE 0 END AS is_break 生成断点标识（注意单位是分钟还是小时）
• 再用 SUM(is_break) OVER (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 累计求和，得到 segment_id
• PARTITION BY user_id, segment_id 才是真正安全的窗口划分方式，漏掉 user_id 就会把不同用户行为混进同一段
• 不要用 RANGE BETWEEN INTERVAL '1 HOUR' PRECEDING AND CURRENT ROW 替代——它只框物理时间范围，无法跳过中间缺失的三天数据

断点处理后的行为路径拼接

有了 segment_id，路径拼接才不会跨会话乱连。此时 STRING_AGG() 或 COLLECT_LIST() 才有意义。

• MySQL 8.0+ 用 STRING_AGG(event_type, '→' ORDER BY event_time)，Hive/Spark SQL 用 CONCAT_WS('→', COLLECT_LIST(event_type))
• 必须加 ORDER BY event_time，否则同会话内事件顺序随机，view→pay→cart_add 这种错序会直接误导分析结论
• 如果同一秒内多事件，二级排序不能省：ORDER BY event_time, event_type（按业务语义约定先后，比如 'click' 排 'submit' 前）
• 拼接结果里出现 login→logout→login？说明断点阈值设太小，会话被不合理切碎，得回头调大 hours_gap 阈值

为什么 FIRST_VALUE/LAST_VALUE 在断点场景容易失效

很多人想用 FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_time) 标首行为，但没意识到：它默认只看当前窗口帧，而未显式声明 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 时，LAST_VALUE 实际只返回当前行及之前行的最后一个值。

• 断点存在时，一个用户可能有多个会话，每个会话都有自己的首尾行为；若不分 segment_id 直接 PARTITION BY user_id，FIRST_VALUE 会始终返回该用户最早那次登录，完全失真
• 正确写法是 PARTITION BY user_id, segment_id，再套 FIRST_VALUE 和 LAST_VALUE，且 LAST_VALUE 必须带完整 ROWS 子句
• 更隐蔽的坑：event_time 若为 DATE 类型（无时分秒），同一日多次行为会被视为“同时发生”，ORDER BY 随机排序，首尾标定全乱

断点识别不是纯 SQL 技术活，它卡在业务定义、时区对齐、时间精度三处。跑通一条路径查询不难，难的是每次换业务场景（比如从电商会话切到 IoT 设备心跳）都得重新校准阈值和时区转换逻辑。