Big Query处理分区有序数据时出现负时间计算结果求助

问题背景 #

处理40GB（4年）的分区数据（分区键为rated_at），通过SELF JOIN和ROW_NUMBER()生成序列号来计算每条记录间的时间间隔，但运行后约1%的记录（11K条左右）time_studied为负数。每次重新运行，出现负数的记录块都不同，单独查询这些负数记录却能得到正确的正数结果，推测是BigQuery的shuffle机制导致序列号丢失了原有排序。

问题根源 #

1. 全局ROW_NUMBER()逻辑错误：原SQL中ROW_NUMBER()是全局排序，没有按user_id, pack_id分区，导致seqnum会跨用户或跨pack生成。自连接时t.seqnum = tprev.seqnum +1可能把不同用户/pack的记录关联在一起，时间差自然可能为负。
2. CTE中的ORDER BY无效：BigQuery的CTE不保证输出顺序，原CTE末尾的ORDER BY不会影响后续的窗口函数计算，seqnum的排序基础没有保障。
3. 自连接效率低且易出错：全局自连接在分布式环境下容易因为shuffle打乱顺序，导致关联错误。

解决方案 #

改用LAG()窗口函数替代自连接和全局ROW_NUMBER()，在user_id, pack_id分组内直接获取上一条记录的时间戳，计算时间差后求和。这种方式既避免了分布式shuffle带来的顺序问题，又提升了查询效率。

修正后的SQL：

WITH calculated_time AS (
  SELECT
    user_id,
    pack_id,
    -- 计算当前记录与同组上一条记录的时间差（秒）
    DATETIME_DIFF(
      DATETIME(TIMESTAMP(rated_at), "America/New_York"),
      LAG(DATETIME(TIMESTAMP(rated_at), "America/New_York")) OVER (
        PARTITION BY user_id, pack_id 
        ORDER BY rated_at
      ),
      SECOND
    ) AS time_diff
  FROM dp.table
)

SELECT
  user_id,
  pack_id,
  SUM(time_diff) AS time_studied
FROM calculated_time
WHERE time_diff IS NOT NULL -- 过滤每组第一条无前置记录的数据
GROUP BY user_id, pack_id

关键改动说明 #

• PARTITION BY分组：确保只在同一用户、同一pack的范围内计算相邻记录的时间差，彻底避免跨组关联导致的负数问题。
• LAG()窗口函数：在每个分组内按rated_at排序，直接获取上一条记录的时间戳，无需自连接，分布式执行时能稳定保留组内顺序。
• 简化逻辑：去掉了冗余的全局ROW_NUMBER()和自连接，减少了shuffle操作，查询性能更优，结果也更稳定。

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