Функции ранжирования T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Функции ранжирования

Стандарт SQL поддерживает четыре оконные функции, которые служат для ранжирования. Это ROW_NUMBER, NTILE, RANK и DENSE_RANK. В стандарте первые две считаются относящимися к одной категории, а вторые две — ко второй. Это связано с различиями в отношении детерминизма. Подробнее я расскажу в процессе рассказа об отдельных функциях.

Функции ранжирования появились еще в SQL Server 2005. Тем не менее я покажу альтернативные, основанные на наборах методы для получения того же результата. Я сделаю это по двум причинам: во-первых, это может быть интересным, а во-вторых, я верю, что это помогает лучше понять нюансы работы функций. Тем не менее имейте в виду, что на практике я настоятельно рекомендую придерживаться оконных функций, потому что они и проще, и намного эффективнее, чем альтернативные решения. Подробнее об оптимизации мы поговорим позже.

Все четыре функции ранжирования поддерживают необязательное предложение секционирования и обязательное предложение упорядочения окна. Если предложение секционирования окна отсутствует, весь результирующий набор базового запроса (вспомните о входных данных этапа SELECT) считается одной секцией. Что касается предложения упорядочения окна, то оно обеспечивает упорядочение при вычислениях. Понятно, что ранжирование строк без определения упорядочения вряд ли имеет смысл. В ранжирующих оконных функциях упорядочение служит другой цели по сравнению с функциями, поддерживающими кадрирование, такими как агрегирующие оконные функции. В первом случае упорядочение имеет логический смысл для самих вычислений, а во втором — упорядочение связано с кадрированием, то есть служит целям фильтрации.

Функция ROW_NUMBER

Эта функция вычисляет последовательные номера строк, начиная с 1, в соответствующей секции окна и в соответствии с заданным упорядочением окна. Посмотрите на пример запроса:

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY orderid) AS rownum
FROM Sales.OrderValues;

Вот сокращенный результат выполнения этого запроса:

Этот запрос кажется тривиальным, но здесь есть несколько моментов, о которых стоит сказать.

Так как в запросе нет предложения представления ORDER BY, упорядочение представления не гарантируется. Поэтому упорядочение представления здесь нужно считать произвольным. На практике SQL Server оптимизирует запрос с учетом отсутствия предложения ORDER BY, поэтому строки могут возвращаться в любом порядке. Если нужно гарантировать упорядочение представления, нужно не забыть добавить предложение представления ORDER BY. Если нужно, чтобы упорядочение представления выполнялось на основе номера строки, можно использовать псевдоним, назначенный в процессе вычисления предложения представления ORDER BY примерно так:

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY orderid) AS rownum
FROM Sales.OrderValues
ORDER BY rownum;

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

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY orderid) AS rownum
FROM Sales.OrderValues
ORDER BY val DESC;

Можно использовать оконный агрегат COUNT для создания операции которая логически эквивалентна функции ROW_NUMBER. Допустим, WPO - определение секционирования и упорядочения окна, примененное в функции ROW_NUMBER. Тогда ROW_NUMBER OVER WPO эквивалентно COUNT(*) OVER(WPO ROWS UNBOUNDED PRECEDING). Например, следующее эквивалентно запросу из листинга предыдущего примера:

SELECT orderid, val,
  COUNT(*) OVER(ORDER BY orderid
                ROWS UNBOUNDED PRECEDING) AS rownum
FROM Sales.OrderValues;

Как я говорил, это хорошая тренировка — попытаться и создать альтернативные варианты вместо использования оконных функций, и не важно, что эти варианты сложнее и менее эффективные. Если уж мы говорим о функции ROW_NUMBER, то вот основанная на наборах стандартная альтернатива запросу, в которой оконные функции не используются:

SELECT orderid, val,
  (SELECT COUNT(*)
   FROM Sales.OrderValues AS O2
   WHERE O2.orderid <= O1.orderid) AS rownum
FROM Sales.OrderValues AS O1;

