如何使用BigQuery SQL实现时间序列数据15分钟粒度均匀采样

核心实现代码 #

WITH dedup_raw_data AS (
  -- 原始数据去重：相同时间戳的价格取平均值，可根据业务调整聚合逻辑
  SELECT 
    timestamp,
    AVG(price) AS price
  FROM `your_project.your_dataset.your_table`
  WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-02-01 00:00:00' -- 替换为你的查询时间范围
  GROUP BY timestamp
),
target_anchors AS (
  -- 生成查询范围内所有目标采样锚点：每小时0/15/30/45分
  SELECT anchor_ts
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
    TIMESTAMP_TRUNC('2024-01-01 00:00:00', HOUR), -- 替换为你的查询开始时间
    TIMESTAMP_TRUNC('2024-02-01 00:00:00', HOUR) + INTERVAL 59 MINUTE, -- 替换为你的查询结束时间
    INTERVAL 15 MINUTE
  )) AS anchor_ts
),
nearest_match AS (
  SELECT
    a.anchor_ts,
    r.timestamp AS raw_ts,
    r.price,
    -- 按时间差排序，取每个锚点最近的原始数据
    ROW_NUMBER() OVER(
      PARTITION BY a.anchor_ts 
      ORDER BY ABS(TIMESTAMP_DIFF(r.timestamp, a.anchor_ts, SECOND)) ASC
    ) AS rn
  FROM target_anchors a
  -- 仅关联锚点前后15分钟内的原始数据，大幅缩小计算范围提升性能
  LEFT JOIN dedup_raw_data r
  ON r.timestamp BETWEEN a.anchor_ts - INTERVAL 15 MINUTE AND a.anchor_ts + INTERVAL 15 MINUTE
  QUALIFY rn = 1
)
-- 最终输出：去重避免同一条原始数据被多个锚点匹配
SELECT DISTINCT raw_ts AS timestamp, price
FROM nearest_match
WHERE raw_ts IS NOT NULL
ORDER BY timestamp

可调整的自定义规则 #

• 原始数据去重逻辑：如果同时间戳多值需要取最新价，可以把AVG(price)替换为ARRAY_AGG(price ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)]
• 允许缺值展示：如果某个锚点前后15分钟没有数据需要留空，删除最后一步的WHERE raw_ts IS NOT NULL，输出anchor_ts替代raw_ts即可
• 允许重复采样：如果接受同一条数据对应多个锚点（保证每小时固定4个点），删除最后一步的DISTINCT即可

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