sql - 如何对每个唯一版本: For each unique id,，抓取最好的分数整理成表格

Question

只是想先说明一下，虽然我确实有基本的了解，但我对一般使用 Bigquery 表和 sql 语句还是相当陌生。

我正在尝试从一个查询中创建一个新 View ，该查询获取每个员工每个版本的所有最佳测试分数:

select emp_id,version,max(score) as score from `project.dataset.table` where type = 'assessment_test' group by version,emp_id order by emp_id

我想获取该查询的结果，并创建一个由员工 ID 组成的新表，其中每个版本的列都有一列，该行 emp_id。我知道我可以通过包含“where version = a”、“where version = b”等来手动为每个版本创建一个表......然后在最后加入所有表，但这似乎不是就像最优雅的解决方案一样，总共有大约 20 个不同的版本。

有没有办法以编程方式为每个唯一版本创建一个列，或者至少将我的初始查询用作子查询并仅引用它，如下所示:

with a as (
  select id,version,max(score) as score 
  from `project.dataset.table` 
  where type = 'assessment_test' and version is not null and score is not null and id is not null 
  group by version,id 
  order by id),

version_a as (select score from a where version = 'version_a')
version_b as (select score from a where version = 'version_b')
version_c as (select score from a where version = 'version_c')

select 
  a.id as id,
  version_a.score as version_a,
  version_b.score as version_b,
  version_c.score as version_c
from 
a,
version_a, 
version_b,
version_c

Example Picture: left table is example data, right table is expected output

示例数据:

<表类="s-表"><头>id版本得分<正文>1一个881b931c922一个892b992c783一个953b833c894一个904b904c865一个825b785c981一个791b971c772一个1002b962c853一个833b873c964一个844b804c775一个955b77

预期输出:

<表类="s-表"><头>id分数b 得分c 得分<正文>188979221009985395879649090865957898

提前致谢，如有任何需要澄清的问题，请随时提出

Answer 1

使用下面的方法

select * from your_table
pivot (max(score) score for version in ('a', 'b', 'c'))      

如果应用于您问题中的示例数据 - 输出为

如果事先不知道版本 - 使用下面

execute immediate (select '''
select * from your_table
pivot (max(score) score for version in (''' || string_agg(distinct "'" || version || "'") || "))"
from your_table
)