PARTITION в Oracle SQL — как использовать разбиение данных

PARTITION BY в Oracle SQL — как разбивать данные на группы для аналитики

PARTITION BY — это часть оконной функции (window function) в Oracle SQL, позволяющая разбивать результирующий набор данных на логические группы. Она даёт возможность выполнять агрегатные и аналитические вычисления внутри каждой группы, не сворачивая строки.

🖋️ Синтаксис PARTITION BY

функция_аналитики(...) OVER (PARTITION BY выражение [ORDER BY выражение])

Пример:

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

🔄 Где используется PARTITION BY в Oracle SQL

• Расчёт рангов внутри групп
• Подсчёт накопительных итогов по категориям
• Анализ активности по пользователям, департаментам
• Работа с временными окнами, лагами, скользящими средними
• Сравнение значений внутри подмножеств

📊 Примеры использования PARTITION BY в Oracle SQL (1–50 из 50)

1. Ранжирование зарплат внутри департаментов:

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees;

2. Подсчёт сотрудников в каждом отделе:

SELECT employee_id, department_id,
       COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;

3. Средняя зарплата по отделу:

SELECT employee_id, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

4. Накопительный итог по продажам клиента:

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative
FROM orders;

5. Сравнение текущего и предыдущего значения:

SELECT customer_id, order_date, amount,
       LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders;

6. Сравнение текущей и следующей продажи:

SELECT customer_id, order_date, amount,
       LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;

7. Количество логинов пользователя по дням:

SELECT user_id, login_date,
       COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) AS login_sequence
FROM user_logins;

8. Максимальный рейтинг в каждой категории:

SELECT product_id, category, rating,
       MAX(rating) OVER (PARTITION BY category) AS max_rating
FROM products;

9. Минимальная дата по аккаунту:

SELECT user_id, action_date,
       MIN(action_date) OVER (PARTITION BY user_id) AS first_activity
FROM user_activity;

10. Разница между текущей и максимальной суммой:

SELECT account_id, transaction_date, amount,
       MAX(amount) OVER (PARTITION BY account_id) - amount AS diff_to_max
FROM transactions;

11. Оценка места в соревновании:

SELECT player_id, game_id, score,
       DENSE_RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS place
FROM game_results;

12. Количество ошибок по модулю:

SELECT module, error_type,
       COUNT(*) OVER (PARTITION BY module) AS error_count
FROM error_logs;

13. Процент от общего по отделу:

SELECT employee_id, department_id, salary,
       ROUND(100 * salary / SUM(salary) OVER (PARTITION BY department_id), 2) AS percent_of_total
FROM employees;

14. Временной лаг в ответах:

SELECT ticket_id, agent_id, response_time,
       LAG(response_time) OVER (PARTITION BY agent_id ORDER BY ticket_id) AS prev_response
FROM support_responses;

15. Количество задач по исполнителю:

SELECT task_id, assigned_to,
       COUNT(*) OVER (PARTITION BY assigned_to) AS tasks_per_user
FROM tasks;

16. Сумма трат за неделю по пользователю:

SELECT user_id, week, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM spending;

17. Среднее время отклика по серверу:

SELECT server_id, timestamp, response_ms,
       AVG(response_ms) OVER (PARTITION BY server_id ORDER BY timestamp ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM server_logs;

18. Отметка о первом заказе:

SELECT customer_id, order_date,
       CASE WHEN order_date = MIN(order_date) OVER (PARTITION BY customer_id) THEN 'YES' ELSE 'NO' END AS is_first
FROM orders;

19. Позиция по рейтингу внутри региона:

SELECT user_id, region, score,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY score DESC) AS rank_in_region
FROM user_scores;

20. Накопление суммы за месяц:

SELECT user_id, TO_CHAR(purchase_date, 'MM') AS month, amount,
       SUM(amount) OVER (PARTITION BY user_id, TO_CHAR(purchase_date, 'MM')) AS monthly_total
FROM purchases;

21. Сравнение среднего и текущего значения:

SELECT department_id, employee_id, salary,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

22. Определение повторного логина:

SELECT user_id, login_date,
       CASE WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NOT NULL 
            THEN 'REPEAT' ELSE 'FIRST' END AS login_type
FROM user_logins;

23. Количество заказов за каждый день:

SELECT store_id, order_date,
       COUNT(*) OVER (PARTITION BY store_id ORDER BY order_date) AS daily_order_count
FROM store_orders;

24. Прирост по сравнению с предыдущим периодом:

SELECT product_id, year, sales,
       sales - LAG(sales) OVER (PARTITION BY product_id ORDER BY year) AS delta
FROM yearly_sales;

25. Кол-во логов по пользователю и типу:

SELECT user_id, log_type, event_time,
       COUNT(*) OVER (PARTITION BY user_id, log_type) AS count_per_type
FROM user_logs;

26. Максимальное значение по клиенту:

SELECT client_id, transaction_id, amount,
       MAX(amount) OVER (PARTITION BY client_id) AS max_client_amount
