Google Big Query中按时间戳精准查询资产5分钟级价格的问题

碰到这种加密货币价格数据的时间对齐和缺失值问题太常见了，我来给你几个实用的BigQuery解决方案！核心思路是先补全所有需要的5分钟时间戳，再用合适的方法填充缺失的价格值，完美适配VEN、ICX这类有数据间隙的资产。

1. 先生成连续的5分钟时间序列 #

首先得确保我们要的每个5分钟时间点都存在，不会因为原始数据缺失而漏掉。用GENERATE_TIMESTAMP_ARRAY就能生成指定时间范围内的连续5分钟时间戳：

WITH time_series AS (
  SELECT 
    timestamp_trunc(ts, MINUTE, 5) AS target_ts
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
      TIMESTAMP('2024-01-01 00:00:00'), -- 起始时间
      TIMESTAMP('2024-01-02 00:00:00'), -- 结束时间
      INTERVAL 5 MINUTE
    )) AS ts
),

2. 关联原始数据并填充缺失值 #

把上面的时间序列和你的原始价格表左连接，这样缺失的时间点就会显示为NULL，接下来用三种常用方法填充空值：

方法一：前向填充（最常用，用上一个非空价格填充） #

适合加密货币价格不会突然跳变的场景，用LAST_VALUE窗口函数实现：

raw_prices AS (
  SELECT
    asset_symbol,
    timestamp_trunc(price_timestamp, MINUTE, 5) AS aligned_ts,
    price
  FROM `your-project.your-dataset.crypto_prices`
  WHERE asset_symbol IN ('VEN', 'ICX') -- 针对特定资产过滤
)
SELECT
  ts.target_ts,
  p.asset_symbol,
  LAST_VALUE(p.price IGNORE NULLS) OVER (
    PARTITION BY p.asset_symbol 
    ORDER BY ts.target_ts 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS filled_price
FROM time_series ts
LEFT JOIN raw_prices p ON ts.target_ts = p.aligned_ts
ORDER BY asset_symbol, target_ts;

方法二：线性插值（适合需要平滑价格曲线的场景） #

如果需要更精准的插值，用LAG和LEAD获取前后非空值，计算线性填充值：

WITH interpolated AS (
  SELECT
    target_ts,
    asset_symbol,
    price,
    LAG(price IGNORE NULLS) OVER (PARTITION BY asset_symbol ORDER BY target_ts) AS prev_price,
    LAG(target_ts IGNORE NULLS) OVER (PARTITION BY asset_symbol ORDER BY target_ts) AS prev_ts,
    LEAD(price IGNORE NULLS) OVER (PARTITION BY asset_symbol ORDER BY target_ts) AS next_price,
    LEAD(target_ts IGNORE NULLS) OVER (PARTITION BY asset_symbol ORDER BY target_ts) AS next_ts
  FROM time_series ts
  LEFT JOIN raw_prices p ON ts.target_ts = p.aligned_ts
)
SELECT
  target_ts,
  asset_symbol,
  CASE
    WHEN price IS NOT NULL THEN price
    ELSE prev_price + (next_price - prev_price) * 
         TIMESTAMP_DIFF(target_ts, prev_ts, SECOND) / 
         TIMESTAMP_DIFF(next_ts, prev_ts, SECOND)
  END AS interpolated_price
FROM interpolated
ORDER BY asset_symbol, target_ts;

方法三：取最近的非空价格（适合数据间隔不规则的情况） #

如果某个时间点前后都有数据，取距离最近的那个价格：

WITH nearest_prices AS (
  SELECT
    ts.target_ts,
    p.asset_symbol,
    p.price,
    ABS(TIMESTAMP_DIFF(ts.target_ts, p.price_timestamp, SECOND)) AS time_diff,
    ROW_NUMBER() OVER (
      PARTITION BY ts.target_ts, p.asset_symbol 
      ORDER BY ABS(TIMESTAMP_DIFF(ts.target_ts, p.price_timestamp, SECOND))
    ) AS rn
  FROM time_series ts
  LEFT JOIN `your-project.your-dataset.crypto_prices` p 
    ON p.asset_symbol IN ('VEN', 'ICX')
    -- 限制只取目标时间戳前后10分钟内的价格，避免取到太远的数据
    AND p.price_timestamp BETWEEN ts.target_ts - INTERVAL 10 MINUTE AND ts.target_ts + INTERVAL 10 MINUTE
)
SELECT
  target_ts,
  asset_symbol,
  price AS nearest_price
FROM nearest_prices
WHERE rn = 1
ORDER BY asset_symbol, target_ts;

小提醒 #

• 记得调整GENERATE_TIMESTAMP_ARRAY里的起始/结束时间，匹配你的实际查询需求；
• 原始秒级数据用timestamp_trunc对齐到5分钟起点，确保时间戳完全匹配；
• 窗口函数里的IGNORE NULLS是关键，能跳过空值直接取最近的有效价格。

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