如何编写查询语句补全数据表中的缺失观测值？

要生成包含所有产品-年份-月份组合的完整数据表，补充缺失行的销售额（通常补0），可以通过生成全量组合+左连接原始表的方式实现，以下是通用SQL思路及示例：

核心逻辑 #

1. 提取原始表中所有唯一的产品、年份
2. 生成1-12月的完整月份列表
3. 通过交叉连接（CROSS JOIN）得到所有产品、年份、月份的组合
4. 左连接原始表，将缺失的销售额字段填充为0（或保留NULL）

示例SQL（以MySQL为例） #

假设你的原始表名为sales，字段为product（产品）、year（年份）、month（月份）、sales_amount（销售额）：

WITH months AS (
    -- 生成1-12月的完整列表
    SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
years AS (
    -- 提取原始表中所有唯一年份
    SELECT DISTINCT year FROM sales
),
products AS (
    -- 提取原始表中所有唯一产品
    SELECT DISTINCT product FROM sales
),
all_combinations AS (
    -- 生成所有产品-年份-月份的全量组合
    SELECT p.product, y.year, m.month
    FROM products p
    CROSS JOIN years y
    CROSS JOIN months m
)
-- 左连接原始表，填充缺失销售额为0
SELECT 
    ac.product,
    ac.year,
    ac.month,
    COALESCE(s.sales_amount, 0) AS sales_amount
FROM all_combinations ac
LEFT JOIN sales s 
    ON ac.product = s.product 
    AND ac.year = s.year 
    AND ac.month = s.month
ORDER BY ac.product, ac.year, ac.month;

适配说明 #

• 如果需要指定固定年份范围（比如只生成2022、2023年），可以把years部分改成：

  years AS (
      SELECT 2022 AS year UNION ALL SELECT 2023
  )
  

• 若使用SQL Server/PostgreSQL，生成月份列表可以用递归CTE，比如SQL Server：

  WITH months AS (
      SELECT 1 AS month
      UNION ALL
      SELECT month + 1 FROM months WHERE month < 12
  )
  

• 如果不需要将NULL转为0，去掉COALESCE函数即可，保留原始的NULL值。

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