Сведение повторяющихся полей таблицы и агрегирование

Хотя я довольно привык к PostgreSQL и многим другим диалектам SQL, эта вещь меня ставит в тупик:

У меня есть таблица BigQuery, которая выглядит примерно так:

orders
|- orderId
|- orderStatus
|- orderLines
   |- sku
   |- price_per_item
   |- quantity

В стандартном SQL я бы сделал:

select orderLines.sku, sum(orderLines.price_per_item * quantity)
from flatten(orders, orderLines.sku) o
where orderStatus = 'valid'

но «выравнивание» не работает в стандартном SQL.

Итак, я могу сделать что-то вроде этого:

select array(select sku FROM UNNEST(orderLines)) sku, array(select price_per_item from unnest(orderLines)) revenue
from orders

Однако я не могу суммировать это сейчас, т.е.:

select array(select sku FROM UNNEST(orderLines)) sku, sum(array(select price_per_item from unnest(orderLines))) revenue
from orders
group by sku

И я попытался сделать это с помощью инструкции with для предварительного создания таблицы. Но результат тот же.

Каков правильный подход и почему это кажется излишне многословным?

Меня немного раздражает необходимость использовать устаревший SQL, потому что я также использую функцию в объединении, и это работает только в стандартном SQL.


google-bigquery bigquery-standard-sql
person dengar81    schedule 17.07.2018    source источник
comment
Возможный дубликат выражения UNNEST ссылается на продукты столбца, которые ни сгруппированы, ни объединены в   -  person Pentium10    schedule 17.07.2018

Ответы (1)


arrow_upward
2
arrow_downward

Если вы знакомы с массивами в PostgreSQL, возможно, вы уже использовали оператор UNNEST. Вам нужно использовать его в этом случае, чтобы соединить массив с самой таблицей, сглаживая повторение:

select orderLine.sku, sum(orderLine.price_per_item * quantity)
from orders, UNNEST(orderLines) AS orderLine
where orderStatus = 'valid'
GROUP BY sku

(Я добавил GROUP BY, так как похоже, что он отсутствует). Дополнительную информацию о массивах, включая примеры использования UNNEST, см. в документации. Если вы привыкли использовать устаревший SQL в BigQuery, существует руководство по миграции, в котором среди прочих тем описываются различия в выравнивании между устаревшим и стандартным SQL в BigQuery.

person Elliott Brossard    schedule 17.07.2018