В этом решении используется агрегат COUNT во вложенном запросе для определения, у скольких строк значение упорядочения (в нашем случае orderid) меньше или равно текущему. Это просто, если у вас уникальное упорядочение, основанное на одном атрибуте. Но все сильно усложняется, если упорядочение неуникально, что я и продемонстрирую при обсуждении детерминизма.

Детерминизм

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

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER(ORDER BY orderdate DESC) AS rownum
FROM Sales.OrderValues;

Так как атрибут orderdate не уникальный, упорядочение строк с одинаковым значением orderdate следует считать произвольным. В принципе существует более одного корректного результата этого запроса. В качестве примера возьмем четыре строки с датой заказа 2008-05-06. Любой порядок строк с номерами от 1 до 4 считается правильным. Поэтому если вы выполните запрос снова, то в принципе можете получить другой порядок — сейчас не будем оценивать вероятность такого события, обусловленную особенностями реализации SQL Server.

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

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER(ORDER BY orderdate DESC, orderid DESC) AS rownum
FROM Sales.OrderValues;

При использовании оконных функций детерминизм вычисления номеров страниц реализуется просто. Сделать то же, не прибегая к оконным функция сложнее, но вполне реально:

SELECT orderdate, orderid, val,
  (SELECT COUNT(*)
   FROM Sales.OrderValues AS O2
   WHERE O2.orderdate >= O1.orderdate
     AND (O2.orderdate > O1.orderdate
          OR O2.orderid >= O1.orderid)) AS rownum
FROM Sales.OrderValues AS O1;

Но вернемся к функции ROW_NUMBER: как мы выдели, ее можно использовать для создания недетерминистических вычислений при использовании неуникального упорядочения. Таким образом, недетерминизм разрешен, но странно то, что он не разрешен полностью. Я имею в виду то, что предложение ORDER BY обязательно. Но что, если вы хотите просто получить в секции уникальные номера строк не обязательно в каком-то определенном порядке? Можно создать такой запрос:

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER() AS rownum
FROM Sales.OrderValues;

Но, как уже говорилось, предложение ORDER BY в функциях ранжирования является обязательным:

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

Вы можете поумничать и определить константу в списке ORDER BY:

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER(ORDER BY NULL) AS rownum
FROM Sales.OrderValues;

Ho SQL Server вернет такую ошибку:

Windowed functions and NEXT VALUE FOR functions do not support 
constants as ORDER BY clause expressions.

Однако решение есть, и я вскоре его приведу.

Предложение OVER и последовательности

Вам наверное интересно, что делает функция NEXT VALUE FOR в сообщении об ошибке при попытке применить константу в предложении OVER. Это связано с расширенной поддержкой последовательностей в SQL Server 2012 по сравнению со стандартом SQL. Последовательность SQL Server - это объект базы данных, который служит для автогенерации номеров, которые часто используются как ключи. Вот пример кода создания объекта-последовательности dbo.Seq1:

CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

Для получения очередного значения последовательности применяется функция NEXT VALUE FOR. Вот пример:

SELECT NEXT VALUE FOR dbo.Seq1;

Эту функцию можно вызывать в запросе, возвращающем несколько строк:

SELECT orderid, orderdate, val,
    NEXT VALUE FOR dbo.Seq1 AS seqval
FROM Sales.OrderValues;

Это стандартный код. В SQL Server 2012 возможности функции NEXT VALUE FOR расширены и позволяют определять упорядочение в предложении OVER подобно тому, как это делается в оконных функциях. Таким образом можно гарантировать, что значения последовательности отражают желаемое упорядочение. Вот пример использования расширенной функции NEXT VALUE FOR:

SELECT orderid, orderdate, val,
    NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY orderdate, orderid) AS seqval
FROM Sales.OrderValues;

Аналогичный принцип детерминизма применим к предложению OVER в функции NEXT VALUE FOR, как это происходит в оконных функциях.

Итак, нет прямого способа назначить номера строкам без упорядочения, но, по-видимому, SQL Server не смущает вложенный запрос, возвращающий константу в качестве элемента упорядочения окна. Вот пример:

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM Sales.OrderValues;

