Функции гипотетического набора в T-SQL

63 Исходник базы данных

Вам когда-нибудь требовалось соединить (конкатенировать) элементы группы в определенном порядке в одну строку? Для решения именно такой задачи применяются функции для работы с упорядоченными наборами. Функция упорядоченного набора (ordered set function) — это особый вид агрегирующей функции. От обычных функций (таких как SUM, MIN, МАХ и прочих) ее отличает значимость порядка при вычислениях, например порядок элементов при конкатенации.

Функции упорядоченного набора используются в групповых запросах аналогично обычным функциям набора. Что касается синтаксиса, в стандарте SQL определено предложение WITHIN GROUP, в котором можно задать правило упорядочивания, например, так:

<функция_сортировки> WITHIN GROUP ( ORDER BY <список_параметров_сортировки> )

В стандарте SQL определены два типа функций упорядоченного набора, с именами необычными, но, тем не менее, отражающими назначение: функции гипотетического набора и функции обратного распределения. Я объясню, почему они так называются, когда буду рассказывать о ключевых характеристиках каждого типа. Но прежде чем приступить к подробному описанию, я хочу заметить, что концепция функций упорядоченного набора не ограничивается только этими двумя типами, заданными в стандарте — она может быть расширена до любой агрегатной функции, для вычисления которой важно упорядочение.

В качестве примера замечу, что в агрегате конкатенации строк можно предоставить пользователю возможность задать алфавитный порядок по убыванию или по возрастанию или использовать для упорядочения внешний ключ. Было бы здорово, если бы в будущем SQL Server поддержал эту концепцию и в пользовательских агрегатных функциях в общеязыковой среде выполнения (CLR). И если в пользовательских агрегатных функциях важно упорядочение в результатах вычисления, Microsoft стоило бы использовать стандартный синтаксис с использованием инструкции WITHIN GROUP.

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

Существует две функции ранжирования упорядоченного набора: RANK и DENSE_RANK. Также существует две функции распределения рангов упорядоченного набора: PERCENT_RANK и CUME_DIST. У оконных функций и функций упорядоченного набора существуют различия в способе упорядочивания. У первых, упорядочивание производится в рамках окна, а у последних — в пределах группы. При использовании в оконной функции, порядковое значение текущей строки вычисляется относительно порядковых значений в оконной секции. При использовании в функции упорядоченного набора входное значение рассчитывается в соответствии с упорядоченными значениями в группе. Когда функция упорядоченного набора получает на вход значение, вы спрашиваете «Каков был бы результат вычисления функции для данного значения, если бы я добавил его в набор в качестве элемента?» Заметьте, что «Каков был бы» используется здесь чисто гипотетически.

Эта одна из тем, которые легче объяснить на конкретном примере. Я начну с функции RANK.

Функция RANK

Пусть у нас есть следующий запрос с использованием оконной функции RANK, результат показан ниже в сокращенном виде:


SELECT custid, val,
  RANK() OVER(PARTITION BY custid ORDER BY val) AS rnk
FROM Sales.OrderValues;
Результат запроса с функцией RANK

Функция ранжирует заказы каждого клиента по размеру заказа. Вы можете объяснить, почему, допустим, у строк с рангом 5 именно этот ранг? Ранее описывалось, что при упорядочении по возрастанию функция RANK вычисляет величину на единицу большую количества строк в окне разбиения с упорядочиванием, значения которых меньше текущего. Возьмем, например, клиента 3. У строки, получившей ранг 3 для клиента 5, значение упорядочения равно 813,37. Ее ранг определен как 5, потому что есть еще четыре строки в том же разбиении со значениями упорядочение меньшими, чем 813,37 (375.50, 403.20, 660.00 и 749.06).

Теперь предположим, что нужно выполнить анализ «что, если» и узнать ответ на вопрос: «Каков будет ранг входного значения @val в каждой группе клиентов с учетом других значений в столбце rnk? Это равносильно тому, что вы бы сделали следующее:

  1. Посчитали каждую группу клиентов оконной секцией с упорядочением окна по столбцу val.

  2. Добавили каждую секцию по строке со значением @val.

  3. Вычислили оконную функцию RANK для этой строки в каждой секции.

  4. Вернули только эту строку для каждого разбиения.

