DEV Community

hexfloor
hexfloor

Posted on

SQL Pro tips : GCP BigQuery SQL CROSS JOIN with UNPIVOT UNNEST

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;
Enter fullscreen mode Exit fullscreen mode

Image description
Similar example with array :

select array['SUNDAY','MONDAY'] as day;
Enter fullscreen mode Exit fullscreen mode

Look carefully, in fact there is only 1 row :

Image description

And once you have performed UNNEST :

select * from unnest(array['SUNDAY','MONDAY']);
Enter fullscreen mode Exit fullscreen mode

you have 2 rows :

Image description
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;
Enter fullscreen mode Exit fullscreen mode

Image description

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'
  ) 
);
Enter fullscreen mode Exit fullscreen mode

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description
Now let's have some UNION ALL

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;
Enter fullscreen mode Exit fullscreen mode

Image description

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;
Enter fullscreen mode Exit fullscreen mode

look carefully, there is only 1 row in the result :

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description

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'
  ) 
);
Enter fullscreen mode Exit fullscreen mode

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description

Enjoy!

Top comments (0)