Introduction
TL;DR : Why to use UNPIVOT
over UNION ALL
? Performance, UNION ALL
scans all the data for each subquery, with UNPIVOT
the scan happens only once.
We shall perform now the same operation with BigQuery
Feel free to check the Oracle XE CROSS JOIN UNPIVOT tips or a post on the internet.
I'm writing this guide to provide a simple step-by-step manual easy to understand.
UNPIVOT
Let's start straight away with something trivial :
select 'SUNDAY' as day0, 'MONDAY' as day1;
select array['SUNDAY','MONDAY'] as day;
Look carefully, in fact there is only 1 row :
And once you have performed UNNEST :
select * from unnest(array['SUNDAY','MONDAY']);
you have 2 rows :
As explained in the UNNEST documentation
the operator takes an array
as an input and returns a table with a single row for each element in the array
.
What if we have to UNPIVOT
multiple values ?
The comma in between the UNNEST
in the query below stands for the CROSS JOIN
:
select * from unnest(array['SUNDAY','MONDAY']) as day, unnest(array[0,1]) as day_order;
This is now what we wanted to achieve, hence let's try to use UNPIVOT
with data as (select 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from data
unpivot(
(day, day_order) for day_column in (
(day0, day_order0) as 'day0',
(day1, day_order1) as 'day1'
)
);
Great ! Now we know how to use UNNEST
and UNPIVOT
, let's dive into CROSS JOIN
.
CROSS JOIN
In a nutshell CROSS JOIN
operation is simple = it's just a Cartesian product of the two tables, take all the possible combinations from the values on the left with the values on the right.
Few examples :
with
week_data as (select '2024W01' as week_ref),
day_data as (select 'SUNDAY' as day_ref)
select * from week_data cross join day_data;
with
week_data as (select '2024W01' as week_ref),
day_data as (
select 'SUNDAY' as day_ref
union all
select 'MONDAY' as day_ref)
select * from week_data cross join day_data;
a bit of array
:
with
week_data as (select '2024W01' as week_ref),
day_data as (
select array['SUNDAY', 'MONDAY'] as day_ref)
select * from week_data cross join day_data;
look carefully, there is only 1 row in the result :
let's have 2 rows with UNNEST
:
with
week_data as (select '2024W01' as week_ref),
day_data as (
select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;
and finally the full power of CROSS JOIN
:
with
week_data as (select * from unnest(array['2024W01', '2024W02']) as week_ref),
day_data as (
select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;
We are ready now to combine the CROSS JOIN
with UNPIVOT
or UNNEST
.
CROSS JOIN UNPIVOT
Let's combine both CROSS JOIN
and UNPIVOT
, first I will generate some data that we would like to slice and to stack the chunks :
with
in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from in_data;
And let's have the desired result :
with
in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day, day_order from in_data
unpivot(
(day, day_order) for day_column in (
(day0, day_order0) as 'day0',
(day1, day_order1) as 'day1'
)
);
Thanks to the examples above you know that what happens here is CROSS JOIN
of the week_ref
on the left with the result of UNPIVOT
on the right.
If you have only one column you can take a shortcut with UNNEST
:
with
in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day from in_data
cross join unnest(array[day0, day1]) as day;
Enjoy!
Top comments (0)