BigQuery自连接计算向量距离时同文档列值不一致问题

• BigQuery 默认不会持久化存储公共表表达式（CTE）的计算结果，每次在查询中引用同一个CTE时，都会重新执行该CTE对应的全部计算逻辑。你的查询里rows_with_weights被左表l和右表r各引用了一次，相当于触发了两次完整的上游计算。
• rand()是非确定性函数，每次调用都会生成独立的随机值。两次计算exploded CTE时，每个维度对应的weight都会重新随机生成，最终导致同一个document_id在左表和右表的向量结果完全不同。

核心思路是让随机向量仅生成一次，后续所有比对逻辑都复用同一份计算结果，有两种常用实现方式：

方案1：临时表物化结果（最稳妥） #

先将生成好的向量存入临时表，再做自连接比对：

-- 生成固定的随机向量并持久化到临时表
CREATE TEMP TABLE rows_with_weights AS
WITH input_data AS (
  SELECT
    document_id
  FROM UNNEST(["doc1", "doc2"]) AS document_id
),
exploded AS (
  SELECT
    document_id,
    STRUCT(dimension_id, rand() AS weight) AS weight
  FROM
    input_data CROSS JOIN UNNEST(GENERATE_ARRAY(1, 3)) AS dimension_id
)
SELECT 
  document_id,
  ARRAY_AGG(weight) as vector
FROM 
  exploded 
GROUP BY 
  document_id;

-- 基于固定的向量结果做两两比对
SELECT 
  l.document_id as l_doc_id,
  r.document_id as r_doc_id,
  l.vector as l_vectors,
  r.vector as r_vectors
FROM 
  rows_with_weights AS l 
CROSS JOIN rows_with_weights AS r 
WHERE l.document_id <= r.document_id;

方案2：CTE 物化提示 #

给需要复用的CTE增加物化查询提示，强制BigQuery仅计算一次该CTE：

WITH input_data AS (
  SELECT
    document_id
  FROM UNNEST(["doc1", "doc2"]) AS document_id
),
exploded AS (
  SELECT
    document_id,
    STRUCT(dimension_id, rand() AS weight) AS weight
  FROM
    input_data CROSS JOIN UNNEST(GENERATE_ARRAY(1, 3)) AS dimension_id
),
-- 增加materialize提示，强制仅计算一次
rows_with_weights AS /*+ materialize */ (
  SELECT 
    document_id,
    ARRAY_AGG(weight) as vector
  FROM 
    exploded 
  GROUP BY 
    document_id
)
SELECT 
  l.document_id as l_doc_id,
  r.document_id as r_doc_id,
  l.vector as l_vectors,
  r.vector as r_vectors
FROM 
  rows_with_weights AS l 
CROSS JOIN rows_with_weights AS r 
WHERE l.document_id <= r.document_id;

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