PostgreSQL多表统计：按错误消息汇总任务数据及错误次数

基础实现（匹配示例需求） #

直接关联三张表并按错误分组统计，即可得到你需要的结果：

SELECT
  e.error,
  SUM(m.total_task) AS `main count`,
  COUNT(e.e_id) AS `error count`
FROM Error e
JOIN Task t ON e.t_id = t.t_id
JOIN Main m ON t.id = m.id
GROUP BY e.error
ORDER BY e.error;

逻辑说明 #

1. 表关联：通过Error.t_id关联Task获取对应Main表的id，再通过Task.id关联Main表拿到total_task数值；
2. 分组统计：
   • SUM(m.total_task)：计算当前错误涉及的所有任务对应的total_task之和，即你要的main count；
   • COUNT(e.e_id)：统计当前错误出现的总次数，即error count；

特殊场景处理（同一个Main id仅统计一次total_task） #

如果需求是：即使同一个Main id对应多个同错误的任务，total_task只计算一次，需要先对错误关联的Main id去重，再统计：

SELECT
  error,
  SUM(total_task) AS `main count`,
  MAX(error_count) AS `error count`
FROM (
  -- 子查询：按错误+Main id去重，同时统计每个错误的总次数
  SELECT
    e.error,
    m.total_task,
    COUNT(e.e_id) OVER (PARTITION BY e.error) AS error_count
  FROM Error e
  JOIN Task t ON e.t_id = t.t_id
  JOIN Main m ON t.id = m.id
  GROUP BY e.error, m.id, m.total_task
) AS deduplicated_data
GROUP BY error;

结果验证 #

执行基础实现的SQL后，会得到你预期的结果：

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