SparkSQL关联变更日志表与事件表匹配对应时间生效状态值方案

方案1：窗口函数预构造状态生效区间（兼容性最好，性能稳定） #

核心思路是先给每条状态变更记录标记失效时间，关联时直接通过时间区间匹配，适用所有支持窗口函数的SQL引擎：

WITH status_with_valid_period AS (
  SELECT
    ID,
    VALUE,
    TIME AS start_time,
    -- 同ID下下一条状态的变更时间作为当前状态的失效时间，无后续状态则设为极大值
    LEAD(TIME, 1, '23:59:59') OVER (PARTITION BY ID ORDER BY TIME) AS end_time
  FROM status_change
)
SELECT
  e.ID,
  e.TIME,
  s.VALUE,
  e.`***`
FROM event_log e
LEFT JOIN status_with_valid_period s
  ON e.ID = s.ID
  AND e.TIME >= s.start_time
  AND e.TIME < s.end_time;

• 若匹配结果和你的预期规则有差异，调整>=/>、</<=的比较符号即可适配

你之前的写法错误原因是仅关联了ID维度，同一个ID下的所有状态记录都会和事件记录匹配，才会出现多匹配的结果，子查询加LIMIT只能限制子查询返回的总条数，无法给每个事件单独做匹配限制。

方案2：LATERAL JOIN 横向关联（写法更简洁，适用支持该语法的引擎） #

如果你的SQL引擎支持LATERAL JOIN（MySQL 8.0+、PostgreSQL、Spark SQL、Hive 2.0+等均支持），可以直接为每条事件匹配最近的状态记录：

SELECT
  e.*,
  s.VALUE
FROM event_log e
LEFT JOIN LATERAL (
  SELECT VALUE
  FROM status_change
  WHERE ID = e.ID AND TIME <= e.TIME
  ORDER BY TIME DESC
  LIMIT 1
) s ON TRUE;

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