我想要一个BigQuery查询代码来连接和计算两个表。

huangapple go评论73阅读模式
英文:

I'd like to have a bigquery query code to join and calculate 2 tables

问题

I have 2 tables in Bigquery and I'd like to merge/join them and doing some calculation.
Here are the tables:

Table A

ID Name Score
12-2112 John 23844
12-2310 Matthew 21881
13-6205 Matthew 16721
12-1710 Sonia 13344
12-1710 Sonia 8187

Table B

ID Name Games Score
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karate 1861
12-2310 Matthew Judo 2081
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score on Table A is the total score of games of Table B. However, not all games on Table B is identified.
So, the final table should look like:

Combined

ID Name Games Score
12-2112 John null 6446
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 4156
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 838
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 586
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 707
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

I've tried all the joining statements possibles (I know) but the output is not as desired.

The best I did find was a union all (or distinct) with this query:

select
ID,
Name,
null as Games,
Score
from Table A
Union ALL
Select
ID,
Name,
Games,
Score
from Table A

and here is the output:

ID Name Games Score
12-2112 John null 23844
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 21881
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 16721
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 13344
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 8187
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score is not the total of table A - Sum (of Games) score of Table B as expected.

Could you please help me out?

英文:

I have 2 tables in Bigquery and I'd like to merge/join them and doing some calculation.
Here are the tables :

Table A

ID Name Score
12-2112 John 23844
12-2310 Matthew 21881
13-6205 Matthew 16721
12-1710 Sonia 13344
12-1710 Sonia 8187

Table B

ID Name Games Score
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karate 1861
12-2310 Matthew Judo 2081
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score on Table A is the total score of games of Table B. However, not all games on Table B is identify.
So, the final table should look like :

Combined

ID Name Games Score
12-2112 John null 6446
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 4156
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 838
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 586
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 707
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

I've tried all the joining statements possibles (I know) but the output is not as desired.

The best I did found was a union all (or distinct) with this query :

select
ID,
Name,
null as Games,
Score
from Table A
Union ALL
Select
ID,
Name,
Games,
Score
from Table A

and here is the output :

ID Name Games Score
12-2112 John null 23844
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 21881
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 16721
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 13344
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 8187
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score is not the total of table A - Sum (of Games) score of Table B as expected.

Could you please help me out ?

Thanks

I'm trying to joing query statement between table A and table B and have the score of table A to be the difference from the sum (table A) - sum (table B) for a null "games"

I've tried the following query :

select
ID,
Name,
null as Games,
Score
from Table A
Union ALL
Select
ID,
Name,
Games,
Score
from Table A

答案1

得分: 1

考虑以下简单方法:

select id, name, games, 
  if(not games is null, score, 2 * score - sum(score) over(partition by id, name)) as score
from (
  select * from tableB union all
  select id, name, null, score from tableA
)

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

我想要一个BigQuery查询代码来连接和计算两个表。

英文:

Consider below simple approach

select id, name, games, 
  if(not games is null, score, 2 * score - sum(score) over(partition by id, name)) as score
from (
  select * from tableB union all
  select id, name, null, score from tableA
)

if applied to sample data in your question - output is

我想要一个BigQuery查询代码来连接和计算两个表。

huangapple
  • 本文由 发表于 2023年1月6日 14:29:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75027677.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定