PARTITION: примеры (SQL)

Базовые примеры использования

Пример 1: Нумерация строк внутри секции

-- Создание тестовой таблицы
CREATE TABLE #Sales (
    SaleID INT,
    ProductCategory VARCHAR(20),
    SaleAmount DECIMAL(10,2)
);

INSERT INTO #Sales VALUES
(1, 'Electronics', 1000),
(2, 'Electronics', 1500),
(3, 'Books', 300),
(4, 'Books', 200),
(5, 'Electronics', 800);

-- Нумерация продаж внутри каждой категории по возрастанию SaleID
SELECT
    SaleID,
    ProductCategory,
    SaleAmount,
    ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY SaleID) AS RowNumInCategory
FROM #Sales;

SaleID   ProductCategory SaleAmount RowNumInCategory
3        Books           300.00     1
4        Books           200.00     2
1        Electronics     1000.00    1
2        Electronics     1500.00    2
5        Electronics     800.00     3

Пример 2: Агрегация внутри секции

-- Расчет доли каждой продажи в общей сумме по ее категории
SELECT
    SaleID,
    ProductCategory,
    SaleAmount,
    SUM(SaleAmount) OVER (PARTITION BY ProductCategory) AS TotalInCategory,
    SaleAmount * 100.0 / SUM(SaleAmount) OVER (PARTITION BY ProductCategory) AS PercentOfCategory
FROM #Sales
ORDER BY ProductCategory, SaleID;

SaleID ProductCategory SaleAmount TotalInCategory PercentOfCategory
3      Books           300.00     500.00          60.000000000000
4      Books           200.00     500.00          40.000000000000
1      Electronics     1000.00    3300.00         30.303030303030
2      Electronics     1500.00    3300.00         45.454545454545
5      Electronics     800.00     3300.00         24.242424242424

Пример 3: Сравнение со средним по секции

-- Определение строк, где сумма продажи выше средней в своей категории
SELECT
    SaleID,
    ProductCategory,
    SaleAmount,
    AVG(SaleAmount) OVER (PARTITION BY ProductCategory) AS AvgInCategory
FROM #Sales
WHERE SaleAmount > AVG(SaleAmount) OVER (PARTITION BY ProductCategory);

SaleID ProductCategory SaleAmount AvgInCategory
2      Electronics     1500.00    1100.00
3      Books           300.00     250.00

Похожие средства MS SQL Server

GROUP BY: Основная альтернатива для агрегации данных. В отличие от PARTITION BY, GROUP BY сворачивает строки, возвращая один результат на группу. PARTITION BY сохраняет детализацию исходных строк. GROUP BY предпочтительнее для итоговых отчетов, PARTITION BY — для аналитических расчетов без потери деталей.

APPLY операторы (CROSS/OUTER): Позволяют вызывать табличную функцию для каждой строки основного запроса. Могут быть альтернативой в сложных случаях, где логику окна нельзя выразить стандартной оконной функцией, но требуют больше ресурсов.

Коррелированные подзапросы: Могут эмулировать некоторые оконные вычисления (например, расчет суммы по группе), но часто менее производительны и сложнее читаются по сравнению с PARTITION BY.

Аналоги в других СУБД

Oracle

Синтаксис оконных функций с PARTITION BY практически идентичен SQL Server и появился раньше.

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

PostgreSQL

Полная поддержка стандартного синтаксиса оконных функций, включая PARTITION BY. Поведение аналогично.

SELECT
    product_id,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_cat
FROM products;

MySQL (с версии 8.0+)

Поддерживает оконные функции с предложением PARTITION BY в полном объеме, синтаксис соответствует стандарту.

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

SQLite (с версии 3.28.0+)

Добавлена базовая поддержка оконных функций, включая PARTITION BY.

SELECT
    name,
    year,
    revenue,
    LAG(revenue) OVER (PARTITION BY name ORDER BY year) AS prev_revenue
FROM sales;

Отличия: В более старых версиях MySQL, SQLite или в Sybase ASE оконные функции могли отсутствовать. В таких случаях используют подзапросы или временные таблицы для эмуляции логики.

Распространенные ошибки

Ошибка 1: Использование без оконной функции

PARTITION BY не является самостоятельной конструкцией.

-- Неправильно
SELECT SaleID, PARTITION BY ProductCategory FROM Sales;

-- Правильно
SELECT SaleID, ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY SaleID) FROM Sales;

Ошибка 2: Путаница с GROUP BY в одном запросе

