PostgreSQL中Full Outer Join填充关联表缺失值的实现方法

问题梳理 #

你当前的查询存在几个核心问题：

• CTE命名逻辑颠倒：yesterday_report_count实际查的是最近24小时（你定义的「今日」），子查询的today_report_count查的是此前24小时（「昨日」），导致字段别名对应错误
• 语法错误：GROUP BY和ORDER BY末尾多了逗号，会直接导致执行报错
• 缺失值填充逻辑未落地：full join后未用coalesce()正确补全缺失的类型和数量值

修正后的SQL #

WITH
  today_report_count AS (
    SELECT
      "reports"."report"."type" AS "type",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
      "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
    GROUP BY
      "reports"."report"."type"
  ),
  yesterday_report_count AS (
    SELECT
      "reports"."report"."type" AS "type",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
      "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '48 HOURS' AND NOW() - INTERVAL '24 HOURS'
    GROUP BY
      "reports"."report"."type"
  )
SELECT 
  COALESCE(today.type, yesterday.type) AS "今日类型",
  COALESCE(today.count, 0) AS "今日数量",
  COALESCE(yesterday.type, today.type) AS "昨日类型",
  COALESCE(yesterday.count, 0) AS "昨日数量"
FROM
  today_report_count AS today
FULL JOIN yesterday_report_count AS yesterday
  ON today.type = yesterday.type
ORDER BY
  "今日类型" ASC;

关键调整说明 #

• 修正CTE命名逻辑：让today_report_count对应最近24小时数据，yesterday_report_count对应此前24小时数据，避免混淆
• 移除冗余排序：CTE内的ORDER BY对最终join结果无意义，统一放到最后排序即可
• 完善缺失值填充：
  • 类型字段：用COALESCE()取两侧非空的type值，确保单侧缺失时类型列能被补全
  • 数量字段：用COALESCE(..., 0)将缺失的count值替换为0
• 修复语法错误：删除GROUP BY末尾的多余逗号，避免执行报错

最终效果 #

执行修正后的SQL后，会生成你期望的结果：

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