代码之家 › 专栏 › 技术社区 › Danne Paredes

如何:对于每个唯一id,对于每个唯一版本,获取最佳分数并将其组织到一个表中

google-bigquery sql

Danne Paredes · 技术社区 · 2 年前

在开始之前,我想说,虽然我对Bigquery表和sql语句有基本的了解,但我对一般情况下使用Bigquery表和sql语句还是相当陌生的。

我试图从一个查询中得出一个新的观点,该查询获取每个员工每个版本的所有最佳测试分数:

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\u 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

示例数据:

身份证件	版本	分数
1.	一
1.	b	93
1.	c	92
2.	一
2.	b	99
2.	c	78
3.	一	95
3.	b	83
3.	c	89
4.	一
4.	b	90
4.		86
5.	一	82
5.	b	78
5.	c	98
1.	一
1.	b	97
1.	c	77
2.	一	100
2.	b	96
2.	c	85
3.	一	83
3.		87
3.	c
4.	一	84
4.	b	80
4.		77
5.	一	95
5.	b	77

预期输出:

身份证件	a分数	b分数	c分数
1.	88	97	92
2.	100	99	85
3.	95	87	96
4.	90	90	86
5.	95	78	98

提前感谢,如果有任何澄清问题,请随时提问

1 回复 | 直到 2 年前

Mikhail Berlyant 2 年前

使用以下方法

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
)

推荐文章

Hal · 如何在bigquery中从长数据创建列表?

2 年前

Danne Paredes · 如何:对于每个唯一id,对于每个唯一版本,获取最佳分数并将其组织到一个表中

2 年前

Justin · 从字符串中提取日期的大查询

2 年前

Evans Gunawan · 如何使用sql bigquery中的前一列值将数据重塑为新列?

2 年前

Fernando Gomes · 这是本月的第一周。一周从周一开始,周日结束

2 年前

Bill LaLonde · 此错误意味着什么:从位置0开始的行中缺少列索引8所需的列值

2 年前

vinay lokwani · 在大查询中的嵌套字段中插入用于输入数据的语句

2 年前

anat · 在BigQuery中的特定字符串后查找n个字符

2 年前

SaravanaKumar Prithiviraj · 如何在bigquery资源中检测权限删除

2 年前

Anil · 在Google Big\u查询中显示每月的第一天

6 年前

身份证件	版本	分数
1.	一
1.	b	93
1.	c	92
2.	一
2.	b	99
2.	c	78
3.	一	95
3.	b	83
3.	c	89
4.	一
4.	b	90
4.		86
5.	一	82
5.	b	78
5.	c	98
1.	一
1.	b	97
1.	c	77
2.	一	100
2.	b	96
2.	c	85
3.	一	83
3.		87
3.	c
4.	一	84
4.	b	80
4.		77
5.	一	95
5.	b	77

身份证件	版本	分数
1.	一
1.	b	93
1.	c	92
2.	一
2.	b	99
2.	c	78
3.	一	95
3.	b	83
3.	c	89
4.	一
4.	b	90
4.		86
5.	一	82
5.	b	78
5.	c	98
1.	一
1.	b	97
1.	c	77
2.	一	100
2.	b	96
2.	c	85
3.	一	83
3.		87
3.	c
4.	一	84
4.	b	80
4.		77
5.	一	95
5.	b	77

身份证件	版本	分数
1.	一
1.	b	93
1.	c	92
2.	一
2.	b	99
2.	c	78
3.	一	95
3.	b	83
3.	c	89
4.	一
4.	b	90
4.		86
5.	一	82
5.	b	78
5.	c	98
1.	一
1.	b	97
1.	c	77
2.	一	100
2.	b	96
2.	c	85
3.	一	83
3.		87
3.	c
4.	一	84
4.	b	80
4.		77
5.	一	95
5.	b	77