Как в BigQuery «развернуть» столбец с массивом тегов заказа (ARRAY<STRING>) в отдельные строки для подсчёта популярности каждого тега?

Короткий ответ

Нужно использовать UNNEST(tags) в FROM/CROSS JOIN, что превращает каждый элемент массива в отдельную строку, после чего можно обычным GROUP BY посчитать частоту каждого тега.

Как это работает подробнее

UNNEST в BigQuery — табличная функция, которая принимает массив (ARRAY) и возвращает по одной строке на каждый его элемент; при использовании как CROSS JOIN UNNEST(tags) AS tag каждая исходная строка заказа «размножается» на число тегов в её массиве, сохраняя остальные поля заказа неизменными в каждой копии.

Пример кода

question-63.sqlКопировать

SELECT tag, COUNT(DISTINCT order_id) AS orders_with_tag
FROM orders, UNNEST(tags) AS tag
GROUP BY tag
ORDER BY orders_with_tag DESC;

Что использовать на практике

• Практика. это стандартный паттерн для анализа данных, хранящихся в полуструктурированном/вложенном виде (массивы, структуры) — частый случай в BigQuery для событийных и JSON-подобных данных. Аналогичная операция в PostgreSQL называется unnest(), а в ClickHouse — arrayJoin(), с похожей, но не идентичной семантикой.
• Проверка. Проверка: сумма orders_with_tag по всем тегам должна быть не меньше количества заказов хотя бы с одним тегом (может быть больше, так как у заказа может быть несколько тегов).

Подводные камни

• Частая ошибка. не учитывают, что заказ без тегов (пустой массив или NULL) при обычном CROSS JOIN UNNEST может просто исчезнуть из результата — для его сохранения нужен LEFT JOIN UNNEST(...) там, где диалект это поддерживает; забывают, что после разворота COUNT(*) считает число пар «заказ-тег», а не число заказов — для числа уникальных заказов с тегом нужен COUNT(DISTINCT order_id).