Тепер ти готовий зануритись у світ практичних прикладів, щоб побачити, як це все працює на реальних задачах!
Приклад: розрахунок рангу продажів по регіонах
Давай уявимо, що у нас є таблиця sales, яка містить дані про продажі в різних регіонах. Нам треба визначити ранги продажів для кожного регіону.
| id | region | sales_amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | North | 3000 |
| 3 | North | 7000 |
| 4 | South | 2000 |
| 5 | South | 4000 |
| 6 | East | 8000 |
| 7 | East | 6000 |
Задача: знайти ранг (RANK) продажів по кожному регіону
SELECT
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;
Результат:
| region | sales_amount | sales_rank |
|---|---|---|
| North | 7000 | 1 |
| North | 5000 | 2 |
| North | 3000 | 3 |
| South | 4000 | 1 |
| South | 2000 | 2 |
| East | 8000 | 1 |
| East | 6000 | 2 |
Зверни увагу, що ми використали PARTITION BY region, щоб порахувати ранги окремо для кожного регіону. Якби ми не використали PARTITION BY, ранг рахувався б глобально по всій таблиці.
Приклад: розрахунок накопичувальної суми доходу
Тепер давай обробимо таблицю transactions, щоб підрахувати накопичувальну (кумулятивну) суму доходу для кожного клієнта.
| id | customer_id | purchase_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 100 |
| 2 | 101 | 2023-01-03 | 50 |
| 3 | 102 | 2023-01-02 | 200 |
| 4 | 101 | 2023-01-05 | 150 |
| 5 | 102 | 2023-01-04 | 100 |
Задача: порахувати накопичувальну суму для кожного клієнта
SELECT
customer_id,
purchase_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM
transactions;
Результат:
| customer_id | purchase_date | amount | cumulative_sum |
|---|---|---|---|
| 101 | 2023-01-01 | 100 | 100 |
| 101 | 2023-01-03 | 50 | 150 |
| 101 | 2023-01-05 | 150 | 300 |
| 102 | 2023-01-02 | 200 | 200 |
| 102 | 2023-01-04 | 100 | 300 |
Тут головний момент — використання ORDER BY purchase_date всередині OVER(), щоб накопичувальна сума рахувалась у хронологічному порядку.
Приклад: розбиття даних на квантили
Уяви, що у нас є таблиця students, де вказані імена та результати тестів. Ми хочемо розділити учнів на 4 групи за їх результатами.
| id | name | test_score |
|---|---|---|
| 1 | Alice | 85 |
| 2 | Bob | 95 |
| 3 | Charlie | 75 |
| 4 | Diana | 88 |
| 5 | Edward | 65 |
| 6 | Fiona | 70 |
Задача: розділити студентів на 4 групи з використанням NTILE()
SELECT
name,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM
students;
Результат запиту буде такий:
| name | test_score | quartile |
|---|---|---|
| Bob | 95 | 1 |
| Diana | 88 | 1 |
| Alice | 85 | 2 |
| Charlie | 75 | 3 |
| Fiona | 70 | 3 |
| Edward | 65 | 4 |
NTILE(4) ділить дані на 4 групи. Студенти з найвищими балами потрапляють у першу групу, з найнижчими — у останню.
Приклад: аналіз часових даних
У таблиці site_visits зберігаються дані про кількість відвідувань сайту по днях. Нам треба порахувати різницю у кількості відвідувань між днями для кожного сайту.
| site_id | visit_date | visits |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 1 | 2023-01-02 | 120 |
| 1 | 2023-01-03 | 110 |
| 2 | 2023-01-01 | 50 |
| 2 | 2023-01-02 | 60 |
| 2 | 2023-01-03 | 70 |
Наша задача — порахувати різницю у відвідуваннях між днями
SELECT
site_id,
visit_date,
visits,
visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM
site_visits;
Результат:
| site_id | visit_date | visits | visit_diff |
|---|---|---|---|
| 1 | 2023-01-01 | 100 | NULL |
| 1 | 2023-01-02 | 120 | 20 |
| 1 | 2023-01-03 | 110 | -10 |
| 2 | 2023-01-01 | 50 | NULL |
| 2 | 2023-01-02 | 60 | 10 |
| 2 | 2023-01-03 | 70 | 10 |
Функція LAG() дозволяє отримати значення з попереднього рядка. Якщо даних для попереднього рядка нема, результат буде NULL. Детальніше про неї ти дізнаєшся у наступних лекціях :P
ПЕРЕЙДІТЬ В ПОВНУ ВЕРСІЮ