Вращение таблиц в Google BigQuery (PIVOT)

Сводные таблицы в Excel являются чрезвычайно мощным инструментом, поскольку позволяют быстро изменять форму данных в пару кликов мышки. Например, вам необходимо построить отчёт продаж по магазинам за год, таким образом, что бы в строках были названия магазинов, а в столбцах месяца. Не проблема, кидай в область столбцов сводной таблицы поле месяц, в область строк поле с названием торговых точек, а в поле значение сумму продаж. Отчёт готов.

До недавних пор реализовать такую операцию в SQL было довольно проблематично, но недавно в функционал Google BigQuery была добавлен оператор PIVOT, о нём и пойдёт речь в этой статье.

Содержание

Если вы интересуетесь анализом данных наверняка вам будут интересены мои telegram и youtube канал. Большая часть контента посвящена языку программирования R. Подписывайтесь!

1. Синтаксис оператора PIVOT

2. Динамическое создани�� списка столбцов

3. Обратная операция UNPIVOT

4. Заключение

Синтаксис оператора PIVOT

PIVOT вращает таблицу превращая значения одного из полей в отдельные столбцы. Тем кто знаком с понятием Tidy Data, это операция по преобразованию таблицы из длинного формата в широкий.

Пример использования PIVOT

Попробуем проделать эту операцию средствами 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 на данный момент мы не можем использовать подзапрос, названия новых полей необходимо прописывать руками.

Результат запроса:

Результат преобразования через PIVOT

Необязательно оператору 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 каналы.