在BigQuery中补全货币表缺失日期的最新可用汇率 - SQL实现

核心思路 #

通过窗口函数或关联筛选的方式，为每个事实表的日期-货币组合匹配最近的可用汇率，解决货币表日期缺失的问题。以下提供两种可直接落地的实现方式：

方式一：使用LAST_VALUE窗口函数 #

假设表结构：

• 事实表fact_table：含transaction_date（交易日期）、currency_code（货币代码）、amount（交易金额）
• 货币表currency_rates：含rate_date（汇率日期）、currency_code（货币代码）、exchange_rate（汇率）

实现SQL #

WITH date_currency_pairs AS (
  -- 获取事实表中所有唯一的日期-货币组合
  SELECT DISTINCT transaction_date, currency_code
  FROM fact_table
),
rate_candidates AS (
  -- 为每个组合匹配所有早于等于交易日期的汇率记录
  SELECT
    dcp.transaction_date,
    dcp.currency_code,
    cr.rate_date,
    cr.exchange_rate
  FROM date_currency_pairs dcp
  LEFT JOIN currency_rates cr
    ON dcp.currency_code = cr.currency_code
    AND cr.rate_date <= dcp.transaction_date
)
-- 取每个组合下最新的非空汇率
SELECT
  transaction_date,
  currency_code,
  LAST_VALUE(exchange_rate IGNORE NULLS) OVER (
    PARTITION BY currency_code, transaction_date
    ORDER BY rate_date ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS latest_exchange_rate
FROM rate_candidates

方式二：通过MAX(rate_date)筛选最新汇率 #

这种方式逻辑更直观，先定位每个组合对应的最新汇率日期，再关联取汇率：

实现SQL #

WITH latest_rate_date_map AS (
  -- 找到每个交易日期-货币对应的最新汇率日期
  SELECT
    ft.transaction_date,
    ft.currency_code,
    MAX(cr.rate_date) AS latest_rate_date
  FROM fact_table ft
  LEFT JOIN currency_rates cr
    ON ft.currency_code = cr.currency_code
    AND cr.rate_date <= ft.transaction_date
  GROUP BY ft.transaction_date, ft.currency_code
)
-- 关联货币表获取对应汇率
SELECT
  lrdm.transaction_date,
  lrdm.currency_code,
  cr.exchange_rate AS latest_exchange_rate
FROM latest_rate_date_map lrdm
LEFT JOIN currency_rates cr
  ON lrdm.currency_code = cr.currency_code
  AND lrdm.latest_rate_date = cr.rate_date

关键注意点 #

• 若某货币在交易日期前无任何汇率记录，结果会返回NULL，可通过COALESCE()设置业务默认值
• 确保两张表的日期字段均为DATE类型，避免隐式转换导致的匹配错误
• 数据量较大时，建议为currency_rates表的currency_code和rate_date字段建立联合索引优化查询效率

内容的提问来源于stack exchange，提问作者LUIZ CARLOS DA SILVA FERNANDES