FROM client_transactions;

27. Общее количество записей по категории:

SELECT category, value,
       COUNT(*) OVER (PARTITION BY category) AS total_per_category
FROM items;

28. Минимум за окно из 3 строк:

SELECT account_id, op_time, balance,
       MIN(balance) OVER (PARTITION BY account_id ORDER BY op_time ROWS 2 PRECEDING) AS min_last3
FROM balances;

29. Первая строка в категории:

SELECT customer_id, region, purchase_date,
       FIRST_VALUE(purchase_date) OVER (PARTITION BY region ORDER BY purchase_date) AS first_in_region
FROM purchases;

30. Последнее значение:

SELECT order_id, customer_id, status,
       LAST_VALUE(status) OVER (PARTITION BY customer_id ORDER BY order_id ROWS 
       BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_status
FROM orders;

31. Сравнение с медианой по группе:

SELECT department_id, employee_id, salary,
       salary - MEDIAN(salary) OVER (PARTITION BY department_id) AS diff_from_median
FROM employees;

32. Общее количество покупок по клиенту:

SELECT customer_id, order_id,
       COUNT(order_id) OVER (PARTITION BY customer_id) AS total_orders
FROM orders;

33. Позиция строки по пользовательскому полю:

SELECT user_id, country,
       ROW_NUMBER() OVER (PARTITION BY country ORDER BY user_id) AS row_pos
FROM users;

34. Анализ посещаемости по дню недели:

SELECT site_id, visit_day,
       COUNT(*) OVER (PARTITION BY site_id, visit_day) AS daily_visits
FROM visits;

35. Кол-во уникальных покупок по клиенту:

SELECT customer_id, product_id,
       COUNT(DISTINCT product_id) OVER (PARTITION BY customer_id) AS unique_products
FROM purchases;

36. Сумма выплат по получателю:

SELECT recipient_id, payment_id, amount,
       SUM(amount) OVER (PARTITION BY recipient_id) AS total_received
FROM payments;

37. Количество сессий по устройству:

SELECT user_id, device_type,
       COUNT(*) OVER (PARTITION BY user_id, device_type) AS sessions_by_device
FROM sessions;

38. Доля заказов по каждому складу:

SELECT warehouse_id, region,
       ROUND(100 * COUNT(*) OVER (PARTITION BY region, warehouse_id) /
             COUNT(*) OVER (PARTITION BY region), 2) AS percent_share
FROM shipments;

39. Кол-во покупок до текущей:

SELECT customer_id, order_date,
       COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date ROWS 
       BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS past_orders
FROM orders;

40. Сумма продаж за неделю по филиалу:

SELECT branch_id, week_num, sale_id, amount,
       SUM(amount) OVER (PARTITION BY branch_id, week_num) AS weekly_branch_total
FROM weekly_sales;

41. Кол-во логинов за последние 7 дней:

SELECT user_id, login_date,
       COUNT(*) OVER (
         PARTITION BY user_id ORDER BY login_date
         RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
       ) AS logins_7d
FROM user_logins;

42. Доля сотрудника в бюджете отдела:

SELECT department_id, employee_id, budget,
       ROUND(budget / SUM(budget) OVER (PARTITION BY department_id), 2) AS share
FROM department_budgets;

43. Сводка визитов по странице:

SELECT page_id, user_id, visit_id,
       COUNT(*) OVER (PARTITION BY page_id) AS total_visits
FROM web_traffic;

44. Определение единственной записи в группе:

SELECT category, item_id,
       CASE WHEN COUNT(*) OVER (PARTITION BY category) = 1 THEN 'UNIQUE' ELSE 'DUPLICATE' END AS uniqueness
FROM catalog;

45. Вклад клиента в месячный оборот:

SELECT customer_id, month, amount,
       ROUND(100 * amount / SUM(amount) OVER (PARTITION BY month), 2) AS contribution
FROM revenue;

46. Кол-во товаров по поставщику:

SELECT supplier_id, product_id,
       COUNT(*) OVER (PARTITION BY supplier_id) AS total_products
FROM supplier_products;

47. Анализ ретеншн-поведения:

SELECT user_id, visit_date,
       DATEDIFF(visit_date, LAG(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date)) AS days_since_last
FROM app_visits;

48. Сумма трат по месяцам:

SELECT user_id, EXTRACT(MONTH FROM purchase_date) AS month, amount,
       SUM(amount) OVER (PARTITION BY user_id, EXTRACT(MONTH FROM purchase_date)) AS monthly_spend
FROM purchases;

49. Вклад категории в общий доход:

SELECT category, product_id, revenue,
       ROUND(100 * revenue / SUM(revenue) OVER (PARTITION BY category), 2) AS product_share
FROM category_sales;

50. Динамика баланса по счету:

SELECT account_id, transaction_date, balance,
       balance - LAG(balance) OVER (PARTITION BY account_id ORDER BY transaction_date) AS balance_change
FROM account_history;