识别异常频次需用LAG()计算同IP相邻登录时间差，转为秒数后筛选≤60秒的短间隔，且WHERE必须置于子查询内以保障性能与正确性。
用 ROW_NUMBER() 标出每个IP的登录序号再筛突增
直接对 ip 分组计数只能看到总量，但“异常频次”本质是时间局部突增——比如某IP在5分钟内从平均1次/小时突然变成12次。这时得先按时间排序打序号，再看相邻行的时间差是否小于阈值。
关键点：窗口函数不聚合，保留原始行，才能做“当前行 vs 上一行”的对比。
- 必须用
ORDER BY login_time，否则LAG()拿不到正确前一行 -
PARTITION BY ip是必须的，否则不同IP的时间混在一起算差值会出错 - 别用
ROW_NUMBER()直接替代时间差判断——它只编号，不反映真实间隔
SELECT ip, login_time,
login_time - LAG(login_time) OVER (PARTITION BY ip ORDER BY login_time) AS gap
FROM login_log
WHERE login_time > NOW() - INTERVAL '30 minutes';
LAG() 计算IP相邻登录时间差并过滤短间隔
真正识别“异常频次”的核心不是总次数，而是密集程度。用 LAG() 拿到上一次登录时间，相减后判断是否 ≤ 60 秒，就能抓出秒级重试或脚本扫号行为。
注意 PostgreSQL 对 interval 的减法返回的是 interval 类型，不能直接跟数字比，要用 EXTRACT(EPOCH FROM ...) 转成秒数。
- WHERE 条件必须放在子查询内，否则先算全量再过滤，性能暴跌
- 如果日志表没在
ip和login_time上建联合索引，这个查询会很慢 - 空值处理：首条记录的
LAG()返回 NULL，需用WHERE gap IS NOT NULL过滤
SELECT ip, login_time, gap_sec
FROM (
SELECT ip, login_time,
EXTRACT(EPOCH FROM (login_time - LAG(login_time)
OVER (PARTITION BY ip ORDER BY login_time))) AS gap_sec
FROM login_log
WHERE login_time > NOW() - INTERVAL '15 minutes'
) t
WHERE gap_sec <= 60;用 COUNT(*) OVER 算滚动窗口内频次再设阈值
固定时间窗（如“过去5分钟”）内的登录次数，比单纯看相邻间隔更能反映真实攻击强度。PostgreSQL 不支持 RANGE BETWEEN INTERVAL 的窗口框架，得用自连接或子查询模拟，但更稳的方式是用 COUNT(*) OVER 配合 ORDER BY login_time + ROWS BETWEEN 做近似滚动统计。
例如：每行计算“自身及前4行（共5次登录）的时间跨度”，再筛跨度 ≤ 300 秒的组——这比硬写子查询快得多。
-
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW是行数控制，不是时间控制，适合日志密度较均匀的场景 - 若日志稀疏（比如用户偶尔登录），这种行数窗口会漏掉跨行但时间接近的密集行为
- 真要严格按时间滚动，得用
generate_series()+LATERAL JOIN，但代价高，只建议离线分析用
SELECT ip, login_time, cnt
FROM (
SELECT ip, login_time,
COUNT(*) OVER (
PARTITION BY ip
ORDER BY login_time
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS cnt
FROM login_log
WHERE login_time > NOW() - INTERVAL '10 minutes'
) t
WHERE cnt >= 5;PostgreSQL 中 HAVING 不能和窗口函数混用的坑
有人想写 GROUP BY ip HAVING COUNT(*) OVER (...) > 10，这是语法错误。窗口函数不能出现在 HAVING 子句里，也不能和聚合函数在同一层 SELECT 中混用（除非嵌套子查询）。
常见误报来源：把“高频IP”理解为“总次数高”，而忽略了时间维度。结果查出的是长期活跃用户（比如运维同事），不是异常IP。
- 正确路径永远是：先用窗口函数生成每行的上下文指标（如最近3次间隔、滚动5分钟计数），再在外层 WHERE 筛
- 别在 GROUP BY 后试图用窗口函数——它早该在聚合前就计算完了
- 如果必须关联历史均值（比如“比自己7天均值高3倍”），得用
WITH先算均值表，再 JOIN，而不是塞进一个 SELECT 里硬搞
实际跑起来最常卡住的地方不是语法，而是没意识到：窗口函数本身不减少行数，所有过滤逻辑都得靠外层 WHERE 或子查询完成；而 WHERE 放错位置，要么结果错，要么慢十倍。