Допустим, к примеру, что входное значение @val равно 1000,00. Каков будет ранг этого значения в каждой группе клиентов по отношению к другим значениям в столбце val при упорядочивании по возрастанию? Результат будет на единицу превосходить количество строк в каждой группе клиентов, имеющих значения менее чем 1000,00. Например, для клиента 3 мы получим ранг 6, потому что есть пять строк в столбце val, значения которых меньше, чем 1000,00 (375.50, 403.20, 660.00, 749.06 и 813.37).

Стандартизированная форма записи функции упорядоченного набора RANK выглядит следующим образом:

RANK(<входные данные>) WITHIN GROUP ( ORDER BY <список сортировки> )

А вот как ее можно использовать в качестве функции группового агрегирования, чтобы решить задачу (не забывайте, что этот синтаксис не поддерживается в SQL Server 2012):

-- Не работает в T-SQL
DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid,
  RANK(@val) WITHIN GROUP(ORDER BY val) AS rnk
FROM Sales.OrderValues
GROUP BY custid;
Результат запроса группового агрегирования с использованием RANK

На этом этапе принцип работы функций упорядоченного набора должен выглядеть для вас более понятным.

В последнем примере я показал, как пользоваться стандартной функцией упорядоченного набора RANK, но как было уже сказано, SQL Server не поддерживает данный синтаксис. Но для таких вычислений можно легко обойтись без встроенной функции. Для этого задействуем выражение CASE, которое возвращает некоторую константу, когда значение упорядочения меньше входного, и NULL в противном случае (это поведение по умолчанию, если отсутствует ELSE). Применяем агрегирование COUNT к результату выражения CASE и прибавляем единицу. Вот готовый запрос:

DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid,
  COUNT(CASE WHEN val < @val THEN 1 END) + 1 AS rnk
FROM Sales.OrderValues
GROUP BY custid;

Функция DENSE_RANK

Вспомните, что DENSE_RANK, как оконная функция схожа с RANK, только она возвращает значение на единицу большее, чем количество неповторяющихся значений, по которым выполняется упорядочение, (вместо количества строк) в разбиении, которые не превосходят текущее значение. Аналогично при использовании ее в качестве функции упорядоченного набора для входного значения @val функция DENSE_RANK вернет значение, на единицу превосходящее количество неповторяющихся упорядоченных значений в группе, меньших, чем @va . Вот как должен выглядеть код в соответствии со стандартом (опять же, не поддерживается в SQL Server 2012):

-- Не работает в T-SQL
DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid,
  DENSE_RANK(@val) WITHIN GROUP(ORDER BY val) AS densernk
FROM Sales.OrderValues
GROUP BY custid;
Результат запроса с функцией DENSE_RANK

В качестве альтернативного решения с использованием имеющейся в SQL Server функциональности можно использовать прием, примененный в RANK. Только вместо возврата константы в случае, когда упорядочиваемое значение меньше @val, нужно возвращать val и применять предложение DISTINCT к выражению агрегации примерно так:

DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid,
  COUNT(DISTINCT CASE WHEN val < @val THEN val END) + 1 AS densernk
FROM Sales.OrderValues
GROUP BY custid;

Функция PERCENT_RANK

Функции распределения рангов, а именно PERCENT_RANK и CUME_DIST, также поддерживаются стандартом как функции гипотетического набора. В этом разделе я расскажу о PERCENT_RANK, а в следующем — о CUME_DIST.

Напомню, что в качестве оконной функции PERCENT_RANK вычисляет относительный ранг строки в секции окна и представляет его в виде значения из диапазона от нуля до единицы (процента). Ранг вычисляется по следующему алгоритму:

