基于S&P数据集：USD转EUR时如何填充缺失日期的汇率值？

我通过S&P Global Delta Share获取各类船舶燃油价格，数据里部分以USD计价、部分以EUR计价，需要统一转换为EUR。数据集内有标记为EUUSD00的汇率数据，我写了一段SQL生成VALUE_EURO列，但部分日期没有对应的EUUSD00记录，导致VALUE_EURO返回NULL。希望这些缺失日期能使用该日期之前最近的可用汇率计算，比如某日期无汇率时用2024-04-30的汇率。

原SQL代码 #

select
  symbol,
  DESCRIPTION,
  ASSESSDATE,
  MODIFIEDDATETIME,
  bate,
  VALUE,
  CURRENCY,
  CASE
    WHEN CURRENCY = 'EUR' THEN VALUE
    WHEN CURRENCY = 'USD' THEN VALUE / (
      SELECT
        AVG(VALUE)
      FROM
        `sp_marketdata`.`mdv2`.`pricedata`
      WHERE
        symbol = 'EUUSD00'
        AND ASSESSDATE = outerQuery.ASSESSDATE
    )
  END AS VALUE_EURO
from
  `sp_marketdata`.`mdv2`.`pricedata` outerQuery
where
  symbol in (
    'AAYWT00',
    'AAJUS00',
    'AAWGH00',
    'HVNWC00',
    'GTFTM01',
    'ENXEM01',
    'EUUSD00'
  )
  and ASSESSDATE between '2024-05-01'
  and '2024-06-28'
  and BATE in ('c', 'u')
order by
  ASSESSDATE DESC

修改后的解决方案 #

核心思路是先预计算每个日期对应的最近可用汇率，再将其与原数据关联计算欧元计价。以下是适配BigQuery的SQL代码：

WITH exchange_rates AS (
  -- 计算每天EUUSD00的平均汇率，并用LAST_VALUE向前填充缺失值
  SELECT
    ASSESSDATE,
    AVG(VALUE) AS avg_eu_usd,
    LAST_VALUE(AVG(VALUE) IGNORE NULLS) OVER (ORDER BY ASSESSDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_eu_usd
  FROM `sp_marketdata`.`mdv2`.`pricedata`
  WHERE symbol = 'EUUSD00' AND BATE IN ('c', 'u')
  GROUP BY ASSESSDATE
),
date_range AS (
  -- 生成查询区间内的所有日期，确保无遗漏
  SELECT DATE_ADD('2024-05-01', INTERVAL x DAY) AS ASSESSDATE
  FROM UNNEST(GENERATE_ARRAY(0, DATE_DIFF('2024-06-28', '2024-05-01', DAY))) x
),
latest_rates AS (
  -- 为每个日期匹配最近的可用汇率
  SELECT
    dr.ASSESSDATE,
    (
      SELECT MAX(latest_eu_usd) 
      FROM exchange_rates er 
      WHERE er.ASSESSDATE <= dr.ASSESSDATE
    ) AS exchange_rate
  FROM date_range dr
)
SELECT
  p.symbol,
  p.DESCRIPTION,
  p.ASSESSDATE,
  p.MODIFIEDDATETIME,
  p.bate,
  p.VALUE,
  p.CURRENCY,
  CASE
    WHEN p.CURRENCY = 'EUR' THEN p.VALUE
    WHEN p.CURRENCY = 'USD' THEN p.VALUE / lr.exchange_rate
  END AS VALUE_EURO
FROM `sp_marketdata`.`mdv2`.`pricedata` p
JOIN latest_rates lr ON p.ASSESSDATE = lr.ASSESSDATE
WHERE
  p.symbol IN ('AAYWT00', 'AAJUS00', 'AAWGH00', 'HVNWC00', 'GTFTM01', 'ENXEM01', 'EUUSD00')
  AND p.ASSESSDATE BETWEEN '2024-05-01' AND '2024-06-28'
  AND p.BATE IN ('c', 'u')
ORDER BY p.ASSESSDATE DESC;

关键说明 #

1. exchange_rates子查询：先计算每天的平均汇率，再用LAST_VALUE(IGNORE NULLS)窗口函数，将之前最近的非空汇率填充到后续缺失的日期中，处理连续多天无汇率的情况。
2. date_range子查询：生成查询区间内的所有日期，确保每个业务日期都能匹配到汇率，避免遗漏。
3. latest_rates子查询：为每个日期找到早于或等于它的最新汇率值，确保缺失日期能获取到最近的可用汇率。

内容的提问来源于stack exchange，提问作者Jaco Tran van der Laan