Регистрация

[SQL, Data Mining, Big Data, Google Cloud Platform, Data Engineering] Вращение таблиц в Google BigQuery (PIVOT)

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 3 месяца
Сообщений: 27286

Создавать темы news_bot ® написал(а)
03-Июн-2021 13:31
Цитировать

Сводные таблицы в Excel являются чрезвычайно мощным инструментом, поскольку позволяют быстро изменять форму данных в пару кликов мышки. Например, вам необходимо построить отчёт продаж по магазинам за год, таким образом, что бы в строках были названия магазинов, а в столбцах месяца. Не проблема, кидай в область столбцов сводной таблицы поле месяц, в область строк поле с названием торговых точек, а в поле значение сумму продаж. Отчёт готов.До недавних пор реализовать такую операцию в SQL было довольно проблематично, но недавно в функционал Google BigQuery была добавлен оператор PIVOT, о нём и пойдёт речь в этой статье.
Содержание
Если вы интересуетесь анализом данных наверняка вам будут интересены мои telegram и youtube канал. Большая часть контента посвящена языку программирования R. Подписывайтесь!
Синтаксис оператора PIVOTPIVOT вращает таблицу превращая значения одного из полей в отдельные столбцы. Тем кто знаком с понятием 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 развивается, и на данный момент даёт аналитику весь необходимый инструментарий для реальзации вычислений любой сложности: Если вы интересуетесь анализом данных приглашаю подписать на мой Telegram и YouTube каналы.
 ===========
 Источник:
habr.com
===========
Похожие новости: Теги для поиска: #_sql, #_data_mining, #_big_data, #_google_cloud_platform, #_data_engineering, #_bigquery, #_pivot, #_unpivot, #_pivoting, #_svodnye_tablitsy (сводные таблицы), #_sql, #_data_mining, #_big_data, #_google_cloud_platform, #_data_engineering
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 17-Май 05:48
Часовой пояс: UTC + 5