Теперь вы готовы погрузиться в мир практических примеров, чтобы увидеть, как всё это работает в реальных задачах!
Пример: рассчет ранга продаж по регионам
Давайте представим, что у нас есть таблица 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
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