SQL 窗口函数在日志分析中的应用

应使用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp, id) 确保稳定排序，避免仅依赖精度不足的时间字段；需处理 NULL 值、时间精度转换及脏数据过滤。

如何用 ROW_NUMBER() 给日志事件按时间排序编号

日志里同一用户可能在毫秒级内产生多条记录，ORDER BY timestamp 不够稳定，直接 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) 会因时间精度不足导致序号乱序。必须补上二级排序：比如 id（自增主键）或 log_id（唯一标识），确保顺序确定。

实操建议：

• 优先用 ORDER BY timestamp, id，避免仅依赖时间字段
• 如果日志时间含毫秒但数据库只存到秒（如 mysql 5.6 的 DATETIME），需先转成带精度的类型（如 CAST(timestamp AS DATETIME(3))）
• 注意 NULL 时间值：加 WHERE timestamp IS NOT NULL 或在 ORDER BY 中显式写 timestamp ASC NULLS LAST（postgresql/oracle 支持；MySQL 8.0+ 可用 IFNULL(timestamp, '1970-01-01')）

用 LAG() 和 LEAD() 计算用户操作间隔

分析用户两次点击之间是否超时、是否快速重试，本质是取当前行的前一行/后一行时间做差。但日志常有脏数据：重复采集、乱序写入、缺失字段。

实操建议：

• 先过滤掉无效时间：WHERE timestamp > '2024-01-01' AND timestamp IS NOT NULL
• 用 LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp, id) 获取上一次合法时间，再用 EXTRACT(EPOCH FROM (timestamp - prev_ts))（PostgreSQL）或 TIMESTAMPDIFF(SECOND, prev_ts, timestamp)（MySQL）算秒级间隔
• 警惕 LAG() 返回 NULL：首条记录无“上一条”，需用 COALESCE(prev_ts, timestamp) 避免整列计算失败
• 若日志跨天但未分区，PARTITION BY user_id 是必须的，否则用户 A 的末条和用户 B 的首条会被错误连起来

count() OVER 和 MAX() OVER 快速统计会话行为

识别一次“会话”（session）通常靠时间窗口（如 30 分钟无活动即断开），但纯用窗口函数无法动态划分 session_id。更可行的是：先用 LAG() 标出“断连点”，再用累计求和生成 session_id，最后用 COUNT() OVER (PARTITION BY session_id) 算单次会话总操作数。

实操建议：

• 不要直接 COUNT(*) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)——这算的是用户累计行为，不是单次会话
• session_id 构建逻辑示例（PostgreSQL）：

  SELECT *,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY timestamp, id) AS session_id
  FROM (
    SELECT *,
      CASE WHEN EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp, id))) > 1800 THEN 1 ELSE 0 END AS is_new_session
    FROM logs
  ) t

• 后续再套一层 COUNT(*) OVER (PARTITION BY user_id, session_id) 就能得到每会话操作次数

为什么 RANK() 在日志去重中要慎用

有人想用 RANK() OVER (PARTITION BY user_id, event_type ORDER BY timestamp) 取 rank = 1 来保留每个用户每类事件的“首次发生”，但 RANK() 遇到相同时间会并列且跳号，而日志里高频事件常时间戳完全一致（尤其埋点 SDK 批量上报）。

实操建议：

• 改用 ROW_NUMBER()：它不并列，强制唯一排序，哪怕时间相同也靠二级字段（如 id）区分
• 如果真要“同时间只留一条”，且不关心留哪条，可用 DENSE_RANK() + WHERE dr = 1，但得接受非确定性结果
• 更稳妥做法是先用 GROUP BY user_id, event_type, DATE_TRUNC('second', timestamp) 聚合去噪，再在聚合后用窗口函数

窗口函数本身不解决日志乱序、重复、缺失问题，它们只是在已有数据上做有序计算。真正可靠的分析，始于对原始日志写入链路的理解——比如 kafka 分区策略是否打散了用户行为，flink 处理时间 vs 事件时间配置是否匹配业务语义。