Функция NTILE

Эта функция позволяет разбивать строки в секции окна на примерно равные по размеру подгруппы (tiles) в соответствии с заданным числом подгрупп и упорядочением окна. Допустим, что нужно разбить строки представления OrderValues на 10 подгрупп одинакового размера на основе упорядочения по val. В представлении 830 строк, поэтому требуется 10 подгрупп, размер каждой будет составлять 83 (830 деленное на 10). Поэтому первым 83 строкам (одной десятой части), упорядоченным по val, будет назначен номер группы 1, следующим 83 строкам — номер подгруппы 2 и т. д. Вот запрос, вычисляющий номера как строк, так и подгрупп:

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY val) AS rownum,
  NTILE(10) OVER(ORDER BY val) AS tile
FROM Sales.OrderValues;

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

Но вернемся к результату запроса, вычисляющего номера как строк, так и подгрупп: как видите они тесно связаны друг с другом. По сути, можно считать, что номер подгруппы вычисляется на основе номера строки. В предыдущем разделе мы говорили, что если упорядочение окна не является уникальным, функция ROW_NUMBER является недетерминистической. Если разбиение на подгруппы принципиально основано на номерах строк, то это означает, что вычисление NTILE также недетерминистично, если упорядочение окна не уникально. Это означает, что у данного запроса может быть несколько правильных результатов. Можно посмотреть на это с другой стороны: двум строкам с одним значением упорядочения могут быть назначены разные номера подгрупп. Если нужен гарантированный детерминизм, можно следовать моим рекомендациям по получению детерминистических номеров строк, а именно добавить в упорядочение окна дополнительный параметр:

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY val, orderid) AS rownum,
  NTILE(10) OVER(ORDER BY val, orderid) AS tile
FROM Sales.OrderValues;

Теперь у запроса только один правильный результат. Ранее, при описании функции NTILE я пояснил, что она позволяет разбить строки в секции окна на примерно равные подгруппы. Я использовал слово «примерно», потому что число строк, полученное в базовом запросе, может не делиться нацело на число подгрупп. Допустим, вы хотите разбить строки представления OrderValues на 100 подгрупп. При делении 830 на 100 получаем частное 8 и остаток 30. Это означает, что базовая размерность подгрупп будет 8, но часть подгрупп получать дополнительную строку. Функция NTILE не пытается распределять дополнительные строки среди подгрупп с равным расстоянием между подгруппами — она просто добавляет по одному ряду в первые подгруппы, пока не распределит остаток. При наличии остатка 30 размерность первых 30 подгрупп будет на единицу больше базовой размерности. Поэтому первые 30 будут содержать 9 рядов, а последние 70 — 8, как показано в следующем запросе:

SELECT orderid, val,
  ROW_NUMBER() OVER(ORDER BY val, orderid) AS rownum,
  NTILE(100) OVER(ORDER BY val, orderid) AS tile
FROM Sales.OrderValues;

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

Я покажу один способ решения задачи. Для начала, вот код, который вычисляет число подгрупп по заданным размерности, числу подгрупп и числу строк:

DECLARE @cnt AS INT = 830, @numtiles AS INT = 100, @rownum AS INT = 42;

WITH C1 AS
(
  SELECT 
    @cnt / @numtiles     AS basetilesize,
    @cnt / @numtiles + 1 AS extendedtilesize,
    @cnt % @numtiles     AS remainder
),
C2 AS
(
  SELECT *, extendedtilesize * remainder AS cutoffrow
  FROM C1
)
SELECT
  CASE WHEN @rownum <= cutoffrow
    THEN (@rownum - 1) / extendedtilesize + 1
    ELSE remainder + ((@rownum - cutoffrow) - 1) / basetilesize + 1
  END AS tile
FROM C2;

Вычисление вполне очевидно. Для входных данных код возвращает 5 в качестве числа подгрупп.

