Postgres如何合并统计工单周创建、周解决量的两个SQL查询

下面提供两种常用的实现方式，均可满足需求： #

方案1：FULL OUTER JOIN 关联两个聚合子查询 #

通过全外连接关联每周创建数、每周解决数两个独立统计结果，避免某周只有创建工单/只有解决工单时数据遗漏。

SELECT
  COALESCE(c.week, r.week) AS statistic_week,
  COALESCE(c.created_count, 0) AS weekly_created_count,
  COALESCE(r.resolved_count, 0) AS weekly_resolved_count
FROM (
  -- 子查询：统计每周创建工单数
  SELECT 
    date_trunc('week', createddate) AS week,
    COUNT(id) AS created_count
  FROM tickets
  GROUP BY 1
) c
FULL OUTER JOIN (
  -- 子查询：统计每周解决工单数
  SELECT 
    date_trunc('week', resolutiondate) AS week,
    COUNT(id) AS resolved_count
  FROM tickets
  WHERE resolutiondate IS NOT NULL
  GROUP BY 1
) r ON c.week = r.week
ORDER BY statistic_week;

方案2：UNION ALL + 条件聚合（性能更优） #

仅需扫描一次表，适合数据量较大的场景：先把每个工单的创建、解决事件拆分为两行数据，再按周分组聚合统计。

SELECT
  week AS statistic_week,
  SUM(created_flag) AS weekly_created_count,
  SUM(resolved_flag) AS weekly_resolved_count
FROM (
  -- 提取所有工单的创建时间维度
  SELECT
    date_trunc('week', createddate) AS week,
    1 AS created_flag,
    0 AS resolved_flag
  FROM tickets
  UNION ALL
  -- 提取已完工单的解决时间维度
  SELECT
    date_trunc('week', resolutiondate) AS week,
    0 AS created_flag,
    1 AS resolved_flag
  FROM tickets
  WHERE resolutiondate IS NOT NULL
) t
GROUP BY 1
ORDER BY 1;

补充说明 #

• 两种方案均通过COALESCE或者初始赋值0的方式，避免某周只有一类工单时另一列返回NULL的问题
• 如果需要限定统计的时间范围，可在对应子查询中添加时间过滤条件

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