How to build an intercalated ranking from two tables using BigQuery-CodePudding

I have two tables in BigQuery with records ordered by a ranking. Given a ratio of integers, I want to be able to join both tables, keeping the order of the ranking and the proportions of the ratio of integers. For example:

Table A

Name	Ranking A
Kevin	1
Jack	2
Kate	3
Randall	4
Beck	5

Table B:

Name	Ranking B
William	1
Laurel	2
Sophie	3
Tess	4
Deja	5
Toby	6
Nick	7

Given a ratio 2:3 where 2 corresponds with Table A, and 3 corresponds with Table B, the expected result would be:

Name	Ranking A	Ranking B	Final Rank
Kevin	1		1
Jack	2		2
William		1	3
Laurel		2	4
Sophie		3	5
Kate	3		6
Randall	4		7
Tess		4	8
Deja		5	9
Toby		6	10
Beck	5		11
Nick		7	12

Any ideas?

CodePudding user response：

You can solve this problem with some math trick here. In both tables you have to compute a running sum and skip 2 (for second table) or 3 (for first table) values according to the ranking value you're currently placing. Basically you're making two gapped running sum, where the gaps will be filled by the other one's ranking values.

SELECT Name,
       SUM(CASE WHEN MOD(RankingA,2) = 1 THEN 4 ELSE 1 END) OVER(ORDER BY RankingA)-3 AS rn
FROM tableA
UNION ALL
SELECT Name,
       SUM(CASE WHEN MOD(RankingB,3) = 1 THEN 3 ELSE 1 END) OVER(ORDER BY RankingB) AS rn
FROM tableB
ORDER BY rn

Then you just apply the UNION ALL operation and ORDER BY on the just generated ranking.

CodePudding user response：

Another approach would be:

SELECT name,
       IF(tbl = 1, rank, NULL) AS rankingA,
       IF(tbl = 2, rank, NULL) AS rankingB,
       ROW_NUMBER() OVER (ORDER BY rank_grp, tbl, rank) final_rank 
  FROM (
    SELECT name, rankingA AS rank, DIV(rankingA - 1, 2) AS rank_grp, 1 AS tbl FROM tableA
     UNION ALL
    SELECT name, rankingB, DIV(rankingB - 1, 3), 2 FROM tableB
  );

 --------- ---------- ---------- ------------ 
|  name   | rankingA | rankingB | final_rank |
 --------- ---------- ---------- ------------ 
| Kevin   |        1 |          |          1 |
| Jack    |        2 |          |          2 |
| William |          |        1 |          3 |
| Laurel  |          |        2 |          4 |
| Sophie  |          |        3 |          5 |
| Kate    |        3 |          |          6 |
| Randall |        4 |          |          7 |
| Tess    |          |        4 |          8 |
| Deja    |          |        5 |          9 |
| Toby    |          |        6 |         10 |
| Beck    |        5 |          |         11 |
| Nick    |          |        7 |         12 |
 --------- ---------- ---------- ------------

CodePudding user response：

You may try the following:

select Name, RankingA, RankingB,
       rank() over (order by NewRank) FinalRank
from
  (
   select Name, cast(RankingA as string) as RankingA , '' as RankingB,
       RankingA   floor((RankingA-1)/2)*3 as NewRank
   from TableA
   union all
   select Name, '', cast(RankingB as string), 
       RankingB   ceiling((RankingB)/3)*2
   from TableB
  ) T

RankingA floor((RankingA-1)/2)*3: shifts each two consecutive RankingA values (1,2 and 3,4 and 5,6 ...) by n * 3 where n starts from 0.

RankingB ceiling((RankingB)/3)*2: shifts each three consecutive RankingB values (1,2,3 and 4,5,6 ...) by n * 2 where n starts from 1.