Затем применим эту процедуру к строкам представления OrderValues. Используйте агрегат COUNT, чтобы получить размерность результирующего набора, а не входные данные @cnt, а также примените описанную ранее логику для вычисления номеров строк без использования оконных функций вместо входных данных @rownum:

DECLARE @numtiles AS INT = 100;

WITH C1 AS
(
  SELECT 
    COUNT(*) / @numtiles AS basetilesize,
    COUNT(*) / @numtiles + 1 AS extendedtilesize,
    COUNT(*) % @numtiles AS remainder
  FROM Sales.OrderValues
),
C2 AS
(
  SELECT *, extendedtilesize * remainder AS cutoffrow
  FROM C1
),
C3 AS
(
  SELECT O1.orderid, O1.val,
    (SELECT COUNT(*)
     FROM Sales.OrderValues AS O2
     WHERE O2.val <= O1.val
       AND (O2.val < O1.val
            OR O2.orderid <= O1.orderid)) AS rownum
  FROM Sales.OrderValues AS O1
)
SELECT C3.*,
  CASE WHEN C3.rownum <= C2.cutoffrow
    THEN (C3.rownum - 1) / C2.extendedtilesize + 1
    ELSE C2.remainder + ((C3.rownum - C2.cutoffrow) - 1) / C2.basetilesize + 1
  END AS tile
FROM C3 CROSS JOIN C2;

Как обычно, не пытайтесь повторить это в производственной среде! Это пример предназначен для обучения, а его производительность в SQL Server ужасна по сравнению с функцией NTILE.

Функции RANK и DENSE_RANK

Функции RANK и DENSE RANK похожи на ROW_NUMBER, но в отличие от нее они не создают уникальные значения в оконной секции. При упорядочении окна по возрастанию «Обычный ранг» RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. «Плотный ранг» DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. При упорядочении окна по убыванию RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции. DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции.

Вот пример запроса, вычисляющего номера строк, ранги и «уплотненные» ранги. При этом используется секционирование окна по умолчанию и упорядочение по orderdate DESC:

SELECT orderid, orderdate, val,
  ROW_NUMBER() OVER(ORDER BY orderdate DESC) AS rownum,
  RANK()       OVER(ORDER BY orderdate DESC) AS rnk,
  DENSE_RANK() OVER(ORDER BY orderdate DESC) AS drnk
FROM Sales.OrderValues;

Атрибут orderdate не уникален. Но заметьте, что при этом номера строк уникальны. Значения RANK и DENSE_RANK не уникальны. Все строки с одной датой заказа, например 2008-05-05, получили одинаковый «неплотный» ранг 5 и «плотный» ранг 2. Ранг 5 означает, что есть четыре строки с большими (более поздними) датами заказов (упорядочение ведется по убыванию), а «плотный» ранг 2 означает, что есть одна более поздняя уникальная дата.

Альтернативное решение, заменяющее RANK и DENSE_RANK и не использующие оконные функции, создается просто:

SELECT orderid, orderdate, val,
  (SELECT COUNT(*)
   FROM Sales.OrderValues AS O2
   WHERE O2.orderdate > O1.orderdate) + 1 AS rnk,
  (SELECT COUNT(DISTINCT orderdate)
   FROM Sales.OrderValues AS O2
   WHERE O2.orderdate > O1.orderdate) + 1 AS drnk
FROM Sales.OrderValues AS O1;

Для вычисления ранга определяется число строк с большим значением, по которому ведется упорядочение, (как вы помните, упорядочение ведется по убыванию) и к нему добавляется единица. Для вычисления плотного ранга нужно пересчитать уникальные большие значения, по которым ведется упорядочение, и добавить к полученному числу единицу.

Детерминизм

Как вы уже сами, наверное, поняли, что как RANK, так и DENSE_RANK детерминистичны по определению. При одном значении упорядочения — независимо от его уникальности — возвращается одно и то же значение ранга. Вообще говоря, эти две функции обычно интересны, если упорядочение неуникально. Если упорядочение уникально, они дают те же результаты, что и Функция ROW_NUMBER.