BigQuery中"薛定谔行"问题：为何ROW_NUMBER()不适合作唯一标识

问题背景 #

我们在重构内部复杂营销预算分配逻辑的查询时，遇到了诡异的「薛定谔行」异常：使用ROW_NUMBER() OVER()生成的行唯一标识，会导致同一逻辑行看似同时满足匹配与不匹配条件，最终出现german_spend + non_german_spend > total_spend的矛盾计算结果。目前已通过将行标识替换为业务键的哈希值解决问题，但需明确根因。

异常现象汇总 #

• 用GENERATE_UUID()生成标识时，几乎不会出现匹配结果
• 将中间结果spend_buckets写入物理表后，异常完全消失
• 即使是小体量测试数据，也会触发行不匹配问题
• 依赖RAND()生成测试数据的复现查询，每次执行结果不一致

根因分析 #

核心问题源于SQL查询执行的非确定性与优化器行为：

1. 无稳定排序的ROW_NUMBER()是弱确定性函数：若ROW_NUMBER() OVER()未指定明确且稳定的ORDER BY子句，数据库会基于执行时的物理数据顺序生成行号。当查询中多次引用同一CTE/子查询时，优化器可能选择重复扫描数据集而非物化，每次扫描的物理顺序可能变化，导致同一逻辑行被分配不同的行号。
2. 重复扫描的标识不一致引发矛盾：比如在计算german_spend和non_german_spend时，两次扫描spend_buckets生成的行号不同，导致原本属于同一行的支出被分别计入匹配与不匹配的分组，最终出现重复统计，引发german_spend + non_german_spend > total_spend的矛盾。
3. 其他现象的对应解释：
   • GENERATE_UUID()是强非确定性函数，每次调用生成完全独立的值，因此几乎无匹配；
   • 将spend_buckets写入物理表后，数据被固化，标识不会随扫描重新计算，异常自然消失。

解决方案验证 #

• 替换为业务键哈希值：业务键是稳定的业务唯一标识，基于其生成的哈希值固定不变，无论查询执行多少次，同一逻辑行的标识始终一致，从根源避免了非确定性问题。
• 物化中间结果：将CTE/子查询结果写入物理表，强制固定中间数据的标识，消除重复扫描时的重新计算行为。

复现查询 #

-- 复现查询（依赖RAND()生成测试数据，每次执行结果不同）
WITH spend_buckets AS (
    SELECT
        campaign_id,
        country,
        spend,
        ROW_NUMBER() OVER() AS row_id -- 引发异常的行标识
    FROM (
        SELECT
            CONCAT('camp_', FLOOR(RAND()*10)) AS campaign_id,
            CASE WHEN RAND() < 0.3 THEN 'DE' ELSE 'OTHER' END AS country,
            ROUND(RAND()*1000, 2) AS spend
        FROM GENERATE_SERIES(1, 100)
    ) AS raw_data
),
german_spend AS (
    SELECT campaign_id, SUM(spend) AS sum_spend
    FROM spend_buckets
    WHERE country = 'DE'
    GROUP BY campaign_id
),
non_german_spend AS (
    SELECT campaign_id, SUM(spend) AS sum_spend
    FROM spend_buckets
    WHERE country != 'DE'
    GROUP BY campaign_id
),
total_spend AS (
    SELECT campaign_id, SUM(spend) AS sum_spend
    FROM spend_buckets
    GROUP BY campaign_id
)
SELECT
    ts.campaign_id,
    COALESCE(gs.sum_spend, 0) AS german_spend,
    COALESCE(ng.sum_spend, 0) AS non_german_spend,
    ts.sum_spend AS total_spend,
    (COALESCE(gs.sum_spend, 0) + COALESCE(ng.sum_spend, 0)) - ts.sum_spend AS discrepancy
FROM total_spend ts
LEFT JOIN german_spend gs ON ts.campaign_id = gs.campaign_id
LEFT JOIN non_german_spend ng ON ts.campaign_id = ng.campaign_id
WHERE (COALESCE(gs.sum_spend, 0) + COALESCE(ng.sum_spend, 0)) != ts.sum_spend;

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