Научете се да работите със записи и повтарящи се полета
Винаги намирах 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:
