lag()和lead()是日志审计最常用窗口函数，因其能直接对比相邻时间点操作行为，实现“5秒内连续失败3次后成功”等时序模式的单层查询表达。
为什么 LAG() 和 LEAD() 是日志审计最常用的窗口函数
因为它们能直接对比相邻时间点的操作行为，比如“用户A在5秒内连续失败登录3次后成功”，这种模式靠 GROUP BY 或子查询极难表达。窗口函数让时序比较变成单层查询。
实操建议：
- 必须用
ORDER BY event_time显式排序，否则LAG()行为不可预测（PostgreSQL 不保证物理顺序） - 对高并发日志表，先在
event_time, user_id上建复合索引，否则OVER (PARTITION BY user_id ORDER BY event_time)会全表扫描 - 慎用
LAG(col, 2)这类偏移量大于1的写法——若中间有缺失行（如日志采样丢弃），结果会跳过真实相邻事件
用 ROW_NUMBER() 标记异常会话的首次/末次操作
审计常需定位“某次越权访问是否是该会话第一次敏感操作”。ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) 能给每条日志打上会话内序号，比用 MIN(event_time) 关联更稳定。
常见错误现象：用 RANK() 替代 ROW_NUMBER() 导致相同时间戳日志获得相同序号，破坏“首次”判定逻辑。
使用场景示例（标记每个会话第一条日志）：
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) AS rn
FROM audit_log
WHERE event_type IN ('login', 'delete', 'grant_privilege')
) t
WHERE rn = 1;
注意：PARTITION BY session_id 的粒度要和业务会话标识一致——若前端用 JWT 且后端未解析出 session_id，可能得改用 PARTITION BY ip_address, user_agent 临时兜底。
COUNT() OVER 窗口计数容易忽略的性能陷阱
审计规则如“10分钟内同一IP触发5次错误SQL”，看似适合 COUNT(*) OVER (PARTITION BY ip_address ORDER BY event_time RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW)，但实际执行很慢。
原因：RANGE 窗口需逐行计算时间区间内的所有行，数据量大时 I/O 压力陡增；而等价的 ROWS BETWEEN 100 PRECEDING AND CURRENT ROW 是固定行数，快一个数量级。
可行方案：
- 若日志写入频率稳定（如每秒10条），用
ROWS+ 估算行数替代RANGE - 对严格时间窗口需求，先用物化视图按分钟聚合计数，再用窗口函数查聚合结果
- 避免在 WHERE 条件里引用窗口函数结果（如
HAVING cnt > 5），必须用子查询或 CTE 包裹
实时审计中 WINDOW 命名与复用的必要性
复杂审计规则常需多个窗口函数（如同时算前一次操作类型、最近3次失败次数、当前会话总耗时），重复写 OVER (PARTITION BY user_id ORDER BY event_time) 不仅冗长，还易因一处修改不一致导致逻辑偏差。
正确做法是用 WINDOW 子句命名并复用：
SELECT user_id,
event_type,
LAG(event_type) OVER w AS prev_event,
COUNT(*) FILTER (WHERE event_type = 'failed_login') OVER w AS fail_cnt_7d
FROM audit_log
WINDOW w AS (PARTITION BY user_id ORDER BY event_time
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW);
关键细节：命名窗口仍可被后续 OVER w 复用，但不能在 WINDOW 定义里引用其他命名窗口——想组合逻辑（如“前一次是失败，本次是成功”）仍需嵌套 CTE。
最容易被忽略的是：命名窗口中的 RANGE 或 ROWS 边界一旦定义，所有复用它的函数都共享同一窗口帧，无法单独调整。需要不同窗口范围时，必须定义多个命名窗口（如 w_1min, w_1h）。
