在PostgreSQL 14中怎样使用GROUP BY统计高频访问路径？-SQL-PHP中文网

group by前需清洗path字段：去查询参数、尾斜杠、统一大小写；where条件须置于group by前；排除null/空值；建表达式索引；order by需二级排序保结果稳定。

在postgresql 14中怎样使用group by统计高频访问路径？

GROUP BY 路径字段前必须清洗和标准化

原始日志表里 path 字段常含查询参数、尾部斜杠不一致、大小写混用，比如 /api/user、/api/user/?id=123、/api/user/ 会被当成三个不同值。直接 GROUP BY path 会导致频次分散、结果失真。

实操建议：

用 REGEXP_REPLACE(path, '\?.*$', '') 去掉全部查询参数
用 TRIM(TRAILING '/' FROM ...) 统一去除末尾斜杠
必要时加 LOWER() 统一大小写（尤其当 Nginx 或前端路由不规范时）
若字段名是 request_path 或 uri，记得替换，别硬写 path

WHERE 条件必须放在 GROUP BY 内部，不能丢到外层

比如只想统计最近7天、状态码为200的访问路径，如果把 WHERE status = 200 AND created_at >= NOW() - INTERVAL '7 days' 放在外层 SELECT，PostgreSQL 会先对全量数据分组再过滤——既慢又可能因临时表溢出失败。

正确写法是把条件塞进子查询或主查询的 FROM 后、GROUP BY 前：

SELECT 
  TRIM(TRAILING '/' FROM REGEXP_REPLACE(path, '\?.*$', '')) AS clean_path,
  COUNT(*) AS freq
FROM logs 
WHERE status = 200 
  AND created_at >= NOW() - INTERVAL '7 days'
GROUP BY TRIM(TRAILING '/' FROM REGEXP_REPLACE(path, '\?.*$', ''))
ORDER BY freq DESC
LIMIT 10;

注意：SELECT 和 GROUP BY 中的表达式必须完全一致，否则 PostgreSQL 14 会报错 column "xxx" must appear in the GROUP BY clause or be used in an aggregate function。

高频路径统计容易忽略 NULL 和空字符串

日志中常有 path IS NULL 或空字符串 ''，它们会被单独分组，且频次可能意外排第一（尤其当采集链路异常时）。这会让真正关心的业务路径被挤到后面。

避免方式：

显式排除：WHERE path IS NOT NULL AND TRIM(path) != ''
在清洗后加 HAVING COUNT(*) > 10 过滤掉低频噪音（比如只看至少被访问10次的路径）
建索引加速：CREATE INDEX idx_logs_clean_path ON logs ((TRIM(TRAILING '/' FROM REGEXP_REPLACE(path, '\?.*$', '')))); —— 注意这是表达式索引，PostgreSQL 14 支持，但需用双括号包裹