Применение функций окна для аналитики

  • 14 мин

Подсказка

Дополнительные сведения см. на вкладке "Текст и изображения ".

Аналитические запросы часто требуют вычислений, охватывающих несколько строк, при этом возвращаются отдельные сведения о строке. Традиционные агрегатные функции сворачивают строки в группы, теряя сведения на уровне строк. Функции окна устраняют эту проблему, выполняя вычисления по набору строк, связанных с текущей строкой, не сворачивая результирующий набор.

Общие сведения о синтаксисе функции окна

Функции окна вычисляют значения в "окне" строк, определенных предложением OVER . В отличие от обычных агрегатных функций, функции окна не группируют строки в одну выходную строку. Вместо этого они вычисляют значения между связанными строками, сохраняя все исходные строки в результате.

Общий синтаксис функции окна:

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

Компоненты OVER предложения управляют определением окна:

  • PARTITION BY: делит строки на группы (секции) для вычисления
  • ORDER BY: определяет логический порядок строк в каждой секции
  • ROWS/RANGE: определяет границы кадра относительно текущей строки

Следующий запрос демонстрирует простую оконную функцию, которая вычисляет общую сумму заказов по каждому клиенту.

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Замечание

При указании ORDER BY в OVER предложении без спецификации кадра кадр по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW для агрегатных функций. Это создает накопительные вычисления.

Использование функций ранжирования

Функции ранжирования назначают последовательные числа строкам в зависимости от их положения в секции. SQL Server предоставляет четыре функции ранжирования. Каждая функция обрабатывает связи по-разному:

ROW_NUMBER() присваивает уникальное последовательное число каждой строке без дубликатов даже для привязанных значений:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Результирующий набор выглядит следующим образом:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

Этот запрос оценивает все продукты по цене от самой высокой до самой низкой. Каждый продукт получает уникальное число независимо от того, имеют ли несколько продуктов одну и ту же цену.

RANK() назначает тот же ранг связанным значениям, а затем пропускает числа для учета связей:

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Результирующий набор выглядит следующим образом:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

Если два продукта имеют одинаковые цены, оба получают один и тот же рейтинг. Рейтинг следующего продукта отражает общее количество продуктов, ранжированных выше, создавая пробелы в последовательности.

DENSE_RANK() присваивает один и тот же ранг связанным значениям, но не пропускает числа:

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Результирующий набор выглядит следующим образом:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

Как RANK(), связанные значения имеют одинаковый ранг. Тем не менее, DENSE_RANK() продолжает с следующим последовательным номером, поэтому его можно использовать для подсчета отдельных ценовых уровней.

NTILE(n) распределяет строки в указанное число примерно равных групп:

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

Результирующий набор выглядит следующим образом:

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

Этот запрос делит продукты на четыре группы на основе цены. Самые дорогие продукты находятся в квартиле 1, а самые дешёвые - в квартиле 4. Используйте NTILE() для анализа процентилей или равномерного распределения работы.

Объединение с функциями ранжирования обеспечивает ранжирование PARTITION BY по группам:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

Результирующий набор выглядит следующим образом:

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

Этот запрос оценивает продукты в каждой категории отдельно. Ранжирование обнуляется до 1 для каждой категории, поэтому вы можете определить самый ценный продукт в каждой категории, отфильтровав по CategoryPriceRank = 1.

Подсказка

Используйте ROW_NUMBER(), когда требуется ровно одна строка на ранг (например, выбор лучших N для каждой группы). Используйте RANK() или DENSE_RANK(), когда необходимо сохранить сведения о связях в целях создания отчетов.

Применение агрегатных функций окна

Стандартные агрегатные функции, такие как SUM, AVG, COUNT, MIN и MAX, могут использоваться в качестве оконных функций с добавлением предложения OVER. Это позволяет вычислять статистические данные при сохранении отдельных сведений о строке.

В следующем запросе показано, как вычислить накопительные итоги и агрегаты:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

Результирующий набор выглядит следующим образом:

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

Это важно

При использовании агрегатных функций окна без ORDER BY предложения OVER функция вычисляет всю секцию. Добавление ORDER BY создает выполняющийся расчет от начала раздела до текущей строки.

Определите рамки окон с помощью ROWS и RANGE

Окна фреймов позволяют точно указать, какие строки относительно текущей строки должны быть включены в вычисление. Предложение ROWS подсчитывает физические строки, а RANGE группирует строки с равными значениями.

Границы фрейма можно указать с помощью:

  • UNBOUNDED PRECEDING: с начала раздела
  • n PRECEDING: n строки перед текущей строкой
  • CURRENT ROW: текущая строка
  • n FOLLOWING: n строки после текущей строки
  • UNBOUNDED FOLLOWING: конец раздела

Следующий запрос вычисляет скользящее среднее за последние три заказа:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Результирующий набор выглядит следующим образом:

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

Этот запрос вычисляет скользящее среднее третьего порядка, включая текущую строку и две строки перед ней. Для первой строки доступно только одно значение, поэтому среднее значение равно TotalDue. Начиная с третьей строки, окно охватывает все три строки.

Использование аналитических функций

Аналитические функции позволяют получать доступ к данным из других строк без использования самосоединений или вложенных запросов. Эти функции полезны для анализа временных рядов, обнаружения тенденций и сравнения текущих значений с историческими или будущими значениями. В отличие от функций агрегатного окна, вычисляющих сводки, аналитические функции извлекают определенные значения из определенных строк в окне.

LAG() и LEAD() получают доступ к значениям из предыдущих или последующих строк, как показано ниже:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Результирующий набор выглядит следующим образом:

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() извлекает значение из предыдущей строки, извлекает LEAD() из следующей строки. Второй параметр указывает, сколько строк следует искать назад или вперед (по умолчанию — 1), а третий параметр предоставляет значение по умолчанию, если строка не существует (например, для первой строки с LAG()). Используйте эти функции для вычисления изменений за период, выявления тенденций или обнаружения аномалий в последовательных данных.

FIRST_VALUE() и LAST_VALUE() возвращает значения из первой или последней строки в кадре:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

Результирующий набор выглядит следующим образом:

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() возвращает значение из первой строки в упорядоченном окне, которое в данном случае является самым дорогим продуктом для каждой категории. LAST_VALUE() возвращает наименьшую стоимость, но требует явного фрейма для включения всех строк. Эти функции помогают сравнивать каждую строку с значениями теста, такими как наивысшее, наименьшее или базовое значение в группе.

Замечание

LAST_VALUE() требует явной спецификации кадра для включения строк после текущей строки. Без него кадр по умолчанию включает только строки до текущей строки, вследствие чего LAST_VALUE() возвращает значение текущей строки.

PERCENT_RANK() и CUME_DIST() вычисляют относительную позицию в разбиении:

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

Результирующий набор выглядит следующим образом:

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() возвращает значение от 0 до 1, указывающее, какой процент строк имеет более низкие значения (0 означает самый низкий, один означает самый высокий). CUME_DIST() показывает накопительное распределение, указывающее, какой процент строк имеет значения меньше или равно текущей строке. Используйте эти функции для анализа процентилей, выявления излиев или создания отчетов о распределении.

Дополнительные сведения о функциях окна см. в разделе "Функции окна" (Transact-SQL) и "Ранжирование функций".