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.
Disclaimer : in the example below WEEK_NUMBER is not a suitable identifier for the varchar type, the better name is WEEK_REF and the variables should better be prefixed with year, like 2024W01
Setup
Feel free to check the Oracle XE Quick Start
I will be using docker enabled Oracle XE 21.3.0 with DBeaver as a client.
UNPIVOT
Before starting, I'm inviting you to check a wonderful article from Oracle regarding PIVOTand UNPIVOT. If this article does exist, why to write another one? The answer is simple : because this article is a step-by-step guide which will help you to understand over copy-paste.
While writing this article I have found another good article about unpivot, check it out !
What is the business problem we are trying to solve : as a result of an aggregation over a big chunk of data we have a dataset composed with rows with multiple columns that we would like to stack one below another, probably with the common prefix for each chunk of the row stacked in the new representation.
Let's have an example :
SELECT
'SUNDAY' AS DAY_0,
'MONDAY' AS DAY_1
FROM DUAL;
Simple stacking with UNION ALL :
WITH
DAY_DATA AS
(SELECT
'SUNDAY' AS DAY_0,
'MONDAY' AS DAY_1
FROM DUAL)
SELECT DAY_0 AS DAY_NAME FROM DAY_DATA
UNION ALL
SELECT DAY_1 AS DAY_NAME FROM DAY_DATA;
And the same result with UNPIVOT :
SELECT DAY_NAME FROM (
(SELECT
'SUNDAY' AS DAY_0,
'MONDAY' AS DAY_1
FROM DUAL)
UNPIVOT (
DAY_NAME FOR DAY_COLUMN IN (DAY_0, DAY_1)
)
)
Great ! Let's move on and add two more columns that we would like to stack :
SELECT
'SUNDAY' AS DAY_0,
0 AS DAY_ORDER_0,
'MONDAY' AS DAY_1,
1 AS DAY_ORDER_1
FROM DUAL
Stacking values from the columns DAY_0 and DAY_1 into the column DAY_NAME and values from the columns DAY_ORDER_0 and DAY_ORDER_1 into the column DAY_ORDER with UNION ALL :
WITH
DAY_DATA AS
(SELECT
'SUNDAY' AS DAY_0,
1 AS DAY_ORDER_0,
'MONDAY' AS DAY_1,
1 AS DAY_ORDER_1
FROM DUAL)
SELECT DAY_0 AS DAY_NAME, DAY_ORDER_0 AS DAY_ORDER FROM DAY_DATA
UNION ALL
SELECT DAY_1 AS DAY_NAME, DAY_ORDER_1 AS DAY_ORDER FROM DAY_DATA;
And the same result with UNPIVOT
SELECT * FROM (
(SELECT
'SUNDAY' AS DAY_0,
1 AS DAY_ORDER_0,
'MONDAY' AS DAY_1,
1 AS DAY_ORDER_1
FROM DUAL)
UNPIVOT (
(DAY_NAME, DAY_ORDER)
FOR DAY_COLUMN IN (
(DAY_0, DAY_ORDER_0) AS 'DAY_0',
(DAY_1, DAY_ORDER_1) AS 'DAY_1'
)
)
)
And when we have a common classifier :
SELECT
WEEK_NUMBER,
DAY_NAME,
DAY_ORDER
FROM (
(SELECT
'W01' AS WEEK_NUMBER,
'SUNDAY' AS DAY_0,
1 AS DAY_ORDER_0,
'MONDAY' AS DAY_1,
1 AS DAY_ORDER_1
FROM DUAL)
UNPIVOT (
(DAY_NAME, DAY_ORDER)
FOR DAY_COLUMN IN (
(DAY_0, DAY_ORDER_0) AS 'DAY_0',
(DAY_1, DAY_ORDER_1) AS 'DAY_1'
)
)
)
That's it !
CROSS JOIN
Example above uses implicit CROSS JOIN under the hood, you may check an Oracle article about CROSS JOIN
Firstly let's have a simple example of CROSS JOIN :
WITH WEEK_DATA AS (
SELECT
'W01' AS WEEK_NUMBER
FROM DUAL),
DAY_DATA AS (
SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;
Adding another row to the DAY_DATA:
WITH WEEK_DATA AS (
SELECT
'W01' AS WEEK_NUMBER
FROM DUAL),
DAY_DATA AS (
SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
UNION ALL
SELECT 'MONDAY' AS DAY_NAME FROM DUAL
)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;
Adding another row to the WEEK_DATA :
WITH WEEK_DATA AS (
SELECT 'W01' AS WEEK_NUMBER FROM DUAL
UNION ALL
SELECT 'W02' AS WEEK_NUMBER FROM DUAL
),
DAY_DATA AS (
SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
UNION ALL
SELECT 'MONDAY' AS DAY_NAME FROM DUAL
)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;
CROSS JOIN with UNPIVOT
Let's combine both CROSS JOIN and UNPIVOT :
WITH WEEK_DATA AS (
SELECT 'W01' AS WEEK_NUMBER FROM DUAL
),
DAY_DATA AS (
SELECT
DAY_NAME,
DAY_ORDER
FROM (
(SELECT
'SUNDAY' AS DAY_0,
1 AS DAY_ORDER_0,
'MONDAY' AS DAY_1,
1 AS DAY_ORDER_1
FROM DUAL)
UNPIVOT (
(DAY_NAME, DAY_ORDER)
FOR DAY_COLUMN IN (
(DAY_0, DAY_ORDER_0) AS 'DAY_0',
(DAY_1, DAY_ORDER_1) AS 'DAY_1'
)
)
)
)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;
Now you know how to combine both CROSS JOIN and UNPIVOT, the concepts explained above are semantically the same for the Big Data solutions hosted in different cloud providers, I will show few examples in the following articles. Stay tuned !
Top comments (0)