I have two tables that I want to merge. Both of them have shared ID I am merging on. And the same number of rows. I just want to add one more column from table B to table A. Here is table A:
date custom_key pageviews
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 1
2022-06-29 2022-06-29_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2
Table B:
date custom_key custom_date
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05
2022-06-29 2022-06-29_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05
The desired output is:
date custom_key custom_date pageviews
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05 1
2022-01-05 2022-01-05_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05 1
2022-06-29 2022-06-29_13b6c84c-c369-4c27-92c1-612b87a8e6e0 2022-01-05 2
It is crucial to keep the same number of rows as I am doing additional analysis afterward by counting the rows. I tried the following code but instead of 5 rows, it returned 17 rows.
select
a.*,
b.custom_date
from
`table_a` a
INNER JOIN `table_b` b ON a.custom_key = b.custom_key
>Solution :
Consider below approach
select a.* except(pos), b.custom_date
from (select *, row_number() over(partition by custom_key, date) pos from table_a) a
join (select *, row_number() over(partition by custom_key, date) pos from table_b) b
using(custom_key, date, pos)
if applied to sample data in your question – output is