А теперь будем мыслить в терминах функций гипотетического набора. Допустим, что для определенного входного значения необходимо узнать его процентиль в каждой группе при условии его (входного значения) добавления во все группы. Возьмем, к примеру, таблицу Scores со значениями результатов тестов. Для входного результата теста (назовем его @score) необходимо узнать величину его процентиля в каждом тесте при условии добавления этого входного значения в результаты всех тестов. В соответствии со стандартом SQL функция упорядоченного набора PERCENT_RANK используется в качестве агрегирующей функции:

-- Не работает в T-SQL
DECLARE @score AS TINYINT = 80;

SELECT testid,
  PERCENT_RANK(@score) WITHIN GROUP(ORDER BY score) AS pctrank
FROM Stats.Scores
GROUP BY testid;

Чтобы получить величину процентиля как результат функции гипотетического набора в SQL Server, потребуется самому реализовать эту функциональность. Один из вариантов — сгенерировать rk и nr с помощью агрегирования COUNT, а затем высчитать величину процентиля как (rk - 1)/(nr - 1). Чтобы вычислить rk, нужно посчитать количество результатов теста, которые ниже входного результата. Чтобы высчитать nr, необходимо просто посчитать количество строк и прибавить единицу (чтобы входное значение учитывалось в составе группы). Вот окончательный результат:

DECLARE @score AS TINYINT = 80;

WITH C AS
(
  SELECT testid,
    COUNT(CASE WHEN score < @score THEN 1 END) + 1 AS rk,
    COUNT(*) + 1 AS nr
  FROM Stats.Scores
  GROUP BY testid
)
SELECT testid, 1.0 * (rk - 1) / (nr - 1) AS pctrank
FROM C;

Функция CUME_DIST

Вычисление функции CUME_DIST схоже с PERCENT_RANK. Как оконная функция она рассчитывается следующим образом:

В качестве функции гипотетического набора CUME_DIST возвращает результат накопительной функции распределения для входного значения, если это значение добавить во все группы. Вот пример «стандартного» использования CUME_DIST в качестве функции упорядоченного набора применительно к нашей задаче с таблицей Scores:

-- Не работает в T-SQL
DECLARE @score AS TINYINT = 80;

SELECT testid,
  CUME_DIST(@score) WITHIN GROUP(ORDER BY score) AS cumedist
FROM Stats.Scores
GROUP BY testid;

Что касается версии для SQL Server, то она очень похожа на вариант, который вы использовали в качестве альтернативной реализации функции PERCENT_RANK. Мы вычисляли np как количество строк в группе, результаты в которой ниже, чем входное значение, плюс единица, которая соответствует входному значению. Мы вычисляли nr как количество строк в группе плюс единица, которая соответствует входному значению. И, наконец, высчитывали накопительное распределение как np/nr. Вот готовое решение:

DECLARE @score AS TINYINT = 80;

WITH C AS
(
  SELECT testid,
    COUNT(CASE WHEN score <= @score THEN 1 END) + 1 AS np,
    COUNT(*) + 1 AS nr
  FROM Stats.Scores
  GROUP BY testid
)
SELECT testid, 1.0 * np / nr AS cumedist
FROM C;

Обобщенное решение

Так как в SQL Server 2012 отсутствует поддержка функций гипотетического набора, я привел альтернативные способы, которые позволяют получить нужные результаты. Для различных вычислений я приводил очень разные решения. Далее я покажу более универсальное решение.

У всех четырех неподдерживаемых функций гипотетического набора есть парные оконные функции. Так, SQL Server 2012 поддерживает оконные функции RANK, DENSE_RANK, PERCENT_RANK и CUME_DIST. Вспомните, что функция гипотетического набора должна возвращать для входного значения результат, который соответствующая оконная функция вернет, если входное значение добавить в набор. Помня об этом, можно создать решение, которое будет работать одинаково для всех вычислений. Возможно, такое универсальное решение будет не так хорошо оптимизироваться, как более специализированные, но на него интересно взглянуть. Вот последовательность универсального решения:

  1. Объединение существующего набора и входного значения.

  2. Применение оконной функции.

  3. Фильтрация строки с входным значением для получения результата.

