Научитесь работать с записями и повторяющимися полями
Меня всегда раздражал SQL, потому что я не мог хранить списки удобным способом. К счастью, современные хранилища данных, такие как BigQuery, позволяют нам использовать массивы и структуры. Давайте посмотрим, как это работает, и научимся хранить и запрашивать вложенные данные!
Создание массивов и структур
Массивы
Допустим, вы заказали несколько товаров в местном супермаркете. Вы можете сохранить свой заказ в таблице orders. В каждом заказе должна быть одна строка, но каждый заказ может содержать несколько элементов.
SELECT 1 AS order_id, ["soap", "sugar", "batteries"] AS order_items
Массивы — это, по сути, списки, которые позволяют хранить несколько результатов в одном поле. Давайте сохраним наши результаты в таблице и посмотрим на схему.
Мы видим, что массивы — это не тип данных, а режим: наш массив строк обрабатывается как repeatedстрока.
Структуры
Если вы хотите перегруппировать похожие поля вместе, структуры — правильный выбор. Они эквивалентны объектам или словарям в других языках программирования и позволяют хранить пары ключей и значений.
В нашем примере мы можем захотеть сохранить идентификатор, цену, имя и количество для каждого продукта.
SELECT
STRUCT(
1 AS id,
"soap" AS name,
5.4 AS price,
2 AS quantity
) AS product
Давайте сохраним наши продукты в другой таблице и посмотрим на схему:
Здесь наше поле продукта имеет тип record.
Массивы структур
Мы только начинаем веселиться! Теперь вы хотите иметь список всех товаров, которые вы приобрели, со всеми деталями продукта.
SELECT 1 AS order_id, [STRUCT( 1 AS id, "soap" AS name, 5.4 AS price, 2 AS quantity ) , STRUCT( 2 AS id, "sugar" AS name, 3.1 AS price, 1 AS quantity ), STRUCT( 3 AS id, "batteries" AS name, 2.8 AS price, 1 AS quantity ) ] AS order_items
Как вы уже догадались, наше поле order_items — это record режима repeated.
Доступ к значениям
Внутренние записи
Предположим, мы хотим получить доступ к названию нашего продукта.
Это довольно просто, нам просто нужно добавить точку и указать ключ:
SELECT product.name FROM arrays_and_structs.products
Внутри массивов
Теперь мы хотим получить доступ ко всем различным значениям наших продуктов внутри нашего order_items:
Это становится немного сложнее, так как это повторяющееся поле, нам придется его разложить.
BigQuery предоставляет оператор UNNEST, который создает таблицу с одной строкой на каждое повторяющееся поле. Затем мы можем JOIN эту таблицу с нашей исходной таблицей.
SELECT DISTINCT oi AS order_item FROM arrays_and_structs.orders_array INNER JOIN UNNEST(order_items) AS oi
И теперь у нас есть наше отличное order_items !
Для этого есть быстрый способ INNER JOINс невложенным полем.
SELECT DISTINCT oi AS order_item FROM arrays_and_structs.orders_array, UNNEST(order_items) AS oi
Будьте осторожны, если ваше вложенное поле пусто, это внутреннее соединение исключит эту строку. Представьте, что у нас есть эта таблица подарков, полученных людьми на день рождения:
Поскольку Том не получил никакого подарка, если мы разложим его с помощью внутреннего соединения, вот что мы получим:
Наш бедный друг Том не только ничего не получил, но и исчез с нашего стола! Если мы хотим сохранить всех, подарок или нет, нам нужно выполнить левое соединение.
SELECT name, gift FROM gifts LEFT JOIN UNNEST(gifts_received) AS gift
На этот раз Том здесь:
Внутри массивов структур
Предположим, мы хотим получить общую сумму каждого заказа. Нам нужно будет умножить количество и цену каждого товара, а затем просуммировать их.
Мы просто объединяем все, что мы узнали для доступа к структурам и массивам, и суммируем сумму:
SELECT order_id, SUM(order_item.price * order_item.quantity) AS order_amount FROM `arrays_and_structs.arrays_of_structs` LEFT JOIN UNNEST(order_items) order_item GROUP BY order_id
И вот наша общая сумма!
Если хотите копнуть дальше, ознакомьтесь с официальной документацией по работе с массивами и вложенным и повторяющимся полям.
И если вам понравилась эта статья, подпишитесь на меня, чтобы узнать больше о SQL и BigQuery, например, об этой статье о SQL JOINS:
