Как сделать поворот в Google BigQuery
Предположим, у меня есть следующий запрос в BQ:
SELECT shipmentID, category, quantity
FROM [myDataset.myTable]
Кроме того, предположим, что запрос возвращает такие данные, как:
shipmentID category quantity
1 shoes 5
1 hats 3
2 shirts 1
2 hats 2
3 toys 3
2 books 1
3 shirts 1
Как я могу повернуть результаты из BQ, чтобы произвести вывод следующим образом:
shipmentID shoes hats shirts toys books
1 5 3 0 0 0
2 0 2 1 0 1
3 0 0 1 3 0
В качестве некоторого дополнительного фона у меня на самом деле есть более 2000 категорий, которые мне нужно развернуть, и количество данных таково, что я не могу сделать это напрямую через Pandas DataFrame в Python (использует всю память, затем замедляется до сканирования) , Я попытался использовать реляционную базу данных, но столкнулся с лимитом столбцов, поэтому я хотел бы иметь возможность делать это непосредственно в BQ, даже если мне придется самому строить запрос через python. Какие-либо предложения?
** Правка 1 Я должен отметить, что поворот самих данных может быть выполнен кусками и, следовательно, не является проблемой. Реальная проблема заключается в попытке выполнить агрегацию впоследствии, так что у меня есть только одна строка для каждого shipmentID. Вот что жрет всю оперативку.
** Edit 2 После опробования принятого ответа ниже я обнаружил, что попытка использовать его для создания сводной таблицы столбцов 2k + приводила к ошибкам «Превышено количество ресурсов». Моя команда BQ смогла реорганизовать запрос, разбив его на более мелкие куски и позволив ему пройти. Основная структура запроса выглядит следующим образом:
SELECT
SetA.*,
SetB.*,
SetC.*
FROM (
SELECT
shipmentID,
SUM(IF (category="Rocks", qty, 0)),
SUM(IF (category="Paper", qty, 0)),
SUM(IF (category="Scissors", qty, 0))
FROM (
SELECT
a.shipmentid shipmentid,
a.quantity quantity,
a.category category
FROM
[myDataset.myTable] a)
GROUP EACH BY
shipmentID ) SetA
INNER JOIN EACH (
SELECT
shipmentID,
SUM(IF (category="Jello Molds", quantity, 0)),
SUM(IF (category="Torque Wrenches", quantity, 0))
FROM (
SELECT
a.shipmentID shipmentID,
a.quantity quantity,
a.category category
FROM
[myDataset.myTable] a)
GROUP EACH BY
shipmentID ) SetB
ON
SetA.shipmentid = SetB.shipmentid
INNER JOIN EACH (
SELECT
shipmentID,
SUM(IF (category="Deep Thoughts", qty, 0)),
SUM(IF (category="Rainbows", qty, 0)),
SUM(IF (category="Ponies", qty, 0))
FROM (
SELECT
a.shipmentid shipmentid,
a.quantity quantity,
a.category category
FROM
[myDataset.myTable] a)
GROUP EACH BY
shipmentID ) SetC
ON
SetB.shipmentID = SetC.shipmentID
Приведенный выше шаблон можно продолжить до бесконечности, добавивINNER JOIN EACH
сегменты один за другим. Для моего приложения BQ смог обработать около 500 столбцов на блок.