Динамическое отключение SQL для нескольких столбцов в Bigquery

Запись каждого столбца в раздел unpivot для меня тяжелая работа...

BigQuery(BQ) очень полезен для анализа или обработки данных.

Он хорошо справляется с большими объемами данных. Он возвращает итоговый результат в короткие сроки.

При обработке данных иногда мы хотели бы изменить столбцы на строки.

Но чтобы использовать UNPIVOT, мы должны написать имя каждого столбца.

Это хлопотно.

Колонки лучше задавать динамически.

Есть ли более простой способ разворота?

Итак, сегодня я расскажу об "динамическом развороте SQL для нескольких столбцов в Bigquery".

Author

Инженер среднего звена (ИИ, системы). Хорошо владеет Python и SQL.

UNPIVOT в BigQuery

WМы можем использовать UNPIVOT Bigquery, как показано ниже.

Сначала подготовьте данные с несколькими столбцами.

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

Затем мы устанавливаем столбцы в оператор «UNPIVOT», например «IN (Q1, Q2, Q3, Q4)».

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

Но таким образом, мы должны установить имя каждого столбца.

Это займет время.

Мне нужен более простой способ.

Динамическое отключение SQL для нескольких столбцов в Bigquery

Чтобы развернуть несколько столбцов, мы можем использовать функцию.

Это функция fhoffa.x.unpivot().

Мы можем использовать его, как показано ниже.

SELECT product, unpivotted
FROM Produce a 
  , UNNEST(fhoffa.x.unpivot(a, 'Q')) 
unpivotted

Тогда мы получаем следующий результат.

Второй параметр fhoffa.x.unpivot(a, 'Q') определяет столбцы.

В этом случае столбцы, в имени которых есть Q, являются целевыми столбцами.

Мы получаем тот же результат с Q[1-9].

Эта функция была введена в 2020 году.

Это определяемая пользователем функция (UDF).

Исходный код открыт.

Таким образом, вы можете определить его в своем собственном проекте.

Теперь мы можем использовать один и тот же SQL для множества столбцов.

Заключение

Сегодня я рассказал о "динамическом преобразовании SQL для нескольких столбцов в Bigquery".

Чтобы развернуть несколько столбцов, мы можем использовать UDF fhoffa.x.unpivot().

Используя регулярные выражения, мы можем выбрать множество столбцов.

Теперь нам не нужно задавать имя каждого столбца.

Есть и другие статьи о BigQuey.

Если вы заинтересованы в них, пожалуйста, прочитайте их.