BigQuery中按ID、版本取最高测试分数并转宽表的实现方法

原有写法问题说明 #

你之前编写的CTE+多表交叉查询存在两个核心问题：

• 各版本临时表仅提取了分数字段，没有保留员工ID关联键，最后直接做笛卡尔交叉连接会导致员工和分数错配，无法得到正确结果
• 手动枚举版本做临时表再join的方案，在版本数量多的时候维护成本极高，且如果员工缺失某版本成绩，join逻辑很容易漏数

最优实现方案 #

BigQuery原生提供PIVOT算子专门处理行转列场景，无需手写多表关联，代码简洁且性能更好，分两种场景使用：

1. 版本列表固定（推荐用于创建视图） #

如果你的20个测试版本是稳定的、不会频繁增减，直接用静态PIVOT写法，写完可以直接基于该查询创建视图：

CREATE OR REPLACE VIEW `project.dataset.emp_max_score_wide` AS
WITH max_score_base AS (
  SELECT
    id,
    version,
    MAX(score) AS score
  FROM `project.dataset.table`
  WHERE
    type = 'assessment_test'
    AND id IS NOT NULL
    AND version IS NOT NULL
    AND score IS NOT NULL
  GROUP BY id, version
)
SELECT * FROM max_score_base
PIVOT(
  ANY_VALUE(score) -- 基础CTE已经聚合到id+version粒度，用ANY_VALUE/MAX都可
  FOR version IN (
    'version_a' AS version_a,
    'version_b' AS version_b,
    'version_c' AS version_c
    -- 按上述格式补全剩余版本即可
  )
)

该写法会自动以员工ID为行键，每个版本生成独立列存储对应最高分，员工缺失某版本成绩时自动填充NULL，不会漏数。

2. 版本动态变化（无需手动枚举版本） #

如果测试版本会持续新增，不想每次更新版本都修改SQL，可以用BigQuery的动态SQL自动识别所有存在的版本生成列：

EXECUTE IMMEDIATE FORMAT("""
WITH max_score_base AS (
  SELECT
    id,
    version,
    MAX(score) AS score
  FROM `project.dataset.table`
  WHERE
    type = 'assessment_test'
    AND id IS NOT NULL
    AND version IS NOT NULL
    AND score IS NOT NULL
  GROUP BY id, version
)
SELECT * FROM max_score_base
PIVOT(
  ANY_VALUE(score)
  FOR version IN (%s)
)
ORDER BY id
""", (
  -- 子查询自动拉取所有去重后的版本名，生成PIVOT需要的列定义
  SELECT STRING_AGG(DISTINCT CONCAT("'", version, "' AS ", REPLACE(version, '.', '_')), ', ')
  FROM `project.dataset.table`
  WHERE
    type = 'assessment_test'
    AND version IS NOT NULL
));

注意：动态SQL生成的查询列数会随版本数变化，无法直接用于创建固定Schema的视图，适合临时查询、或者写入到固定结构的表中使用。

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