Вот код решения:

-- Не работает в T-SQL
SELECT <partition_col>, wf AS osf
FROM <partitions_table> AS P
  CROSS APPLY (SELECT <window_function>() OVER(ORDER BY <ord_col>) AS wf, return_flag
               FROM (SELECT <ord_col>, 0 AS return_flag
                     FROM <details_table> AS D
                     WHERE D.<partition_col> = P.<partition_col>
               
                     UNION ALL
               
                     SELECT @input_val, 1) AS U) AS A
WHERE return_flag = 1;

Внешний запрос обращается к таблице содержащей неповторяющиеся значения в секции. Далее с помощью оператора CROSS APPLY код обрабатывает каждую секцию отдельно. Самая глубоко вложенная производная таблица U отвечает за объединение строк текущей секции, которые обозначены значением return_flag, со строкой, созданной на основе входного значения. Затем запрос вычисляет на основе таблицы U оконную функцию, генерируя унаследованную таблицу A. Наконец, внешний запрос отфильтровывает только строки со значением return_flag. Это строки, для которых в каждой секции выполнено вычисление с учетом входного значения, то есть на основе гипотетического набора вычислений.

Если данная обобщенная форма вам до сих пор не ясна, попробуйте понять логику на конкретных примерах. Этот запрос обращается к таблице Customers (за данными секций) и представлению Sales.OrderValues (за подробными данными). Он вычисляет RANK и DENSE_RANK как гипотетический набор вычислений для входного значения @val с секционирующим элементом custid и упорядочивающим элементом val:

DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid, rnk, densernk
FROM Sales.Customers AS P
  CROSS APPLY (SELECT 
                 RANK() OVER(ORDER BY val) AS rnk,
                 DENSE_RANK() OVER(ORDER BY val) AS densernk,
                 return_flag
               FROM (SELECT val, 0 AS return_flag
                     FROM Sales.OrderValues AS D
                     WHERE D.custid = P.custid
               
                     UNION ALL
               
                     SELECT @val, 1) AS U) AS A
WHERE return_flag = 1;
Результат обобщенного запроса

Похожим образом следующий код обращается к таблицам Tests (секции) и Scores (подробности). Он высчитывает PERCENT_RANK и CUME_DIST как набор гипотетических вычислений для входного значения @score, с testid в качестве секционирующего элемента и score в качестве упорядочивающего элемента:

DECLARE @score AS TINYINT = 80;

SELECT testid, pctrank, cumedist
FROM Stats.Tests AS P
  CROSS APPLY (SELECT 
                 PERCENT_RANK() OVER(ORDER BY score) AS pctrank,
                 CUME_DIST() OVER(ORDER BY score) AS cumedist,
                 return_flag
               FROM (SELECT score, 0 AS return_flag
                     FROM Stats.Scores AS D
                     WHERE D.testid = P.testid
               
                     UNION ALL
               
                     SELECT @score, 1) AS U) AS A
WHERE return_flag = 1;
Результат запроса с вызовом функций PERCENT_RANK и CUME_DIST

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

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

DECLARE @val AS NUMERIC(12, 2) = 1000.00;

SELECT custid, rnk, densernk
FROM Sales.Customers AS P
  CROSS APPLY (SELECT 
                 RANK() OVER(ORDER BY val) AS rnk,
                 DENSE_RANK() OVER(ORDER BY val) AS densernk,
                 return_flag
               FROM (SELECT val, 0 AS return_flag
                     FROM Sales.OrderValues AS D
                     WHERE D.custid = P.custid
               
                     UNION ALL
               
                     SELECT @val, 1) AS D) AS A
WHERE return_flag = 1
  AND EXISTS
    (SELECT * FROM Sales.OrderValues AS D
     WHERE D.custid = P.custid)

Этот запрос возвращает 89, а не 91 строк, потому что только 89 из 91 существующих клиентов разместили заказы.

Пройди тесты
Лучший чат для C# программистов