UNPIVOT данных с использованием CROSS JOIN

Зачастую мы получаем данные в предагрегированном виде, когда каждая отдельная колонка является посчитанной метрикой. По аналогии мы получаем подобный результат, когда строим сводную таблицу в Excel и используем некоторое количество фактов для агрегации. Но что делать, если нам нужно произвести обратную операцию — Unpivot?

Как поступить, если в датасете понадобилось трансформировать данные в реляционный вид? В Tableau есть фича Unpivot, которая сделает всё сама: если датасет построен из файла, достаточно выделить нужные колонки и нажать на кнопку «Pivot». А в некоторых диалектах SQL, например, в Transact, уже есть встроенные функции, которые тоже делают это сами.

Но в случае, если датасет построен на Custom SQL Query из базы данных, у которой в арсенале отсутствуют встроенные функции для трансформации в сводную и обратно, необходим какой-то другой подход, и Tableau порекомендует для такой таблицы:

——
|**ID**|**a**|**b**|**c**|
|1|a1|b1|c1|
|2|a2|b2|c2|
——

Воспользоваться таким стандартным универсальным, но не очень эффективным решением:

select id, ‘a’ AS col, a AS value
from yourtable
union all
select id, ‘b’ AS col, b AS value
from yourtable
union all
select id, ‘c’ AS col, c AS value
from yourtable

И в результате получить таблицу вида:

——
|**id**|**col**|**value**|
|1|a|a1|
|2|a|a2|
|1|b|b1|
|2|b|b2|
|1|c|c1|
|2|c|c2|
——

Порой, когда мы работаем с физической таблицей и нам надо быстро получить результаты для двух-трех колонок, действительно, подобное решение можно быстро применить, не задумываясь. Однако в случае, когда вместо таблицы содержится, например, сложный подзапрос с несколькими джойнами и нужно сделать Pivot для 5+ колонок, подзапрос вызовется целых 5+ раз, согласитесь, не очень действенно считать одно и тоже неоднократно. Вместо этого можно воспользоваться рецептом с CROSS JOIN, найденным на просторах Stack Overflow:

select t.id,
c.col,
    case c.col
        when ‘a’ then a
        when ‘b’ then b
        when ‘c’ then c
    end as data
from yourtable t
cross join
(
    select ‘a’ as col
    union all select ‘b’
    union all select ‘c’
) c

Разберём запрос подробнее. CROSS JOIN — перекрёстное соединение, декартово произведение, или, проще говоря, произведение всех строк со всеми. За ненадобностью в синтаксисе CROSS JOIN отсутствует ON — мы объединяем не по какому-то конкретному полю две таблицы, а сразу по всем существующим строкам.

Сначала мы формируем таблицу со всеми колонками, предназначенными для преобразования в строки. В нашем случае это колонки a, b и c: поэтому мы сделали таблицу c, в которой будет колонка col со значениями a, b и c:

(
    select ‘a’ as col
    union all select ‘b’
    union all select ‘c’
) c

Выглядит она так:

——
|**col**|
|a|
|b|
|c|
——

Затем таблицы yourtable и c объединятся перекрестным соединением, а после мы возьмём поля id, col и в зависимости от того, как называется ячейка в col, подставим соответствующие данные в поле data.

select t.id,
c.col,
    case c.col
        when ‘a’ then a
        when ‘b’ then b
        when ‘c’ then c
    end as value
from yourtable t
cross join
(
    select ‘a’ as col
    union all select ‘b’
    union all select ‘c’
) c

В итоге получим ту же самую искомую таблицу, с которой уже можно удобно работать любым аналитическим инструментом:

——
|**id**|**col**|**value**|
|1|a|a1|
|2|a|a2|
|1|b|b1|
|2|b|b2|
|1|c|c1|
|2|c|c2|
——