Рішення
%%sql
SELECT round(avg(orders_count), 2) as orders_avg
FROM (SELECT count(order_id) as orders_count
FROM user_actions
WHERE action = 'create_order'
GROUP BY user_id) as t1| orders_avg | |
|---|---|
| 0 | 2.78 |
18 Підзапити
18.1 Порядок виконання запитів
Перш ніж переходити до підзапитів, давайте трохи поговоримо про порядок виконання запитів у SQL.
До цього моменту ми вже встигли познайомитися з основними операторами, які складають “скелет” стандартного SQL-запиту: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY та LIMIT.
Ми вже знаємо, що порядок їх написання у запиті наступний:
SELECT -- перелік полів результуючої таблиці
FROM -- вказівник джерела даних
WHERE -- фільтрація даних
GROUP BY -- групування даних
HAVING -- фільтрація даних після групування
ORDER BY -- сортування результуючої таблиці
LIMIT -- обмеження кількості записів у виводіПроте важливо розуміти, що порядок виконання операторів у базах даних дещо відрізняється від порядку їх написання у запиті. У спрощеному вигляді порядок виконання запиту такий:
FROM -- вказівник джерела даних
WHERE -- фільтрація даних
GROUP BY -- групування даних
HAVING -- фільтрація даних після групування
SELECT -- перелік полів результуючої таблиці
ORDER BY -- сортування результуючої таблиці
LIMIT -- бмеження кількості записів у виводіТаким чином:
- Спочатку з допомогою
FROMвизначається таблиця. - Потім відповідно до зазначеної у
WHEREумові з цієї таблиці відбираються записи. - Потім вибрані дані групуються та агрегуються за допомогою
GROUP BY. - Далі з агрегованих записів відбираються ті, які задовольняють умову
HAVING. - Тільки після цього відповідно до зазначених у
SELECTінструкціях формується результуюча таблиця — проводяться всі необхідні обчислення, надаються нові імена тощо. - Потім результуюча таблиця сортується відповідно до
ORDER BY. - І нарешті спрацьовує обмеження кількості рядків, вказане в
LIMIT.
Насправді це дуже важлива інформація, яку слід пам’ятати при складанні будь-яких SQL-запитів.
Можливо, ви вже зіткнулися з помилками, коли намагалися в блоці WHERE використовувати фільтрацію по нових полях, розрахованих у SELECT. Така операція не відповідає порядку виконання операторів і тому так робити не можна. Також ви могли потрапити в ситуацію, коли при фільтрації у HAVING у вас не приймався аліас колонки, розрахованої в SELECT. Це відбувалося з тієї ж причини – база даних просто ще не знала про те, що ви надали колонці нове ім’я.
І ще одна важлива порада. Зверніть увагу, що фільтрацію даних за неагрегованими значеннями можна робити як у блоці WHERE, так і в блоці HAVING. Уважно подивіться на такі запити:
SELECT sex, COUNT(user_id)
FROM users
WHERE sex != 'male'
GROUP BY sex
SELECT sex, COUNT(user_id)
FROM users
GROUP BY sex
HAVING sex != 'male'Їх результат буде однаковим (можете переконатися в цьому самі).
Проте робити фільтрацію за неагрегованими даними рекомендується саме у блоці WHERE, тобто заздалегідь. У такому разі ви ще до угруповання прибираєте з розрахунків непотрібні вам дані і таким чином не витрачаєте обчислювальні ресурси на підрахунок значень, які будуть відфільтровані вами пізніше.
Це важливий момент щодо оптимізації SQL-запитів, тому рекомендуємо вам взяти до уваги інформацію.
18.2 Підзапити
Підзапити це потужним інструментом в мові SQL, який дозволяє нам виконувати запити в середині інших запитів. Вони дають можливість створювати складніші і більш гнучкі запити, які вимагають доступу до даних з кількох таблиць або виконання додаткових обчислень.
Один із сценаріїв використання підзапитів - це пошук даних в одній таблиці на основі значень з іншої таблиці. Наприклад, ми можемо створити запит, що повертає всіх клієнтів, які зробили замовлення на товари з певної категорії, використовуючи підзапит для вибірки ідентифікаторів цих товарів з таблиці товарів.
Наприклад у нас є дві таблиці: clients і orders. Нам потрібно отримати список клієнтів, які зробили замовлення після певної дати. Для цього ми можемо скористатися підзапитом:
SELECT *
FROM clients
WHERE ID IN (SELECT client_id
FROM orders
WHERE data > '2023-01-01');
)Іншими словами, підзапит — це лише запит усередині іншого запиту.
Підзапити можуть застосовуватись у таких частинах основного запиту:
- в операторі
FROM; - в операторі
SELECT(якщо запит повертає один стовпець з одним значенням); - в операторах
WHEREтаHAVING(якщо запит повертає один стовпець з одним або декількома значеннями).
Але давайте про все по порядку.
Насамперед важливо зрозуміти, що з результату виконання підзапиту можна звертатися також як і до таблиць у базі даних, тобто використовувати їх у блоці FROM замість наявних таблиць:
SELECT column_1
FROM (
SELECT column_1, column_2
FROM table
) AS subquery_1В межах даного запиту спочатку буде виконано підзапит, який відбере колонки column_1 і column_2 з таблиці table, а потім вже з таблиці, що утворилася, основний запит вибере колонку column_1.
Важливий момент: при використанні підзапиту в блоці FROM сформованій на основі підзапиту таблиці необхідно привласнити якийсь аліас, інакше основний запит не спрацює. У прикладі вище ми позначили результат підзапиту subquery_1.
Крім того, рівнів вкладеності може бути декілька:
SELECT column_1
FROM (
SELECT column_1, column_2
FROM (
SELECT column_1, column_2, column_3
FROM table
) AS subquery_1
) AS subquery_2В даному випадку послідовність роботи запиту така: спочатку буде виконано підзапит, що повертає результат subquery_1, потім підзапит, що повертає результат subquery_2, і тільки потім в результаті основного підзапиту потрапить колонка column_1. В результаті виходить щось схоже на матрьошку, при цьому до основної таблиці table звертається тільки перший підзапит subquery_1.
Наведений приклад досить умовний і на практиці колонки в такий спосіб відбирати не варто, але загальну ідею він має передавати.
Розуміння того, як працюють підзапити в блоці FROM, нам особливо стане в нагоді, коли ми вчитимемося об’єднувати різні таблиці.
А зараз давайте вирішимо просте завдання.
Завдання 18.1
Використовуючи дані з таблиці user_actions, розрахуйте середню кількість замовлень для всіх користувачів нашого сервісу. Для цього спочатку в підзапиті порахуйте скільки замовлень зробив кожен користувач, а потім зверніться до результату підзапиту в блоці FROM і вже в основному запиті усередніть кількість замовлень по всіх користувачах. Отриману середню кількість замовлень всіх користувачів округліть до двох знаків після коми. Назвіть колонку з цим значенням orders_avg.
Поле у результуючій таблиці: orders_avg
Пояснення: до колонок з підзапиту можна застосовувати агрегатні функції — так само, як би ми зверталися до колонок вихідних таблиць.
18.3 Табличні вирази: WITH
Вирішуючи попереднє завдання, ви могли запитати себе: а що якщо один і той же підзапит буде використовуватися в декількох частинах основного запиту? Невже щоразу доведеться дублювати той самий підзапит? А якщо рівнів вкладеності буде кілька? Чи не вийде тоді надто складний та громіздкий запит, який буде складно читати?
Для таких випадків SQL передбачений оператор WITH, який дозволяє створювати так звані табличні вирази (CTE, анг. common table expressions) - тимчасові таблиці, які існують тільки для одного запиту. Їхнє основне призначення полягає в розбиття складних запитів на кілька частин.
Табличні вирази створюються так:
WITH subquery_1 AS (
SELECT column_1, column_2
FROM table
)
SELECT column_1
FROM subquery_1Порівняйте запит вище з результатом запиту, який ми використовували раніше:
SELECT column_1
FROM (
SELECT column_1, column_2
FROM table
) AS subquery_1Оператор WITH може містити кілька табличних виразів, причому до зазначених раніше виразів можна звертатися у подальших виразах:
WITH subquery_1 AS (
SELECT column_1, column_2, column_3
FROM table
),
subquery_2 AS (
SELECT column_1, column_2
FROM subquery_1
)
SELECT column_1
FROM subquery_2Можете знову порівняти запит вище із запитом, який ми використовували раніше:
SELECT column_1
FROM (
SELECT column_1, column_2
FROM (
SELECT column_1, column_2, column_3
FROM table
) AS subquery_1
) AS subquery_2Використовувати у своїх запитах оператор WITH чи ні вирішувати вам, але в деяких випадках він може спростити роботу з кодом запиту.
Примітка
Докладніше про WITH та табличні вирази можна почитати тут.
Завдання 18.2
Повторіть запит із завдання 18.1, але тепер замість підзапиту використовуйте оператор WITH та табличний вираз. Умови завдання ті самі.
Поле у результуючій таблиці: orders_avg
Рішення
%%sql
with t1 as (SELECT user_id,
count(order_id) as orders_count
FROM user_actions
WHERE action = 'create_order'
GROUP BY user_id)
SELECT round(avg(orders_count), 2) as orders_avg
FROM t1| orders_avg |
|---|
Ще один важливий напрямок застосування підзапитів - створення більш просунутих умовних виразів в операторах WHERE та HAVING. Але оскільки і в тому, і в іншому випадку синтаксис і призначення підзапитів приблизно однакові, у цьому уроці ми розглядатимемо все на прикладі підзапитів у WHERE.
Насамперед важливо зрозуміти, що підзапит, який повертає одне значення, може використовуватися як звичайне значення спільно з операторами порівняння.
Уявіть, що нам потрібно порівняти значення в якомусь стовпці з максимальним, мінімальним чи середнім значенням у цьому стовпці. Зробити це в рамках одного запиту не вийде, оскільки агрегатні функції не можна використовувати в блоці WHERE.
Наприклад, наступний запит не працюватиме (база даних повідомить про помилку):
SELECT column
FROM table
WHERE column = MAX(column) У той же час такий запит спрацює, тому що підзапит виконається першим і поверне одне значення:
SELECT column
FROM table
WHERE column = (SELECT MAX(column) FROM table) В результаті виконання такого запиту ми отримаємо всі значення в колонці column, що дорівнює максимальному значенню в цій колонці.
Завдання 18.3
Виведіть з таблиці products інформацію про всі товари крім найдешевшого. Результат відсортуйте за зменшенням id товару.
Поля у результуючій таблиці: product_id, name, price
Рішення
%%sql
SELECT product_id,
name,
price
FROM products
WHERE price != (SELECT min(price)
FROM products)
ORDER BY product_id desc| product_id | name | price | |
|---|---|---|---|
| 0 | 87 | veal | 398.0 |
| 1 | 86 | cold coffee | 70.0 |
| 2 | 85 | jam | 200.0 |
| 3 | 84 | tangerines | 90.0 |
| 4 | 83 | waffles | 55.0 |
| ... | ... | ... | ... |
| 81 | 5 | coffee 3 in 1 | 15.0 |
| 82 | 4 | lollipops | 46.0 |
| 83 | 3 | still water | 80.0 |
| 84 | 2 | green tea bags | 50.0 |
| 85 | 1 | sugar | 150.0 |
86 rows × 3 columns
У блоці WHERE результати підзапитів, що повертають одне значення, також можна використовувати в арифметичних операціях:
SELECT column
FROM table
WHERE column = (SELECT MAX(column) FROM table) - 100Завдання 18.4
Виведіть інформацію про товари в таблиці products, ціна на які перевищує середню ціну всіх товарів на 20 і більше одиниць. Результат відсортуйте за зменшенням id товару.
Поля у результуючій таблиці: product_id, name, price
Рішення
%%sql
SELECT product_id,
name,
price
FROM products
WHERE price >= (SELECT avg(price)
FROM products) + 20
ORDER BY product_id desc
LIMIT 10| product_id | name | price | |
|---|---|---|---|
| 0 | 87 | veal | 398.0 |
| 1 | 85 | jam | 200.0 |
| 2 | 77 | chicken | 298.0 |
| 3 | 69 | cranberry juice | 175.0 |
| 4 | 66 | beef | 370.0 |
| 5 | 64 | pineapple | 200.0 |
| 6 | 61 | honey | 380.0 |
| 7 | 59 | sesame oil | 250.0 |
| 8 | 57 | pork | 450.0 |
| 9 | 51 | smoked fish | 195.0 |
18.4 Підзапит з інтервалом часу: INTERVAL
У яких випадках нам може стати в нагоді підзапит в операторі WHERE?
Уявімо, що нам потрібно провести якісь розрахунки за останні N днів — скажімо, за останній тиждень. Чи будемо ми вручну відраховувати 7 днів від останньої дати в нашій таблиці? Зрозуміло, що ні. Крім того, остання дата може згодом змінитись, коли до нас надійдуть нові дані. Щоразу писати новий запит і рахувати дату вручну — заняття не для нас.
Для вирішення такого завдання ми можемо спочатку за допомогою підзапиту обчислити останню дату наших даних, а потім відкласти від неї тиждень.
Щоб відкласти від дати або додати до неї певний проміжок часу, можна використовувати нескладні арифметичні операції з датами. Наприклад, від поточної дати можна відібрати якийсь проміжок INTERVAL:
SELECT NOW() - INTERVAL '1 year 2 months 1 week'
Результат:
2022-04-24 13:10До речі, NOW() — корисна функція, яка дозволяє отримувати поточну дату та час (у вашому випадку вона буде іншою):
SELECT NOW()
Результат:
2022-07-10 16:11
Примітка
Завдання 18.5
Порахуйте кількість унікальних клієнтів у таблиці user_actions, які зробили за останній тиждень хоча б одне замовлення. Отриману колонку зі значенням назвіть users_count. В якості поточної дати, від якої відкладати тиждень, використовуйте останню дату тієї ж таблиці user_actions.
Поле у результуючій таблиці: users_count
Рішення
%%sql
SELECT count(distinct user_id) as users_count
FROM user_actions
WHERE action = 'create_order'
and time > (SELECT max(time)
FROM user_actions) - interval '1 week'| users_count | |
|---|---|
| 0 | 17352 |
Виходить, що в одному з минулих завдань (16.10) ми неправильно рахували вік кур’єрів. Дійсно, як перша дата у функції AGE() ми використовували поточну дату, а не останню дату в наших даних. Давайте виправимо це прикре непорозуміння, адже тепер у нас для цього є всі необхідні знання.
Завдання 18.6
За допомогою функції AGE() і агрегатної функції знову розрахуйте вік наймолодшого кур’єра чоловічої статі в таблиці couriers, але цього разу в якості першої дати використовуйте останню дату з таблиці courier_actions. Щоб вийшла саме дата, перед застосуванням функції AGE() переведіть пораховану останню дату у формат DATE, як ми робили у 14.9. Вік кур’єра виміряйте кількістю років, місяців і днів і переведіть його до типу VARCHAR. Отриману колонку зі значенням віку назвіть min_age.
Поле у результуючій таблиці: min_age
Примітка
У цій задачі результат підзапиту виступає як аргумент функції. Щоб весь запит виглядав компактнішим, для приведення даних до іншого типу можна використовувати формат запису з двома двокрапками - ::.
Також зверніть увагу, що для отримання необхідного результату ми звертаємось до різних таблиць у рамках одного загального запиту – так можна робити теж.
Рішення
%%sql
-- варіант 1
SELECT age((SELECT max(time)::date
FROM courier_actions), max(birth_date))::varchar as min_age
FROM couriers
WHERE sex = 'male'
-- варіант 2
-- SELECT min(age((SELECT max(time)::date
-- FROM courier_actions), birth_date))::varchar as min_age
-- FROM couriers
-- WHERE sex = 'male'| min_age |
|---|
Підзапит, який повертає кілька значень, може використовуватися в блоці WHERE спільно з оператором IN — наприклад, коли нам потрібно перевірити, чи збігається значення в стовпці з одним із значень із певної множини, отриманої в результаті виконання підзапиту:
SELECT column_1
FROM table_1
WHERE column_1 IN (SELECT column_2 FROM table_2) При цьому, запит вище буде рівносильний запиту з табличним виразом:
WITH subquery AS (
SELECT column_2
FROM table_2
)
SELECT column_1
FROM table_1
WHERE column_1 IN (SELECT * FROM subquery) Зверніть увагу, що при використанні в операторі табличного виразу WHERE звернутися просто до його імені не можна - необхідно попередньо вибрати всі його записи, тобто написати підзапит. При цьому в табличному вираженні має бути лише один стовпець, інакше база даних поверне помилку.
Крім того, в табличному вираженні можна зберігати лише одне значення (наприклад, результат агрегації) і аналогічним чином викликати його в операторі WHERE як змінну:
WITH subquery AS (
SELECT MAX(column_2)
FROM table_2
)
SELECT column_1
FROM table_1
WHERE column_1 = (SELECT * FROM subquery) Давайте розглянемо такий приклад.
З наших даних досить легко відібрати скасовані замовлення - достатньо вказати у WHERE потрібний фільтр. Але як відібрати створені, але не скасовані замовлення? Це якраз можна зробити за допомогою підзапиту або табличного виразу.
Завдання 18.7
З таблиці user_actions за допомогою підзапиту або табличного виразу відберіть усі замовлення, які не було скасовано користувачами. Виведіть стовпчик з id цих замовлень. Результат запиту відсортуйте за зростанням id замовлення. Додайте в запит оператор LIMIT та виведіть лише перші 10 рядків результуючої таблиці.
Поле у результуючій таблиці: order_id
Рішення
%%sql
-- варіант 1
SELECT order_id
FROM user_actions
WHERE order_id in (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order')
ORDER BY order_id limit 10
-- варіант 2
-- SELECT order_id
-- FROM user_actions
-- WHERE order_id not in (SELECT order_id
-- FROM user_actions
-- WHERE action = 'cancel_order')
-- ORDER BY order_id limit 1018.5 Підзапити в блоці SELECT
Як було зазначено в першому кроці, вкладений запит може бути розміщений і після оператора SELECT. Однак результатом підзапиту в такому випадку може бути тільки одне значення — наприклад, результат застосування агрегатної функції до якоїсь колонки:
SELECT column_1, (SELECT MAX(column_1) FROM table) AS max_column_1
FROM tableВ такому випадку з таблиці table буде обрано колонка column_1, і навпаки кожного значення у цій колонці буде виведено результат виконання вкладеного запиту, тобто максимальне значення у цій колонці. При цьому давати аліасу результату підзапиту не обов’язково.
Також результати підзапитів у блоці SELECT можна використовувати у обчисленнях:
SELECT column_1, (SELECT MAX(column_1) FROM table) - 100 AS column_2
FROM tableЗавдання 18.8
Використовуючи дані з таблиці user_actions, розрахуйте скільки замовлень зробив кожен користувач і відобразіть це в стовпці orders_count. В окремому стовпці orders_avg навпроти кожного користувача вкажіть середню кількість замовлень всіх користувачів, округливши до двох знаків після коми. Також для кожного користувача порахуйте відхилення замовлень від середнього значення. Відхилення рахуйте так: число замовлень «мінус» заокруглене середнє значення. Назвіть колонку з відхиленням orders_diff. Результат відсортуйте за зростанням id користувача. Додайте в запит оператор LIMIT та виведіть лише перші 10 рядків результуючої таблиці.
Поля в результуючій таблиці: user_id, orders_count, orders_avg, orders_diff
Примітка
У цій задачі можна використовувати підзапит, написаний у перших завданнях цього уроку. Щоб не довелося двічі писати той самий підзапит, можна використовувати оператор WITH.
Рішення
%%sql
with t1 as (SELECT user_id,
count(order_id) as orders_count
FROM user_actions
WHERE action = 'create_order'
GROUP BY user_id)
SELECT user_id,
orders_count,
round((SELECT avg(orders_count) FROM t1), 2) as orders_avg,
orders_count - round((SELECT avg(orders_count) FROM t1), 2) as orders_diff
FROM t1
ORDER BY user_id limit 1000Перед тим, як піти далі, пропонуємо вам вирішити ще кілька завдань на підзапити.
Завдання 18.9
Виведіть id та вміст 100 останніх доставлених замовлень із таблиці orders. Вмістом замовлень вважаються списки з id товарів, що входять у замовлення. Результат відсортуйте за зростанням id замовлення.
Поля у результуючій таблиці: order_id, product_ids
Примітка
Зверніть увагу, що вміст замовлень знаходиться у таблиці orders, а інформація про дії із замовленнями – у таблиці courier_actions.
Рішення
%%sql
SELECT order_id,
product_ids
FROM orders
WHERE order_id in (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order'
ORDER BY time desc limit 100)
ORDER BY order_id| order_id | product_ids | |
|---|---|---|
| 0 | 59466 | [71, 50, 45] |
| 1 | 59481 | [26, 30, 84, 1] |
| 2 | 59482 | [32, 42, 79, 56] |
| 3 | 59487 | [9, 62, 77] |
| 4 | 59489 | [67, 42] |
| ... | ... | ... |
| 95 | 59591 | [74, 26, 69, 3] |
| 96 | 59592 | [34, 58, 31] |
| 97 | 59593 | [52, 46, 40] |
| 98 | 59594 | [2, 62] |
| 99 | 59595 | [18, 30, 67] |
100 rows × 2 columns
Завдання 18.10
З таблиці couriers виведіть всю інформацію про кур’єрів, які у вересні 2022 року доставили 30 і більше замовлень. Результат відсортуйте за зростанням id кур’єра.
Поля в результуючій таблиці: courier_id, birth_date, sex
Примітка
Зверніть увагу, що інформація про кур’єрів знаходиться в таблиці couriers, а інформація про дії із замовленнями - у таблиці courier_actions.
Рішення
%%sql
SELECT courier_id,
birth_date,
sex
FROM couriers
WHERE courier_id in (SELECT courier_id
FROM courier_actions
WHERE date_part('month', time) = 9
and date_part('year', time) = 2022
and action = 'deliver_order'
GROUP BY courier_id having count(distinct order_id) >= 30)
ORDER BY courier_id| courier_id | birth_date | sex | |
|---|---|---|---|
| 0 | 23 | 1990-03-26 | male |
| 1 | 869 | 2001-08-25 | female |
| 2 | 1466 | 1994-04-07 | male |
| 3 | 1664 | 1987-12-16 | male |
У цій задачі об’єднаємо знання про конструкцію CASE та підзапити.
Завдання 18.11
Призначте знижку 15% на товари, ціна яких перевищує середню ціну на всі товари на 50 і більше одиниць, а також знижку 10% на товари, ціна яких нижча за середню на 50 і більше одиниць. Ціну інших товарів усередині діапазону (середнє – 50; середнє + 50) залиште без змін. При розрахунку середньої ціни округліть її до двох знаків після коми.
Виведіть інформацію про всі товари із зазначенням старої та нової ціни. Колонку із новою ціною назвіть new_price. Результат відсортуйте спочатку за спаданням колишньої ціни в колонці price, потім за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, new_price
Рішення
%%sql
SELECT
product_id,
name,
price,
CASE
WHEN price > (SELECT ROUND(AVG(price), 2) + 50 FROM products) THEN ROUND(price * 0.85, 2)
WHEN price < (SELECT ROUND(AVG(price), 2) - 50 FROM products) THEN ROUND(price * 0.9, 2)
ELSE price
END AS new_price
FROM products
ORDER BY price DESC, product_id| product_id | name | price | new_price | |
|---|---|---|---|---|
| 0 | 13 | caviar | 800.0 | 680.00 |
| 1 | 37 | mutton | 559.0 | 475.15 |
| 2 | 15 | olive oil | 450.0 | 382.50 |
| 3 | 57 | pork | 450.0 | 382.50 |
| 4 | 43 | decaffeinated coffee | 400.0 | 340.00 |
| ... | ... | ... | ... | ... |
| 82 | 6 | crackers | 25.0 | 22.50 |
| 83 | 5 | coffee 3 in 1 | 15.0 | 13.50 |
| 84 | 73 | cake | 15.0 | 13.50 |
| 85 | 10 | seeds | 12.0 | 10.80 |
| 86 | 54 | paper bag | 1.0 | 0.90 |
87 rows × 4 columns
18.6 Розгортання списків: UNNEST()
Давайте знову попрацюємо з масивами і освоїмо нову функцію unnest, яка стане нам у нагоді в подальших завданнях. Функція unnest призначена для розгортання масивів та перетворення їх на набір рядків:
SELECT unnest(ARRAY['one','two','three'])
Результат:
one
two
threeУ прикладі вище функція unnest перетворила вихідний список із трьох елементів на набір із трьох рядків.
Якби у вихідній таблиці крім списку був стовпець з будь-яким значенням, то це значення автоматично проставилося б навпроти значень у кожному рядку, що утворився:
SELECT 'row', unnest(ARRAY['one','two','three'])
Результат:
row one
row two
row threeЗавдання 18.12
Виберіть всі колонки з таблиці orders, але як останню колонку вкажіть функцію unnest, застосовану до колонки product_ids. Нову колонку назвіть product_id. Виведіть лише перші 100 записів результуючої таблиці. Подивіться результат роботи unnest і постарайтеся розібратися, що сталося з вихідною таблицею.
Поля в результуючій таблиці: creation_time, order_id, product_ids, product_id
Рішення
%%sql
SELECT creation_time,
order_id,
product_ids,
unnest(product_ids) as product_id
FROM orders
LIMIT 100| creation_time | order_id | product_ids | product_id | |
|---|---|---|---|---|
| 0 | 2022-08-24 01:52:00 | 1 | [65, 28] | 65 |
| 1 | 2022-08-24 01:52:00 | 1 | [65, 28] | 28 |
| 2 | 2022-08-24 06:37:00 | 2 | [35, 30, 42, 34] | 35 |
| 3 | 2022-08-24 06:37:00 | 2 | [35, 30, 42, 34] | 30 |
| 4 | 2022-08-24 06:37:00 | 2 | [35, 30, 42, 34] | 42 |
| ... | ... | ... | ... | ... |
| 95 | 2022-08-24 18:57:00 | 30 | [9, 32, 42, 8] | 32 |
| 96 | 2022-08-24 18:57:00 | 30 | [9, 32, 42, 8] | 42 |
| 97 | 2022-08-24 18:57:00 | 30 | [9, 32, 42, 8] | 8 |
| 98 | 2022-08-24 18:58:00 | 31 | [77, 67, 25, 23] | 77 |
| 99 | 2022-08-24 18:58:00 | 31 | [77, 67, 25, 23] | 67 |
100 rows × 4 columns
А тепер застосуємо unnest для вирішення практичного завдання.
Завдання 18.13
Використовуючи функцію unnest, визначте 10 найпопулярніших товарів у таблиці orders. Найпопулярнішими вважатимемо ті, які зустрічалися в замовленнях найчастіше. Якщо товар зустрічається в одному замовленні кілька разів (тобто було куплено кілька одиниць товару), це теж враховується при підрахунку.
Виведіть id товарів та скільки разів вони зустрічалися в замовленнях. Нову колонку з кількістю покупок товару назвіть times_purchased.
Поля у результуючій таблиці: product_id, times_purchased
Примітка
У цьому завданні необхідно спочатку розгорнути списки з товарами в замовленнях, а потім для кожного товару порахувати, скільки разів він зустрівся в даних. Для визначення найпопулярніших товарів використовуйте оператор LIMIT.
Рішення
%%sql
SELECT unnest(product_ids) as product_id,
count(*) as times_purchased
FROM orders
GROUP BY product_id
ORDER BY times_purchased desc
LIMIT 10І насамкінець ще пара завдань зі зірочкою, щоб точно переконатися, що ми розібралися з підзапитами.
Завдання 18.14
З таблиці orders виведіть id та вміст замовлень, які включають хоча б один із п’яти найдорожчих товарів, доступних у нашому сервісі. Результат відсортуйте за зростанням id замовлення.
Поля у результуючій таблиці: order_id, product_ids
Рішення
%%sql
with top_products as (SELECT product_id
FROM products
ORDER BY price desc limit 5),
unnest as (SELECT order_id,
product_ids,
unnest(product_ids) as product_id
FROM orders)
SELECT DISTINCT order_id,
product_ids
FROM unnest
WHERE product_id in (SELECT *
FROM top_products)
ORDER BY order_id
-- або з `exists`
-- SELECT order_id,
-- product_ids
-- FROM orders
-- WHERE exists (
-- SELECT 1
-- FROM unnest(product_ids) as product_id
-- WHERE product_id in (
-- SELECT product_id
-- FROM products
-- ORDER BY price desc
-- LIMIT 5
-- )
-- )
-- ORDER BY order_idЗавдання 18.15
Порахуйте вік кожного користувача у таблиці users. Вік виміряйте числом повних років, як ми робили на минулих уроках. Вік рахуйте щодо останньої дати в таблиці user_actions. У результаті включіть колонки з id користувача та віком. Для тих користувачів, у яких у таблиці users не вказано дату народження, вкажіть середнє значення віку решти користувачів, округлене до цілого числа. Колонку з віком назвіть age. Результат відсортуйте за зростанням id користувача.
Поля у результуючій таблиці: user_id, age
Примітка
У цьому завдання вам доведеться написати кілька підзапитів і, можливо, використовувати табличні вирази. Придадуться функції DATE_PART, AGE та COALESCE. Основна складність полягає у заповненні перепусток середнім значенням — подумайте, як це можна зробити, та побудуйте запит навколо свого підходу.
Рішення
%%sql
with users_age as (
SELECT user_id,
date_part('year', age((SELECT max(time) FROM user_actions), birth_date)) as age
FROM users)
SELECT user_id,
coalesce(age, (SELECT round(avg(age))
FROM users_age)) as age
FROM users_age
ORDER BY user_id
-- або без `coalesce`
-- with max_time as (
-- SELECT max(time) as max
-- FROM user_actions
-- ),
-- user_age as (
-- SELECT user_id,
-- DATE_PART('year', age((SELECT max FROM max_time), birth_date)) as age
-- FROM users
-- ),
-- user_avg_age as (
-- SELECT AVG(age)::integer as avg_age
-- FROM user_age
-- )
--
-- SELECT user_id,
-- case when age IS NULL THEN (SELECT avg_age FROM user_avg_age)
-- ELSE age
-- end
-- FROM user_age
-- ORDER BY user_id