PostgreSQL查询：按IP端口分组获取最新时间戳去重记录

推荐写法（PostgreSQL原生优化语法） #

直接使用PostgreSQL专属的DISTINCT ON语法实现，是该场景下性能最优的写法，查询结果可直接供MISP同步逻辑使用，无需额外代码层去重：

SELECT DISTINCT ON (resp_h, resp_p)
  ts, resp_h, resp_p
FROM bro_data
WHERE infection = INFECTIONID
  AND resp_h IS NOT NULL
  AND resp_p IS NOT NULL
ORDER BY resp_h, resp_p, ts DESC;

语法说明 #

• DISTINCT ON (resp_h, resp_p)：指定以响应IP、响应端口的组合作为唯一分组判定依据
• ORDER BY子句必须将分组字段resp_h、resp_p放在最前面，后续紧跟时间戳字段ts倒序排列，会让每个分组下时间最新的记录排在组内第一位，DISTINCT ON仅返回每个分组的第一条记录，正好匹配需求
• 针对提到的64.62.200.237:443多时间戳记录场景，该语句会自动返回时间为2022-07-05 06:29:37.149036 +00:00的最新记录

通用标准SQL写法（可选） #

如果需要兼容标准SQL语法，可使用窗口函数ROW_NUMBER()实现，性能略低于上述原生写法，适合跨数据库兼容场景：

WITH ranked_records AS (
  SELECT
    ts, resp_h, resp_p,
    ROW_NUMBER() OVER (PARTITION BY resp_h, resp_p ORDER BY ts DESC) AS row_rank
  FROM bro_data
  WHERE infection = INFECTIONID
    AND resp_h IS NOT NULL
    AND resp_p IS NOT NULL
)
SELECT ts, resp_h, resp_p
FROM ranked_records
WHERE row_rank = 1
ORDER BY ts DESC;

注意事项 #

• 如果存在同一(resp_h, resp_p)组合下多条记录时间戳完全一致的极端情况，可在ORDER BY中追加唯一字段（比如表自增主键ID）作为兜底排序规则，例如ORDER BY resp_h, resp_p, ts DESC, id DESC，保证返回结果完全稳定
• 数据量较大时，建议创建(infection, resp_h, resp_p, ts DESC)的联合索引，可进一步大幅提升查询速度

内容的提问来源于stack exchange，提问作者Thomas Ward