在PostgreSQL中如何利用窗口函数识别异常登录IP频次-SQL-PHP中文网

识别异常频次需用lag()计算同ip相邻登录时间差，转为秒数后筛选≤60秒的短间隔，且where必须置于子查询内以保障性能与正确性。

在postgresql中如何利用窗口函数识别异常登录ip频次

用 `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 <h3>用 <code>COUNT(*) OVER</code> 算滚动窗口内频次再设阈值</h3><p>固定时间窗（如“过去5分钟”）内的登录次数，比单纯看相邻间隔更能反映真实攻击强度。PostgreSQL 不支持 <code>RANGE BETWEEN INTERVAL</code> 的窗口框架，得用自连接或子查询模拟，但更稳的方式是用 <code>COUNT(*) OVER</code> 配合 <code>ORDER BY login_time</code> + <code>ROWS BETWEEN</code> 做近似滚动统计。</p><p>例如：每行计算“自身及前4行（共5次登录）的时间跨度”，再筛跨度 ≤ 300 秒的组——这比硬写子查询快得多。</p>

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;