selesnow 3 июн 2021 в 10:31

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

7 мин

5.3K

SQL*Data Mining*Big Data*Google Cloud Platform*Data Engineering*

Туториал

Сводные таблицы в 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 каналы.

Теги:

Хабы:

Если эта публикация вас вдохновила и вы хотите поддержать автора — не стесняйтесь нажать на кнопку

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

Содержание

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

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

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

Заключение

Публикации

Истории

Работа

Ближайшие события