SQL 如何处理缺失数据并补齐时间轴？

SQL补齐时间轴需两步：先生成完整时间序列，再左连接原始数据补NULL。PostgreSQL用generate_series，MySQL 8.0+用递归CTE，SQL Server用递归CTE或数字表。

SQL 处理缺失数据并补齐时间轴，核心是两步：先生成完整的时间序列（比如每天、每小时），再用左连接把原始数据“挂”上去，空缺位置自然为 NULL，后续可按需填充（如前向填充、默认值、插值等）。

生成连续时间轴

不同数据库语法略有差异，但思路一致：用递归 CTE（PostgreSQL/SQL Server）、generate_series（PostgreSQL）、或数字表 + DATE_ADD（MySQL）构造日期序列。

• PostgreSQL 示例（补全 2025-01-01 到 2025-01-10 的每日记录）：
  WITH date_series AS (
    SELECT generate_series('2025-01-01'::date, '2025-

  

  01-10'::date, '1 day')::date AS dt
  )
  SELECT * FROM date_series;
• MySQL 8.0+（用递归 CTE）：
  WITH RECURSIVE dates AS (
    SELECT '2025-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt )
  SELECT dt FROM dates;
• SQL Server（使用 master..spt_values 或递归 CTE）：
  ;WITH dates AS (
    SELECT CAST('2025-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM dates WHERE dt )
  SELECT dt FROM dates OPTION (MAXRECURSION 0);

左连接原始数据补空行

将生成的时间轴与业务表 LEFT JOIN，确保每个时间点都有一行，缺失数据自动为 NULL。

• 假设原始表 sales 含 sale_date 和 amount，想补全每日销售记录：
  WITH date_series AS ( /* 上面生成的日期序列 */ ),
  full_data AS (
    SELECT ds.dt, s.amount
    FROM date_series ds
    LEFT JOIN sales s ON ds.dt = s.sale_date
  )
  SELECT * FROM full_data;
• 结果中 amount 为 NULL 的行，即原表中该日无销售记录。

填充缺失值（常见策略）

补出空行后，根据业务逻辑决定如何填 NULL：

• 填 0（适合计数类指标）：COALESCE(amount, 0)
• 前向填充（FFILL，适合状态延续场景）：
  PostgreSQL 可用窗口函数：
  COALESCE(amount,
    FIRST_VALUE(amount) OVER (
      ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ))
  注意：需先按时间排序，并处理首行为 NULL 的情况（可用 LAG + COALESCE 组合实现更稳健的 FFILL）。
• 用上一非空值的平均值或中位数填充（需额外子查询）：适用于离散波动不大的指标。

注意事项

时间轴补齐本质是「维度对齐」，实际使用时要注意：

• 原始表中的时间字段需提前标准化（如截断到日、去时区、转为 date 类型），否则 JOIN 不匹配；
• 若需按多个维度（如「每个产品 + 每天」）补齐，要在时间轴基础上 CROSS JOIN 产品列表，再 LEFT JOIN 原始事实表；
• 大数据量下，生成超长日期序列可能影响性能，建议限制范围或建物化日期维表；
• 某些场景（如传感器每5分钟一条）需用 generate_series(start, end, '5 min') 等粒度控制，而非固定按天。