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

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

CodePudding user response：

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