Snowflake SQL关联查询时如何匹配同ID的最新历史记录

Snowflake SQL实现方案 #

方案1：ASOF JOIN（推荐） #

Snowflake原生支持ASOF JOIN，专门适配时间维度的最近值匹配场景，代码简洁且执行效率远高于普通关联方案：

SELECT
  t2.EVENT,
  t2.ID,
  t2.TIMESTAMP,
  t1."GROUP"
FROM TABLE2 t2
ASOF LEFT JOIN TABLE1 t1
  ON t1.ID = t2.ID
  AND t1.TIMESTAMP <= t2.TIMESTAMP
ORDER BY t2.ID, t2.TIMESTAMP;

方案2：窗口函数通用实现 #

如果你对ASOF JOIN语法不熟悉，也可以用标准窗口函数实现，兼容性更强：

WITH all_matches AS (
  SELECT
    t2.EVENT,
    t2.ID,
    t2.TIMESTAMP AS EVENT_TIMESTAMP,
    t1."GROUP",
    ROW_NUMBER() OVER (
      PARTITION BY t2.EVENT, t2.ID, t2.TIMESTAMP 
      ORDER BY t1.TIMESTAMP DESC
    ) AS rn
  FROM TABLE2 t2
  LEFT JOIN TABLE1 t1
    ON t1.ID = t2.ID
    AND t1.TIMESTAMP < t2.TIMESTAMP
)
-- 取每个事件匹配到的最新GROUP记录
SELECT EVENT, ID, EVENT_TIMESTAMP AS TIMESTAMP, "GROUP"
FROM all_matches
WHERE rn = 1
-- 补充完全没有匹配到前置记录的事件
UNION ALL
SELECT EVENT, ID, TIMESTAMP, NULL AS "GROUP"
FROM TABLE2 t2
WHERE NOT EXISTS (
  SELECT 1 FROM TABLE1 t1 
  WHERE t1.ID = t2.ID AND t1.TIMESTAMP < t2.TIMESTAMP
)
ORDER BY ID, TIMESTAMP;

注意：GROUP 是SQL保留关键字，查询中引用该字段时需要用双引号包裹，避免语法报错。
两种方案均可以输出你要求的预期结果，优先推荐使用ASOF JOIN方案。

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