При совместном использовании GROUP BY и оконных функций, последние вычисляются после группировки. Ошибка — попытка добавить неагрегированный столбец в SELECT без его включения в GROUP BY.

-- Ошибка: ProductName не входит в GROUP BY и не является аргументом агрегата.
SELECT
    CategoryID,
    ProductName,
    SUM(Quantity) OVER (PARTITION BY CategoryID) AS Total
FROM OrderDetails
GROUP BY CategoryID;

-- Возможное решение: убрать GROUP BY или добавить ProductName в GROUP BY.
SELECT
    CategoryID,
    ProductName,
    SUM(Quantity) OVER (PARTITION BY CategoryID) AS Total
FROM OrderDetails;

Ошибка 3: Некорректный порядок вычислений

Оконные функции выполняются после операций WHERE, GROUP BY, HAVING, но до ORDER BY. Попытка фильтрации по результату оконной функции в WHERE вызывает ошибку.

-- Ошибка: нельзя использовать псевдоним оконной функции в WHERE.
SELECT
    SaleID,
    ROW_NUMBER() OVER (ORDER BY SaleID) AS rn
FROM Sales
WHERE rn = 1;

-- Решение: использовать CTE или подзапрос.
WITH NumberedSales AS (
    SELECT SaleID, ROW_NUMBER() OVER (ORDER BY SaleID) AS rn
    FROM Sales
)
SELECT SaleID FROM NumberedSales WHERE rn = 1;

История изменений

Основная логика работы PARTITION BY в составе оконных функций остается неизменной с момента их появления в SQL Server 2005. Ключевые дополнения в последних версиях касаются расширения набора самих оконных функций и улучшения оптимизатора.

• SQL Server 2012: Добавлены функции смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), которые активно используют PARTITION BY.
• SQL Server 2022: Введена функция GREATEST() и LEAST(), не являющиеся оконными, но оптимизатор запросов продолжает совершенствоваться для вычислений с PARTITION BY.

Изменений в синтаксисе или аргументах самого предложения PARTITION BY не было.

Расширенные примеры применения

Пример 1: Расчет скользящего среднего

Использование PARTITION BY вместе с фреймом ROWS.

-- Скользящее среднее по 3 последним продажам для каждого товара
SELECT
    ProductID,
    SaleDate,
    Amount,
    AVG(Amount) OVER (
        PARTITION BY ProductID
        ORDER BY SaleDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3
FROM Sales
ORDER BY ProductID, SaleDate;

Пример 2: Поиск дубликатов сложных данных

-- Поиск полностью дублирующихся строк по нескольким полям
WITH CTE_Duplicates AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID, OrderDate, TotalAmount
            ORDER BY (SELECT NULL)
        ) AS DupNum
    FROM Orders
)
SELECT * FROM CTE_Duplicates WHERE DupNum > 1;

Пример 3: Иерархические проценты внутри групп

-- Расчет вклада каждой подкатегории в сумму по основной категории и общей сумме
SELECT
    MainCategory,
    SubCategory,
    Revenue,
    SUM(Revenue) OVER (PARTITION BY MainCategory) AS CatTotal,
    Revenue * 100.0 / SUM(Revenue) OVER (PARTITION BY MainCategory) AS PercentOfCategory,
    Revenue * 100.0 / SUM(Revenue) OVER () AS PercentOfGrandTotal
FROM CategoryRevenue
ORDER BY MainCategory, Revenue DESC;

Пример 4: Одновременное использование нескольких секций

В одном запросе можно применить несколько оконных функций с разными условиями секционирования.

-- Сравнение продаж сотрудника с результатами его отдела и общими результатами
SELECT
    EmployeeID,
    DepartmentID,
    Year,
    Sales,
    AVG(Sales) OVER (PARTITION BY DepartmentID, Year) AS AvgDeptYear,
    AVG(Sales) OVER (PARTITION BY Year) AS AvgCompanyYear,
    Sales - AVG(Sales) OVER (PARTITION BY DepartmentID, Year) AS DiffFromDept
FROM EmployeeSales;

Пример 5: Использование с агрегатными функциями без ORDER BY

Для функций SUM, AVG и других агрегатов в качестве оконных, ORDER BY не является обязательным в PARTITION BY. Это позволяет получить одно и то же агрегированное значение для всех строк секции.

-- Добавление общего итога по отделу к каждой строке без нарастающего итога
SELECT
    EmpID,
    Department,
    Salary,
    SUM(Salary) OVER (PARTITION BY Department) AS DeptSalaryFund
FROM Employees;