Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Сводные таблицы в Excel являются чрезвычайно мощным инструментом, поскольку позволяют быстро изменять форму данных в пару кликов мышки. Например, вам необходимо построить отчёт продаж по магазинам за год, таким образом, что бы в строках были названия магазинов, а в столбцах месяца. Не проблема, кидай в область столбцов сводной таблицы поле месяц, в область строк поле с названием торговых точек, а в поле значение сумму продаж. Отчёт готов.
До недавних пор реализовать такую операцию в SQL было довольно проблематично, но недавно в функционал Google BigQuery была добавлен оператор PIVOT
, о нём и пойдёт речь в этой статье.
Содержание
Если вы интересуетесь анализом данных наверняка вам будут интересены мои telegram и youtube канал. Большая часть контента посвящена языку программирования R. Подписывайтесь!
Синтаксис оператора PIVOT
Динамическое создание списка столбцов
Обратная операция UNPIVOT
Заключение
Синтаксис оператора PIVOT
PIVOT
вращает таблицу превращая значения одного из полей в отдельные столбцы. Тем кто знаком с понятием Tidy Data, это операция по преобразованию таблицы из длинного формата в широкий.
Попробуем проделать эту операцию средствами Google BigQuery Standart SQL.
Код создания приведённой в примере таблицы
WITH sales_data AS (
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
SELECT * FROM sales_data
PIVOT
являеться часть блока FROM
и имеет следующий синтаксис:
FROM from_item[, ...] pivot_operator
pivot_operator:
PIVOT(
aggregate_function_call [as_alias][, ...]
FOR input_column
IN ( pivot_column [as_alias][, ...] )
) [AS alias]
as_alias:
[AS] alias
При использовании PIVOT
вам необходимо задать 3 свойства:
from_item
- таблица, или подзапрос результат которого мы будем вращатьaggregate_function_call
- описание того, как сжимать данные при вращении, это необходимо в связи с тем, что в результате мы сжимаем значение из нескольких строк в одну ячейку. В нашем примере каждый магазин в каждом месяце имеет несколько записей, и нам необходимо получить в результате сумму продаж каждого магазина в каждом месяце. Т.е. продажи одного магазина в каждом из месяцев нам необходимо просуммировать.FOR
- значения столбца, которые будут образовывать в результате новые столбцы
Теперь давайте попробуем повернуть таблицу, приведённую в нашем примере.
WITH sales_data AS (
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
SELECT * FROM
(
-- #1 подзапрос который будем разворачивать
SELECT * FROM sales_data
)
PIVOT(
-- #2 агрегация, в нашем случае суммирование
SUM(sales) as sum_of
-- #3 значения для новых столбцов
FOR month IN ('jan', 'feb', 'mar')
)
К сожалению в блоке FOR
на данный момент мы не можем использовать подзапрос, названия новых полей необходимо прописывать руками.
Результат запроса:
Необязательно оператору FOR
передавать список всех уникальных значений разворачиваемого столбца. Например, если нам необходимо вывести продажи только по февралю и марту то мы можем перечислить только их.
SELECT * FROM
(SELECT * FROM sales_data)
PIVOT(SUM(sales) as sum_of FOR month IN ('feb', 'mar'))
Динамическое создание списка столбцов
Выше я писал о том, что вы не можете в блоке FOR
ссылаться на какой то конкретный столбец не перечислив руками его значения, из которых будут созданы новые столбцы. Тем не менее один способ есть, и этот способ был подсмотрен в статье Лака Лакшманана "PIVOT in BigQuery".
Лак предлагает изначально создать переменную в которой мы сгенерируем текст для блока FOR IN
. Далее использовать эту переменную в скрипте.
Создание переменной:
DECLARE months STRING; -- объявляем переменную
-- присваиваем переменной значение, полученное в результате конкатенации
-- в нашем случае значение будет ("jan", "feb", "mar")
SET months = (
SELECT
CONCAT('("', STRING_AGG(DISTINCT month, '", "'), '")'),
FROM
(
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
);
Если вы запустите этот код, то получите следующий результат:
Т.е. как раз то выражение, которое нам необходимо было прописать руками в блоке FOR IN
.
Теперь мы можем использовать полученную переменную months
для генерации запроса.
-- подставим переменную months в запрос
EXECUTE IMMEDIATE format("""
WITH sales_data as
(
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
SELECT * FROM
(SELECT * FROM sales_data)
PIVOT(SUM(sales) as sum_of FOR month IN %s)
""", months);
Мы подставили с помощью функции format()
переменную months
в запрос, в блок FOR IN
.
Полный код генерации и выполнения динамического запроса
DECLARE months STRING; -- объявляем переменную
-- присваиваем переменной значение, полученное в результате конкатенации
-- в нашем случае значение будет ("jan", "feb", "mar")
SET months = (
SELECT
CONCAT('("', STRING_AGG(DISTINCT month, '", "'), '")'),
FROM
(
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
);
-- подставим переменную months в запрос
EXECUTE IMMEDIATE format("""
WITH sales_data as
(
SELECT 'shop_1' as shop_name, 'jan' as month, 105 as sales UNION ALL
SELECT 'shop_2', 'jan', 123 UNION ALL
SELECT 'shop_3', 'jan', 100 UNION ALL
SELECT 'shop_1', 'feb', 211 UNION ALL
SELECT 'shop_2', 'feb', 250 UNION ALL
SELECT 'shop_3', 'feb', 132 UNION ALL
SELECT 'shop_1', 'mar', 303 UNION ALL
SELECT 'shop_2', 'mar', 231 UNION ALL
SELECT 'shop_3', 'jan', 167 UNION ALL
SELECT 'shop_1', 'jan', 111 UNION ALL
SELECT 'shop_2', 'feb', 267 UNION ALL
SELECT 'shop_3', 'feb', 305 UNION ALL
SELECT 'shop_1', 'feb', 344 UNION ALL
SELECT 'shop_2', 'mar', 210 UNION ALL
SELECT 'shop_3', 'mar', 306 UNION ALL
SELECT 'shop_1', 'mar', 195 UNION ALL
SELECT 'shop_2', 'mar', 276 UNION ALL
SELECT 'shop_3', 'mar', 129)
SELECT * FROM
(SELECT * FROM sales_data)
PIVOT(SUM(sales) as sum_of FOR month IN %s)
""", months);
Результат:
Мы получили результат аналогичный прежнему, но в данном случае нам не потребовалось в ручную перечислять все месяца в запросе. Когда у вас всего 3 месяца использование такого приёма не оправдано, а если бы у вас были тысячи уникальных значений, то вариант перечисления их в ручную был бы практически невозможен.
Обратная операция UNPIVOT
Оператор UNPIVOT позволяет произвести обратную операцию, хоть в базах данных крайне редко можно встретить таблицы представленные в широком формате.
Синтаксис UNPIVOT:
FROM from_item[, ...] unpivot_operator
unpivot_operator:
UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
{ single_column_unpivot | multi_column_unpivot }
) [unpivot_alias]
single_column_unpivot:
values_column
FOR name_column
IN (columns_to_unpivot)
multi_column_unpivot:
values_column_set
FOR name_column
IN (column_sets_to_unpivot)
values_column_set:
(values_column[, ...])
columns_to_unpivot:
unpivot_column [row_value_alias][, ...]
column_sets_to_unpivot:
(unpivot_column [row_value_alias][, ...])
unpivot_alias and row_value_alias:
[AS] alias
Код генерации таблицы для примеров:
WITH sales_data as
(
SELECT 'shop_1' as shop_name, 216 as jan, 555 as feb, 498 as mar UNION ALL
SELECT 'shop_2', 123, 517, 717 UNION ALL
SELECT 'shop_3', 267, 437, 435
)
SELECT * FROM sales_data
Таблица которую мы преобразуем из широкого формата в длинный:
Для того, что бы свернуть эту таблицу, т.е. преобразовать эти пять столбцов в три, магазин, месяц, сумма продаж, достаточно выполнить следующую операцию UNPIVOT
.
WITH sales_data as
(
SELECT 'shop_1' as shop_name, 216 as jan, 555 as feb, 498 as mar UNION ALL
SELECT 'shop_2', 123, 517, 717 UNION ALL
SELECT 'shop_3', 267, 437, 435
)
SELECT * FROM sales_data
UNPIVOT(sales FOR month IN (jan, feb, mar))
Результат данного запроса:
В операторе UNPIVOT
мы указали:
название столбца в который мы поместили значения сворачиваемых столбцов
sales
название стобца в который поместили имена сворачиваемых столбцов
month
перечислили столбцы, которые необходимо свернуть
(jan, feb, mar)
.
Заключение
Standart SQL в Google BigQuery развивается, и на данный момент даёт аналитику весь необходимый инструментарий для реальзации вычислений любой сложности:
Оконные функции
Работа с вложенными конструкциями с помощью функции UNNEST
PIVOT
послужил вишенкой на торте
Если вы интересуетесь анализом данных приглашаю подписать на мой Telegram и YouTube каналы.