如何用SQL填充员工时间线中的稀疏数据？

我有一张存储员工各类信息变更的表，部分信息随时间变化，但并非所有信息同时变更，变更周期不固定。记录按日期存储，若某员工在某时间未变更某项信息，该字段值为Null。原始数据表如下：

需要编写SQL查询，将Null值替换为该员工最近的非Null值（若无历史非Null值则保留Null），生成目标数据表如下：

（注：上述表格中替换后的取值取自该员工的历史最近有效记录）

希望将该查询创建为视图，以便后续查询任意日期的员工信息（例如查询2021-08-17时员工的薪资和通勤距离），请问该如何实现？是否有更优方案？

1. 创建填充历史值的视图 #

核心思路是用窗口函数获取每个员工每条记录之前的最近有效值，不同SQL方言语法略有差异，以下是两种通用方案：

兼容多数数据库的通用写法 #

CREATE VIEW employee_current_info AS
SELECT
    employeeId,
    Date,
    -- 填充Salary的最近非Null值
    FIRST_VALUE(Salary) OVER (
        PARTITION BY employeeId
        ORDER BY CASE WHEN Salary IS NOT NULL THEN 0 ELSE 1 END, Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Salary,
    -- 填充CommuteDistance的最近非Null值
    FIRST_VALUE(CommuteDistance) OVER (
        PARTITION BY employeeId
        ORDER BY CASE WHEN CommuteDistance IS NOT NULL THEN 0 ELSE 1 END, Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CommuteDistance
FROM employee_changes;

支持IGNORE NULLS的简化写法（PostgreSQL 11+、Oracle等） #

直接使用LAST_VALUE忽略Null值，语法更简洁：

CREATE VIEW employee_current_info AS
SELECT
    employeeId,
    Date,
    LAST_VALUE(Salary) IGNORE NULLS OVER (
        PARTITION BY employeeId
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Salary,
    LAST_VALUE(CommuteDistance) IGNORE NULLS OVER (
        PARTITION BY employeeId
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CommuteDistance
FROM employee_changes;

2. 查询任意日期的员工信息 #

视图创建后，查询指定日期（如2021-08-17）的员工最新信息，只需取每个员工在该日期之前的最后一条记录：

SELECT
    employeeId,
    Salary,
    CommuteDistance
FROM (
    SELECT
        employeeId,
        Salary,
        CommuteDistance,
        ROW_NUMBER() OVER (
            PARTITION BY employeeId
            ORDER BY Date DESC
        ) AS rn
    FROM employee_current_info
    WHERE Date <= '2021-08-17'
) t
WHERE rn = 1;

3. 性能优化方案 #

如果数据量较大，实时窗口函数计算可能存在性能瓶颈，可尝试以下优化：

• 预处理快照表：定期（如每日）生成员工信息快照表，存储每个员工截至当日的最新信息，查询时直接读取快照表，避免实时计算：

  -- 创建每日快照表
  CREATE TABLE employee_daily_snapshot (
      employeeId INT,
      snapshot_date DATE,
      Salary INT,
      CommuteDistance INT,
      PRIMARY KEY (employeeId, snapshot_date)
  );
  
  -- 每日更新快照
  INSERT INTO employee_daily_snapshot
  SELECT
      employeeId,
      CURRENT_DATE AS snapshot_date,
      Salary,
      CommuteDistance
  FROM (
      SELECT
          employeeId,
          Salary,
          CommuteDistance,
          ROW_NUMBER() OVER (PARTITION BY employeeId ORDER BY Date DESC) AS rn
      FROM employee_current_info
      WHERE Date <= CURRENT_DATE
  ) t
  WHERE rn = 1
  ON DUPLICATE KEY UPDATE
      Salary = VALUES(Salary),
      CommuteDistance = VALUES(CommuteDistance);
  

• 索引优化：在原始表employee_changes的employeeId和Date字段上创建联合索引，加速窗口函数的分区和排序操作：

  CREATE INDEX idx_employee_date ON employee_changes(employeeId, Date);
  

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