SQL查询补全缺失月度数据：取当月最新值，缺失月用上月值填充

一、先搞定LEFT JOIN没生效的问题 #

你的LEFT JOIN结果和INNER JOIN一样，大概率是过滤条件放错了位置：

• 如果把原数据表的日期过滤写在WHERE里，会直接把LEFT JOIN生成的NULL行滤掉，必须把原表的日期条件移到ON关联语句里
• 关联时一定要用EOMONTH(原表日期字段)和月份表的月末日期做匹配，保证月份完全对齐

错误示例（会滤掉缺失月份）：

SELECT m.month_end, t.id, t.value
FROM month_table m
LEFT JOIN target_table t ON m.month_end = EOMONTH(t.date)
WHERE t.date BETWEEN '2023-01-01' AND '2023-12-31' -- 这里把无数据的月份直接筛掉了

正确写法：

SELECT m.month_end, id_list.id, t.value
FROM month_table m
CROSS JOIN (SELECT DISTINCT id FROM target_table) id_list -- 生成所有ID+月份的全量组合
LEFT JOIN target_table t 
  ON id_list.id = t.id 
  AND m.month_end = EOMONTH(t.date)
  AND t.date BETWEEN '2023-01-01' AND '2023-12-31' -- 原表条件移到ON里
WHERE m.month_end BETWEEN '2023-01-31' AND '2023-12-31' -- 只过滤月份表的范围

二、处理当月多条数据取最后一条 #

用ROW_NUMBER()窗口函数，按ID和月份分组，提取每个组内最新的一条数据：

WITH latest_monthly_data AS (
  SELECT 
    id,
    EOMONTH(date) AS month_end,
    value,
    -- 按日期倒序排，取每个ID+月份的第一条（即最新数据）
    ROW_NUMBER() OVER (PARTITION BY id, EOMONTH(date) ORDER BY date DESC) AS rn
  FROM target_table
  WHERE date BETWEEN '2023-01-01' AND '2023-12-31'
)
SELECT id, month_end, value
FROM latest_monthly_data
WHERE rn = 1

三、填充缺失月份的前值 #

把前面两步结合，再用LAST_VALUE()窗口函数（带IGNORE NULLS）自动填充空值：

WITH all_id_month AS (
  -- 生成所有ID和目标月份的全量组合
  SELECT 
    id_list.id,
    m.month_end
  FROM month_table m
  CROSS JOIN (SELECT DISTINCT id FROM target_table) id_list
  WHERE m.month_end BETWEEN '2023-01-31' AND '2023-12-31'
),
latest_data AS (
  -- 提取每个ID每个月的最新数据
  SELECT 
    id,
    EOMONTH(date) AS month_end,
    value,
    ROW_NUMBER() OVER (PARTITION BY id, EOMONTH(date) ORDER BY date DESC) AS rn
  FROM target_table
  WHERE date BETWEEN '2023-01-01' AND '2023-12-31'
),
joined_data AS (
  -- 关联全量组合和最新数据，得到带NULL的中间结果
  SELECT 
    aim.id,
    aim.month_end,
    ld.value
  FROM all_id_month aim
  LEFT JOIN latest_data ld 
    ON aim.id = ld.id 
    AND aim.month_end = ld.month_end
  WHERE ld.rn IS NULL OR ld.rn = 1
)
-- 用LAST_VALUE填充缺失的月份值
SELECT 
  id,
  month_end,
  LAST_VALUE(value IGNORE NULLS) OVER (
    PARTITION BY id 
    ORDER BY month_end 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS filled_value
FROM joined_data
ORDER BY id, month_end;

注意事项 #

• 不同数据库对IGNORE NULLS的支持有差异：
  • SQL Server、MySQL 8.0+、Oracle直接支持该语法
  • PostgreSQL需要用COALESCE(value, LAST_VALUE(value) OVER (...))结合窗口逻辑，或者嵌套LAG函数实现填充
• 月份表必须包含你需要的所有月份的月末日期（比如2023-01-31、2023-02-28这类格式）

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