如何在Snowflake/DuckDB/CedarDB中实现基于最近时间的ASOF Join？

你的原语句仅能匹配销售时间之前的最近温度记录，要实现绝对时间距离最近（包含之前或之后的最近记录），以下是三个数据库的具体实现方案：

Snowflake #

Snowflake的ASOF JOIN仅支持单向条件匹配，要获取绝对最近记录，需先筛选时间范围内的候选温度数据，再计算时间差取最小项：

WITH ranked_temperatures AS (
  SELECT
    s.brand,
    s.quantity,
    t.recorded_temperature,
    s.amusement_park_id,
    ABS(DATEDIFF(SECOND, s.load_datetime, t.recorded_datetime)) AS time_diff,
    ROW_NUMBER() OVER (PARTITION BY s.load_datetime, s.amusement_park_id ORDER BY time_diff) AS rn
  FROM ice_cream_sales s
  JOIN temperature t ON s.amusement_park_id = t.amusement_park_id
  -- 可选：缩小时间范围提升性能，比如仅匹配前后1小时内的温度
  WHERE t.recorded_datetime BETWEEN DATEADD(HOUR, -1, s.load_datetime) AND DATEADD(HOUR, 1, s.load_datetime)
)
SELECT
  brand,
  quantity,
  recorded_temperature,
  amusement_park_id
FROM ranked_temperatures
WHERE rn = 1;

DuckDB #

DuckDB支持通过CROSS JOIN LATERAL直接筛选单条最近记录，写法更简洁：

SELECT
  s.brand,
  s.quantity,
  t.recorded_temperature,
  s.amusement_park_id
FROM ice_cream_sales s
CROSS JOIN LATERAL (
  SELECT *
  FROM temperature t
  WHERE t.amusement_park_id = s.amusement_park_id
  -- 可选：限制时间范围
  ORDER BY ABS(s.load_datetime - t.recorded_datetime)
  LIMIT 1
) t;

CedarDB #

CedarDB同样需要结合窗口函数和自连接，先获取温度记录的前后时间节点，再匹配销售时间的最近项：

WITH temp_with_neighbors AS (
  SELECT
    t.*,
    LEAD(recorded_datetime) OVER (PARTITION BY amusement_park_id ORDER BY recorded_datetime) AS next_datetime,
    LAG(recorded_datetime) OVER (PARTITION BY amusement_park_id ORDER BY recorded_datetime) AS prev_datetime
  FROM temperature t
),
sales_with_matches AS (
  SELECT
    s.brand,
    s.quantity,
    s.amusement_park_id,
    t.recorded_temperature,
    ABS(s.load_datetime - t.recorded_datetime) AS time_diff
  FROM ice_cream_sales s
  JOIN temp_with_neighbors t ON s.amusement_park_id = t.amusement_park_id
  WHERE s.load_datetime BETWEEN COALESCE(t.prev_datetime, t.recorded_datetime) AND COALESCE(t.next_datetime, t.recorded_datetime)
),
ranked_matches AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY s.load_datetime, s.amusement_park_id ORDER BY time_diff) AS rn
  FROM sales_with_matches
)
SELECT
  brand,
  quantity,
  recorded_temperature,
  amusement_park_id
FROM ranked_matches
WHERE rn = 1;

核心思路说明 #

所有方案的核心逻辑一致：

• 关联同乐园下的销售与温度数据
• 计算每条温度记录与销售时间的时间差绝对值
• 按时间差排序，取最小的那一条记录

添加时间范围过滤（如前后1小时）可大幅减少计算量，提升查询性能。

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