Рішення
%%sql
SELECT sex,
count(courier_id) as couriers_count
FROM couriers
GROUP BY sex
ORDER BY couriers_count| sex | couriers_count | |
|---|---|---|
| 0 | female | 1149 |
| 1 | male | 1674 |
17 Групування даних
17.1 Оператор GROUP BY
Для групування даних в SQL використовується оператор GROUP BY. Він дозволяє групувати дані по одному або декільком стовпцям таблиці. При цьому вибірка даних буде містити лише унікальні значення з вказаних стовпців:
- Спочатку в таблиці визначаються рядки, в яких у вказаному в
GROUP BYстовпці є однакові значення. - Далі за цими значеннями записи об’єднуються у групи, причому у групі може бути навіть один запис.
- Після цього над елементами цих груп, як правило, проводяться якісь операції за допомогою агрегатних функцій: наприклад, за допомогою
SUM()обчислюється сума значень в якому-небудь стовпці в кожній групі:
SELECT column_1, SUM(column_2)
FROM table
GROUP BY column_1- Потім у результуючий запит потрапляє по одному унікальному запису зі стовпця, по якому здійснювалося групування. При цьому навпроти кожного такого значення відображається результат агрегації по відповідній групі, якщо агрегація проводилася.
Тут важливо зробити кілька уточнень:
- По-перше, групування виконується після фільтрації, тобто спочатку виконуються інструкції
WHEREі лише потім дані групуються черезGROUP BY. - По-друге, до груп, що утворилися в результаті застосування
GROUP BY, можна застосовувати відразу кілька агрегатних функцій (у тому числі до різних колонок). - По-третє, групування можна робити відразу за новими полями, порахованими в
SELECT: при цьому допускається використання вGROUP BYаліасу колонки, зазначеного в SELECT. Наступні два запити дадуть однаковий результат:
SELECT DATE(column_1) AS date, SUM(column_2)
FROM table
GROUP BY DATE(column_1)
SELECT DATE(column_1) AS date, SUM(column_2)
FROM table
GROUP BY date- По-четверте, робити агрегацію після групування необов’язково. Якщо не вказувати агрегатну функцію, то запит поверне унікальні значення в стовпці, тобто той самий результат, як і оператор
DISTINCT. Можете самостійно запустити наступний запит та переконатися:
SELECT user_id
FROM user_actions
GROUP BY user_id
SELECT DISTINCT user_id
FROM user_actionsДо речі, питання про те, як можна відібрати унікальні значення, досвідчені аналітики люблять ставити на співбесідах своїм менш досвідченим колегам.
І нарешті, останнє важливе уточнення: під час використання групування колонки, вказаних у SELECT, повинні бути і GROUP BY, якщо вони використовуються у агрегатних функціях. Це обов’язкова умова, і якщо вона не буде виконана, база даних поверне помилку.
Наступний запит не працюватиме, оскільки в GROUP BY вказані не всі неагреговані колонки з блоку SELECT:
SELECT column_1, column_2, SUM(column_3)
FROM table
GROUP BY column_1Водночас такий запит спрацює:
SELECT SUM(column_2)
FROM table
GROUP BY column_1Зверніть увагу, що у цьому запиті у блоці SELECT немає колонки, вказаної у GROUP BY, тобто у зворотний бік правило не працює: якщо ми щось вказали в GROUP BY, це не обов’язково вказувати в SELECT. Інакше кажучи, виводити найменування груп необов’язково.
І ще: замість назв колонок у блоці GROUP BY можна використовувати номер колонки, вказаної у SELECT. Наприклад, наступні два запити еквівалентні:
SELECT column_1, column_2, SUM(column_3)
FROM table
GROUP BY column_1, column_2
SELECT column_1, column_2, SUM(column_3)
FROM table
GROUP BY 1, 2При цьому номери колонок із SELECT також можна використовувати при сортуванні в операторі ORDER BY. Можете самі поекспериментувати із цим у наступних завданнях.
З теорією начебто розібралися, тепер приступимо до практики. Минулого разу ми вже рахували кількість кур’єрів жіночої статі та використовували для цього фільтрацію. Групування дозволить нам провести розрахунки відразу для двох статей.
Завдання 17.1
За допомогою групування порахуйте кількість кур’єрів чоловічої та жіночої статі у таблиці couriers. Нову колонку з числом кур’єром назвіть couriers_count. Результат відсортуйте по цій колонці за зростанням.
Поля у результуючій таблиці: sex, couriers_count
Тепер давайте аналогічним чином з’ясуємо, скільки всього було зроблено замовлень і скільки було скасовано за весь час роботи нашого сервісу.
Завдання 17.2
Порахуйте кількість створених та скасованих замовлень у таблиці user_actions.
Нову колонку з кількістю замовлень назвіть orders_count.
Результат відсортуйте за кількістю замовлень щодо зростання.
Поля у результуючій таблиці: action, orders_count
Рішення
%%sql
SELECT action,
count(order_id) as orders_count
FROM user_actions
GROUP BY action
ORDER BY orders_count| action | orders_count | |
|---|---|---|
| 0 | cancel_order | 2979 |
| 1 | create_order | 59595 |
З простими завданнями на групування впоралися, тепер спробуємо зробити щось цікавіше: згрупуємо наші дані не по колонці, що є в таблиці, а по розрахунковій.
Щоб зрозуміти, як це працює, просто уявіть, що спочатку ви створюєте якусь нову колонку на основі вже наявної, застосовуючи до неї якусь функцію, а потім відразу в цьому ж запиті здійснюйте групування за новою колонкою.
На першому кроці ми вже розглядали приклад із DATE — давайте розглянемо ще один:
SELECT UPPER(column_1) AS upper_column, SUM(column_2) AS sum
FROM table
GROUP BY UPPER(column_1)Тут спочатку розрахували колонку upper_column, застосувавши функцію UPPER до колонки column_1, а потім відразу ж згрупувалися по ній, порахувавши суму всіх значень в колонці column_2 в кожній групі.
Нагадаємо, що такий варіант запиту також спрацює:
SELECT UPPER(column_1) AS upper_column, SUM(column_2) AS sum
FROM table
GROUP BY upper_columnА тепер, використовуючи всі ці знання, давайте порахуємо, скільки замовлень було зроблено кожного місяця. Зрозуміло, для цього нам потрібно якось отримати місяць із кожної дати. Це можна було б зробити за допомогою функції DATE_PART, але на цей раз для роботи з датами спробуємо нову функцію DATE_TRUNC.
Функція DATE_TRUNC використовується усічення дат і часу, тобто вона працює аналогічно округленню ROUND, тільки для типів даних TIMESTAMP та INTERVAL.
Синтаксис у неї такий самий, як і у DATE_PART:
SELECT DATE_TRUNC(part, column)На місці part у лапках вказується, до якої точності слід округляти передане значення часу: 'year', 'month', 'day', 'hour' тощо.
Значення, що повертається, має тип TIMESTAMP або INTERVAL, а всі «частини» вхідного значення, менш значущі, ніж задана «частина», прирівнюються до нуля (або одиниці, якщо це номер дня або місяця):
SELECT DATE_TRUNC('month', TIMESTAMP '2022-01-12 08:55:30')
Результат:
01/01/22 00:00
SELECT DATE_TRUNC('day', TIMESTAMP '2022-01-12 08:55:30')
Результат:
12/01/22 00:00
SELECT DATE_TRUNC('hour', TIMESTAMP '2022-01-12 08:55:30')
Результат:
12/01/22 08:00 Завдання 17.3
Використовуючи групування та функцію DATE_TRUNC, приведіть усі дати до початку місяця та порахуйте, скільки замовлень було зроблено у кожному з них.
Розрахунки проведіть по таблиці orders. Колонку з усіченою датою назвіть month, колонку з кількістю замовлень - orders_count.
Результат відсортуйте за місяцями – за зростанням.
Поля у результуючій таблиці: month, orders_count
Пояснення:
Зверніть увагу, що у цій задачі вам необхідно провести групування за новим розрахунковим полем.
У цій ситуації важливо пам’ятати, що колонки, вказані в SELECT, повинні знаходитись і в GROUP BY (якщо вони не використовуються в агрегаційних функціях). При цьому GROUP BY допускається використання аліасу колонки, зазначеного в блоці SELECT, тобто повторно проводити обчислення у GROUP BY не обов’язково.
Рішення
%%sql
SELECT date_trunc('month', creation_time) as month,
count(order_id) as orders_count
FROM orders
GROUP BY month
ORDER BY month| month | orders_count | |
|---|---|---|
| 0 | 2022-08-01 | 18799 |
| 1 | 2022-09-01 | 40796 |
Тепер давайте аналогічним чином порахуємо максимальний вік користувачів чоловічої та жіночої статі. Тільки на цей раз виведемо не повний вік, а лише кількість повних років. Для цього до результату обчислень можна застосувати вже знайому нам функцію DATE_PART з аргументом 'year'.
Завдання 17.4
Порахуйте максимальний вік користувачів чоловічої та жіночої статі у таблиці users. Вік виміряйте кількістю повних років. Нову колонку з віком назвіть max_age. Результат відсортуйте за новою колонкою за зростанням віку.
Поля у результуючій таблиці: sex, max_age
Рішення
%%sql
SELECT sex,
date_part('year', max(age(birth_date))) as max_age
FROM users
GROUP BY sex
ORDER BY max_age| sex | max_age | |
|---|---|---|
| 0 | female | 40 |
| 1 | male | 41 |
Розрахункові колонки можна використовувати не тільки для того, щоб групувати дані. Їх також можна використовувати як поля, якими робиться агрегація всередині груп.
Іншими словами, агрегацію не обов’язково проводити за наявними колонками — колонки можна «створювати» в рамках того ж запиту, в якому відбувається групування:
SELECT column_1, MIN(DATE_TRUNC('month', column_2)) AS min_month
FROM table
GROUP BY column_1У прикладі вище за допомогою функції DATE_TRUNC ми спочатку привели всі дати в колонці column_2 до початку місяця (отримавши по суті нову колонку), а потім відразу ж провели агрегацію по новій колонці, порахувавши мінімальну заокруглену дату в кожній групі, сформованій на основі колонки column_1.
Зверніть увагу, що той самий результат ми могли б отримати і за допомогою такого запиту:
SELECT column_1, DATE_TRUNC('month', MIN(column_2)) AS min_month
FROM table
GROUP BY column_1Виходить, що і до результату агрегатної функції можна відразу ж застосовувати інші функції.
При цьому в прикладах вище не має значення, в якому порядку проводити обчислення: ми можемо спочатку округлити дати, а потім знайти серед них мінімальну, або спочатку знайти мінімальну дату і потім округлити її.
Але потрібно бути уважними: часто результат залежить від того, в якому порядку застосовуються звичайні та агрегатні функції. Наприклад, наступні два запити дадуть різний результат:
SELECT column_1, MIN(DATE_PART('month', column_2)) AS min_month
FROM table
GROUP BY column_1
SELECT column_1, DATE_PART('month', MIN(column_2)) AS min_month
FROM table
GROUP BY column_1У першому запиті з кожної дати в колонці column_2 ми спочатку виділяємо всі порядкові номери місяців, а потім у кожній групі знаходимо серед них мінімальний. У другому запиті спочатку визначається мінімальна дата у кожній групі, а потім обчислюється порядковий номер місяця у цій даті.
Зрозуміло, що результат може відрізнятися, оскільки в даних можуть бути дати за різні роки — в самому ранньому році можуть не виявитися дати з деякими місяцями, тоді як у пізніших роках дати з цими місяцями будуть. Через це і можлива ситуація, коли запити даватимуть різний результат.
Давайте розглянемо такі випадки на практиці.
Завдання 17.5
За даними таблиці users порахуйте максимальний порядковий номер місяця серед усіх порядкових номерів місяців народження користувачів сервісу. За допомогою групування проведіть розрахунки окремо у двох групах – для користувачів чоловічої та жіночої статі.
Нову колонку з максимальним номером місяця народження у групах назвіть max_month. Перетворіть значення у новій колонці у формат INTEGER, щоб порядковий номер був виражений цілим числом.
Результат відсортуйте по колонці зі статтю користувачів.
Поля у результуючій таблиці: sex, max_month
Пояснення:
Для отримання порядкового номера місяця з дати стане в нагоді функція DATE_PART.
Рішення
%%sql
SELECT sex,
max(date_part('month', birth_date))::integer as max_month
FROM users
GROUP BY sex
ORDER BY sex| sex | max_month | |
|---|---|---|
| 0 | female | 12 |
| 1 | male | 12 |
А тепер давайте трохи змінимо логіку попереднього запиту.
Завдання 17.6
За даними у таблиці users порахуйте порядковий номер місяця народження наймолодшого користувача сервісу. За допомогою групування проведіть розрахунки окремо у двох групах – для користувачів чоловічої та жіночої статі.
Нову колонку з максимальним номером місяця народження у групах назвіть max_month. Перетворіть значення у новій колонці у формат INTEGER, щоб порядковий номер був виражений цілим числом.
Результат відсортуйте по колонці зі статтю користувачів.
Поля у результуючій таблиці: sex, max_month
Пояснення:
Для отримання порядкового номера місяця з дати стане в нагоді функція DATE_PART.
Після того як розв’яжете завдання, зверніть увагу на отриманий результат. Чи співпаде він із результатом попереднього запиту? Чи це схоже на той випадок, який був описаний на попередньому кроці?
Рішення
%%sql
SELECT sex,
date_part('month', max(birth_date))::integer as max_month
FROM users
GROUP BY sex
ORDER BY sex| sex | max_month | |
|---|---|---|
| 0 | female | 6 |
| 1 | male | 9 |
Маркетологи знову звернулися до нас із завданням: цього разу вони просять провести невеликий аналіз нашої аудиторії та порахувати, скільки клієнтів певного віку користуються нашим сервісом. Давайте допоможемо нашим колегам!
Завдання 17.7
Розбийте користувачів з таблиці users на групи віком (вік вимірюємо кількістю повних років) і порахуйте кількість користувачів кожного віку. Колонку з віком назвіть age, а колонку з кількістю користувачів users_count. Відсортуйте отриманий результат за зростанням по віку. Не забудемо і про тих користувачів, у яких замість віку буде перепустки, для цієї групи також підрахуємо кількість користувачів.
Поля у результуючій таблиці: age, users_count
Пояснення: зверніть увагу, що у цій задачі вам необхідно провести групування за новим розрахунковим полем з віком. У цій ситуації важливо пам’ятати, що колонки, вказані в SELECT, повинні знаходитись і в GROUP BY (якщо вони не використовуються в агрегатних функціях). При цьому GROUP BY допускається використання аліасу колонки, зазначеного в блоці SELECT, тобто повторно проводити обчислення у GROUP BY не обов’язково.
Рішення
%%sql
SELECT date_part('year', age(birth_date)) as age,
count(user_id) as users_count
FROM users
GROUP BY age
ORDER BY age
LIMIT 10| age | users_count | |
|---|---|---|
| 0 | 19 | 1 |
| 1 | 20 | 1 |
| 2 | 21 | 7 |
| 3 | 22 | 26 |
| 4 | 23 | 78 |
| 5 | 24 | 195 |
| 6 | 25 | 421 |
| 7 | 26 | 840 |
| 8 | 27 | 1397 |
| 9 | 28 | 2077 |
Ви могли помітити, що результат попереднього запиту для однієї з груп повернув пусте значення віку. Ми знову зіткнулися з значеннями NULL — цього разу в колонці birth_date. Давайте позбавимося їх перед групуванням і заодно зробимо наш аналіз ще більш детальним: додамо до групування стать користувачів.
Завдання 17.8
Знову розбийте користувачів із таблиці users на групи за віком (вік вимірюємо кількістю повних років), тільки тепер додайте до групування стать користувача. В результаті в кожній віковій групі має з’явитися ще по дві підгрупи зі статтю. У кожній такій підгрупі порахуйте кількість користувачів.
Усі значення NULL у колонці birth_date заздалегідь відфільтруйте за допомогою WHERE. Колонку з віком назвіть age, а колонку з числом користувачів -users_count`, ім’я колонки зі статтю залиште без змін. Відсортуйте отриману таблицю спочатку по колонці з віком по зростанню, потім по колонці з підлогою теж по зростанню.
Поля у результуючій таблиці: age, sex, users_count
Рішення
%%sql
SELECT date_part('year', age(birth_date)) as age,
sex,
count(user_id) as users_count
FROM users
WHERE birth_date is not null
GROUP BY age, sex
ORDER BY age, sex
LIMIT 10| age | sex | users_count | |
|---|---|---|---|
| 0 | 19 | male | 1 |
| 1 | 20 | male | 1 |
| 2 | 21 | female | 4 |
| 3 | 21 | male | 3 |
| 4 | 22 | female | 10 |
| 5 | 22 | male | 16 |
| 6 | 23 | female | 39 |
| 7 | 23 | male | 39 |
| 8 | 24 | female | 105 |
| 9 | 24 | male | 90 |
А тепер, використовуючи наші знання про групування, давайте порахуємо скільки замовлень було зроблено і скільки скасовано в кожному окремому місяці. На цей раз для роботи з датами будемо використовувати не DATE_PART, а нову функцію DATE_TRUNC.
Функція DATE_TRUNC використовується усічення дат і часу, тобто вона працює аналогічно округленню ROUND, тільки для типів даних TIMESTAMP та INTERVAL.
Синтаксис у неї такий самий, як і у DATE_PART:
SELECT DATE_TRUNC(part, column)На місці part у лапках вказується, до якої точності слід обрізати передане значення часу: 'year', 'month', 'day', 'hour’ тощо.
Отримані значення мають тип TIMESTAMP або INTERVAL, а всі «частини» вихідного значення, менш значущі, ніж задана «частина», прирівнюються до нуля (або одиниці, якщо це номер дня або місяця):
SELECT DATE_TRUNC('month', TIMESTAMP '2022-01-12 08:55:30')
Результат:
2022-01-01 00:00
SELECT DATE_TRUNC('day', TIMESTAMP '2022-01-12 08:55:30')
Результат:
2022-01-12 00:00
SELECT DATE_TRUNC('hour', TIMESTAMP '2022-01-12 08:55:30')
Результат:
2022-01-12 08:00
Примітка
Докладніше з функцією DATE_TRUNC можна ознайомитись тут.
Завдання 17.9
Використовуючи функцію DATE_TRUNC, порахуйте, скільки замовлень було зроблено та скільки було скасовано кожного місяця. Розрахунки проводьте за таблицею user_actions. Колонку з усіченою датою назвіть month, колонку з кількістю замовлень - orders_count. Результат відсортуйте спочатку за місяцями – за зростанням, потім за типом дії – також за зростанням.
Поля в результуючій таблиці: month, action, orders_count
Рішення
%%sql
SELECT date_trunc('month', time) as month,
action,
count(order_id) as orders_count
FROM user_actions
GROUP BY month, action
ORDER BY month, action| month | action | orders_count | |
|---|---|---|---|
| 0 | 2022-08-01 | cancel_order | 967 |
| 1 | 2022-08-01 | create_order | 18799 |
| 2 | 2022-09-01 | cancel_order | 2012 |
| 3 | 2022-09-01 | create_order | 40796 |
У Завдання 16.9 ми навчилися працювати з функцією array_length і навіть порахували з її допомогою кількість товарів у кожному замовленні. Давайте для кожного розміру замовлення, яке зустрічається у даних, порахуємо загальну кількість замовлень такого розміру.
Завдання 17.10
Порахуйте кількість товарів у кожному замовленні з таблиці orders, застосуйте до цих значень групування та порахуйте кількість замовлень у кожній групі. Виведіть дві колонки: кількість товарів у замовленні та кількість замовлень з такою кількістю. Колонки назвіть відповідно order_size та orders_count. Результат відсортуйте за зростанням кількості товарів у замовленні.
Поля у результуючій таблиці: order_size, orders_count
Рішення
%%sql
SELECT array_length(product_ids, 1) as order_size,
count(order_id) as orders_count
FROM orders
GROUP BY order_size
ORDER BY order_size| order_size | orders_count | |
|---|---|---|
| 0 | 1 | 3333 |
| 1 | 2 | 11851 |
| 2 | 3 | 17856 |
| 3 | 4 | 15057 |
| 4 | 5 | 8065 |
| 5 | 6 | 2774 |
| 6 | 7 | 583 |
| 7 | 8 | 71 |
| 8 | 9 | 5 |
17.2 Фільтрація після групування: HAVING
HAVING використовується для фільтрації даних після групування. Він працює аналогічно WHERE, тільки використовується після GROUP BY і фільтрує вже групи, а не окремі записи.
Але треба обговорити одну важливу особливість роботи з HAVING: у цьому блоці не можна вказувати аліаси розрахункових полів із блоку SELECT. Справа в тому, що відповідно до порядку виконання запиту оператор SELECT виконується після оператора HAVING. Тому агрегацію необхідно також вказувати і HAVING.
Наприклад, такий запит не спрацює:
SELECT column_1, SUM(column_2) AS new_column
FROM table
GROUP BY column_1
HAVING new_column = 10А такий спрацює:
SELECT column_1, SUM(column_2) AS new_column
FROM table
GROUP BY column_1
HAVING SUM(column_2) = 10Завдання 17.11
Доповніть попередній запит оператором HAVING та відберіть лише ті розміри замовлень, загальна кількість яких перевищує 5000. Знову виведіть дві колонки: кількість товарів у замовленні та кількість замовлень з такою кількістю. Колонки назвіть відповідно order_size та orders_count. Результат відсортуйте за зростанням кількості товарів у замовленні.
Поля у результуючій таблиці: order_size, orders_count
Рішення
%%sql
SELECT array_length(product_ids, 1) as order_size,
count(order_id) as orders_count
FROM orders
GROUP BY order_size
HAVING count(order_id) > 5000
ORDER BY order_size| order_size | orders_count | |
|---|---|---|
| 0 | 2 | 11851 |
| 1 | 3 | 17856 |
| 2 | 4 | 15057 |
| 3 | 5 | 8065 |
Перед тим, як рухатися далі, пропонуємо вам вирішити ще кілька завдань на групування.
Завдання 17.12
З таблиці courier_actions відберіть id трьох кур’єрів, які доставили найбільшу кількість замовлень у серпні 2022 року. Виведіть дві колонки – id кур’єра та кількість доставлених замовлень. Назвіть колонку з кількістю доставлених замовлень delivered_orders. Відсортуйте результат за спаданням delivered_orders.
Поля у результуючій таблиці: courier_id, delivered_orders
Пояснення: пам’ятайте, що у таблиці courier_actions є інформація як про прийняті, так і про доставлені замовлення.
Рішення
%%sql
SELECT courier_id,
count(courier_id) as delivered_orders
FROM courier_actions
WHERE action = 'deliver_order'
and date_part('year', time) = 2022
and date_part('month', time) = 08
GROUP BY courier_id
ORDER BY delivered_orders desc
LIMIT 3| courier_id | delivered_orders | |
|---|---|---|
| 0 | 179 | 36 |
| 1 | 329 | 34 |
| 2 | 103 | 31 |
Завдання 17.13
А тепер відберіть лише тих кур’єрів, які у вересні 2022 року встигли доставити тільки за одне замовлення. Таблиця та сама - courier_actions. Знову виведіть дві колонки – id кур’єра та кількість доставлених замовлень. Назвіть колонку з числом замовлень delivered_orders. Результат відсортуйте за зростанням id кур’єра.
Поля у результуючій таблиці: courier_id, delivered_orders
Рішення
%%sql
SELECT courier_id,
count(distinct courier_id) as delivered_orders
FROM courier_actions
WHERE action = 'deliver_order'
and date_part('year', time) = 2022
and date_part('month', time) = 09
GROUP BY courier_id having count(distinct courier_id) = 1
ORDER BY courier_id| courier_id | delivered_orders | |
|---|---|---|
| 0 | 1 | 1 |
| 1 | 2 | 1 |
| 2 | 3 | 1 |
| 3 | 4 | 1 |
| 4 | 5 | 1 |
| ... | ... | ... |
| 2817 | 3163 | 1 |
| 2818 | 3164 | 1 |
| 2819 | 3165 | 1 |
| 2820 | 3166 | 1 |
| 2821 | 3167 | 1 |
2822 rows × 2 columns
Ой, до нас у кабінет знову постукали! Це знову маркетологи: кажуть, що хочуть розіслати пуш-повідомлення зі спеціальною пропозицією. Аудиторія – користувачі, які давно не робили у нас замовлення.
Завдання 17.14
З таблиці user_actions відберіть користувачів, у яких останнє замовлення було створено до 8 вересня 2022 року. Виведіть тільки їх ID, дату створення замовлення виводити не потрібно. Результат відсортуйте за зростанням id користувача.
Поле у результуючій таблиці: user_id
Підказка
Щоб розв’язати завдання, потрібно спочатку для кожного користувача знайти останній TIMESTAMP, коли він робив замовлення, а потім відібрати тих, хто робив це занадто давно. Враховуйте, що в таблиці є не лише час створення, а й час скасування замовлення.
Рішення
%%sql
SELECT DISTINCT user_id
FROM user_actions
WHERE action = 'create_order'
GROUP BY user_id
HAVING max(time) < '2022-09-08'
ORDER BY user_id| user_id | |
|---|---|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| ... | ... |
| 17368 | 19727 |
| 17369 | 19728 |
| 17370 | 19729 |
| 17371 | 19730 |
| 17372 | 19731 |
17373 rows × 1 columns
В якості поля для групування може виступати і складніша розрахункова колонка — наприклад, результат виконання умовної конструкції CASE:
SELECT name,
CASE
WHEN name='свинина' OR name='баранина' OR name='курица' THEN 'мясо'
WHEN name='треска' OR name='форель' OR name='окунь' THEN 'рыба'
ELSE 'другое'
END AS сategory,
AVG(price) AS average_price
FROM table
GROUP BY сategoryЗапит вище спочатку поділить всі товари на групи, потім групує їх і для кожної групи рахуватиме середню ціну всіх продуктів, що потрапили до неї.
Виходить, що таким чином ми можемо додатково управляти групуванням наших даних — спочатку ми самі задаємо правила, за якими визначаємо належність записів до деяких категорій, а потім групуємо дані і проводимо необхідні розрахунки.
Давайте за допомогою групування та умовної конструкції CASE з’ясуємо, чи відрізняється середній розмір замовлення у будні та вихідні дні.
Завдання 17.15
За даними з таблиці orders розрахуйте середній розмір замовлення у вихідні та будні.
Групу з вихідними днями (субота та неділя) назвіть "weekend", а групу з будними днями (з понеділка по п’ятницю) - "weekdays" (без лапок).
У результаті включіть дві колонки: колонку з групами назвіть week_part, а колонку із середнім розміром замовлення - avg_order_size.
Середній розмір замовлення округліть до двох знаків після коми.
Результат відсортуйте по колонці із середнім розміром замовлення за зростанням.
Поля у результуючій таблиці: week_part, avg_order_size
Рішення
%%sql
SELECT case when extract('dow'
FROM creation_time) in (0, 6) then 'weekend' else 'weekdays' end as week_part, round(avg(array_length(product_ids, 1)), 2) as avg_order_size
FROM orders
GROUP BY week_part
ORDER BY avg_order_size
--або
-- SELECT case when to_char(creation_time, 'Dy') in ('Sat', 'Sun') then 'weekend'
-- else 'weekdays' end as week_part,
-- round(avg(array_length(product_ids, 1)), 2) as avg_order_size
-- FROM orders
-- GROUP BY week_part
-- ORDER BY avg_order_size| week_part | avg_order_size | |
|---|---|---|
| 0 | weekend | 3.39 |
| 1 | weekdays | 3.40 |
А тепер спробуємо вирішити складніше завдання. Для нього нам знову знадобиться агрегатний вираз із фільтрацією, який ми розглядали у Завдання 16.16. Цю конструкцію можна застосовувати не лише до всієї таблиці, а й окремо до кожної групи, сформованої внаслідок застосування оператора GROUP BY. Загалом вона виглядатиме так:
SELECT column_1, agg_function(column_2) FILTER (WHERE [condition])
FROM table
GROUP BY column_1Приклад:
SELECT column_1, AVG(column_2) FILTER (WHERE column_3 > 100)
FROM table
GROUP BY column_1Завдання 17.16
Для кожного користувача в таблиці user_actions порахуйте частку скасованих замовлень. Виведіть дві колонки: ID користувача та розрахований показник. Нову колонку з показником округліть до двох знаків після коми та назвіть cancel_rate. Результат відсортуйте за зростанням id користувача.
Поля у результуючій таблиці: user_id, cancel_rate
Пояснення: щоб порахувати частку скасованих замовлень, необхідно поділити кількість скасованих замовлень на загальну кількість унікальних замовлень користувача. Пам’ятайте, що для отримання коректного результату поділу потрібно хоча б одне із значень привести до типу DECIMAL.
Рішення
%%sql
SELECT user_id,
round(count(action) filter (WHERE action = 'cancel_order')::decimal / count(action) filter (WHERE action = 'create_order'),
2) as cancel_rate
FROM user_actions
GROUP BY user_id
ORDER BY user_id| user_id | cancel_rate | |
|---|---|---|
| 0 | 1 | 0.0 |
| 1 | 2 | 0.0 |
| 2 | 3 | 0.0 |
| 3 | 4 | 0.0 |
| 4 | 5 | 0.0 |
| ... | ... | ... |
| 21396 | 21398 | 0.0 |
| 21397 | 21399 | 0.0 |
| 21398 | 21400 | 0.0 |
| 21399 | 21401 | 0.0 |
| 21400 | 21402 | 0.0 |
21401 rows × 2 columns
Повернімося до запиту з групуванням користувачів за віком, який робили у завданні 16.3.
Ми порахували кількість користувачів кожного віку, але дивитися на дані саме в такому угрупованні не дуже цікаво. Давайте перейдемо від конкретних значень віку до вікових груп.
Завдання 17.17
Розбийте користувачів із таблиці users на 4 вікові групи:
- від 19 до 24 років;
- від 25 до 29 років;
- від 30 до 35 років;
- від 36 до 41 року.
Порахуйте кількість користувачів, які потрапили до кожної вікової групи. Групи назвіть відповідно 19-24, 25-29, 30-35, 36-41. Виведіть найменування груп та кількість користувачів у них. Назвіть колонку з найменуванням груп group_age, а колонку з числом користувачів — users_count. Відсортуйте отриману таблицю по колонці з найменуванням груп за зростання.
Поля у результуючій таблиці: group_age, users_count
Пояснення: для вирішення цього завдання підійде конструкція CASE. Як і минулого разу, вік розглядайте, як кількість повних років.
Рішення
%%sql
SELECT case when date_part('year', age(birth_date)) between 19 and
24 then '19-24'
when date_part('year', age(birth_date)) between 25 and
29 then '25-29'
when date_part('year', age(birth_date)) between 30 and
35 then '30-35'
when date_part('year', age(birth_date)) between 36 and
41 then '36-41' end as group_age,
count(user_id) as users_count
FROM users
WHERE birth_date is not null
GROUP BY group_age
ORDER BY group_age| group_age | users_count | |
|---|---|---|
| 0 | 19-24 | 308 |
| 1 | 25-29 | 7367 |
| 2 | 30-35 | 12002 |
| 3 | 36-41 | 604 |
І насамкінець ще одне цікаве завдання.
Спробуймо з’ясувати, чи відрізняється success rate (частка невідмінених замовлень) у різні дні тижня. Для цього порахуємо, скільки всього було оформлено замовлень у кожен із днів, скільки з цих замовлень було скасовано та скільки фактично було успішно доставлено до користувачів.
Завдання 17.18
Для кожного дня тижня у таблиці user_actions порахуйте:
- Загальна кількість оформлених замовлень.
- Загальна кількість скасованих замовлень.
- Загальна кількість невідмінених замовлень (тобто доставлених).
- Частку невідмінених замовлень у загальній кількості замовлень (success rate).
Нові колонки назвіть відповідно created_orders, canceled_orders, actual_orders та success_rate. Колонку з часткою невідмінених замовлень округліть до трьох знаків після коми.
Усі розрахунки проводьте за період з 24 серпня по 6 вересня 2022 року включно, щоб до часового інтервалу потрапила рівна кількість різних днів тижня.
Групи сформуйте таким чином: виділіть день тижня з дати за допомогою функції функції DATE_PART з параметром 'isodow'. Далі згрупуйте дані за двома полями та проведіть усі необхідні розрахунки.
В результаті має вийти групування за двома колонками: з порядковим номером днів тижня та їх скороченими найменуваннями.
Результат відсортуйте за зростанням порядкового номера тижня тижня.
Поля в результуючій таблиці: weekday_number, weekday, created_orders, canceled_orders, actual_orders, success_rate
Пояснення:
З метою спрощення розрахунків у рамках цього завдання вважаємо, що скасування замовлення відбувається практично відразу після його створення. Випадками, коли замовлення створюється незадовго до півночі, а скасування випадає наступного дня, ми нехтуємо.
Номер дня тижня ми додатково виділяємо для того, щоб одночасно вивести найменування та відсортувати всі записи відповідно до порядку днів тижня.
Пам’ятайте, що при розрахунку відносних показників для отримання коректного результату поділу потрібно хоча б одне із значень привести до типу DECIMAL.
Рішення
%%sql
SELECT
date_part('isodow', time)::int as weekday_number,
count(order_id) filter (WHERE action = 'create_order') as created_orders,
count(order_id) filter (WHERE action = 'cancel_order') as canceled_orders,
count(order_id) filter (WHERE action = 'create_order') - count(order_id) filter (WHERE action = 'cancel_order') as actual_orders,
round((count(order_id) filter (WHERE action = 'create_order') - count(order_id) filter (WHERE action = 'cancel_order'))::decimal / count(order_id) filter (WHERE action = 'create_order'),
3) as success_rate
FROM user_actions
WHERE time >= '2022-08-24'
and time < '2022-09-07'
GROUP BY weekday_number
ORDER BY weekday_number| weekday_number | created_orders | canceled_orders | actual_orders | success_rate | |
|---|---|---|---|---|---|
| 0 | 1 | 8374 | 434 | 7940 | 0.948 |
| 1 | 2 | 7193 | 370 | 6823 | 0.949 |
| 2 | 3 | 3758 | 210 | 3548 | 0.944 |
| 3 | 4 | 5004 | 258 | 4746 | 0.948 |
| 4 | 5 | 6800 | 352 | 6448 | 0.948 |
| 5 | 6 | 8249 | 399 | 7850 | 0.952 |
| 6 | 7 | 9454 | 443 | 9011 | 0.953 |