Аналитические функции T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Аналитические функции

Аналитические оконные функции, или функции распределения (distribution function), предоставляют информацию о распределении данных и используются в основном для статистического анализа. В SQL Server 2012 появилась поддержка двух аналитических оконных функций: распределения рангов и обратного распределения. К функциям распределения рангов относятся PERCENT_RANK и CUME_DIST. Есть также две функции обратного распределения: PERCENTILE_CONT and PERCENTILE_DISC.

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

SELECT * FROM Stats.Scores;

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

Функции распределения рангов

В соответствии со стандартом SQL, аналитические функции вычисляют относительный ранг строки в секции окна, выраженный как дробное число от нуля до единицы, которое большинство воспринимает как процент. Две разновидности - PERCENT_RANK и CUME_DIST - выполняют вычисления немного по-разному.

Допустим, rk это RANK строки, в котором используется то же определение окна, что и в определении окна в аналитической функции. Также допустим, что nr — число строк в секции окна. Еще представим, что np — число строк, которое предшествует или находится на одном уровне с текущей строкой (то же самое, что минимальное значение rk, которое больше, чем текущее значение rk за вычетом единицы или nr, если текущее значение rk является максимальным).

Тогда PERCENT_RANK вычисляется так: (rk - 1) / (nr - 1). A CUME_DIST рассчитывается так: np / nr. Следующий запрос вычисляет как процентильный ранг, так и интегральное распределение результатов студентов, секционированных по testid и упорядоченных по score:

SELECT testid, studentid, score,
  PERCENT_RANK() OVER(PARTITION BY testid ORDER BY score) AS percentrank,
  CUME_DIST()    OVER(PARTITION BY testid ORDER BY score) AS cumedist
FROM Stats.Scores;

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

До SQL Server 2012 вычисление процентильного ранга выполнялось довольно прямолинейно, потому что rk можно вычислить с помощью оконной функции RANK, а nr — с помощью агрегирующей оконной функции COUNT, обе эти функции имеются в SQL Server, начиная с SQL Server 2005. С вычислением интегрального распределения сложнее, потому что для обсчета текущей строки требуется значение rk, связанное с другой строкой. В результате вычисления мы должны получать минимальный rk, который больше текущего rk, или nr, если текущий rk является максимальным. Решить задачу можно с помощью связанного вложенного запроса.

Вот запрос (его можно выполнять в SQL Server 2005 и последующих версиях), вычисляющий как процентильный ранг, так и интегральное распределение:

WITH C AS
(
  SELECT testid, studentid, score,
    RANK() OVER(PARTITION BY testid ORDER BY score) AS rk,
    COUNT(*) OVER(PARTITION BY testid) AS nr
  FROM Stats.Scores
)
SELECT testid, studentid, score,
  1.0 * (rk - 1) / (nr - 1) AS percentrank,
  1.0 * (SELECT COALESCE(MIN(C2.rk) - 1, C1.nr)
         FROM C AS C2
         WHERE C2.testid = C1.testid
           AND C2.rk > C1.rk) / nr AS cumedist
FROM C AS C1;

Причина использования числового значения «1.0» во второй части в том, чтобы принудительно выполнить неявное преобразование целочисленных операндов в числовые, потому что в противном случае мы получим целочисленное деление.

Вот еще один пример — запрос вычисляет процентильный ранг и интегральное распределение числа заказов у сотрудников:

SELECT empid, COUNT(*) AS numorders,
  PERCENT_RANK() OVER(ORDER BY COUNT(*)) AS percentrank,
  CUME_DIST() OVER(ORDER BY COUNT(*)) AS cumedist
FROM Sales.Orders
GROUP BY empid;

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

Функции обратного распределения

Функции обратного распределения, более известные под именем процентелей, выполняют вычисление, которое можно считать обратным к функциям распределения рангов. Как вы помните, функции распределения рангов вычисляют относительный ранг текущей строки в секции окна, который выражается числом от 0 до 1 (процентом). Функции обратного распределения принимают в качестве входных данных процент и возвращают значение из группы (или интерполированное значение), соответствующее этому проценту. Грубо говоря, если на вход поступает процент P и упорядочение в группе основано на ordcol, возвращенный процентиль является значением ordcol, для которого доля значений ordcol, которые меньше процентиля, равна P. Наверное самый известный процентиль — 0,5 (50-й процентиль), более известный как медиана. К примеру, если группа состоит из значений 2,3,7,1759,43112609, то процентиль 0,5 для нее равен 7.

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

SELECT groupcol, PERCENTILE_FUNCTION(0.5) WITHIN GROUP(ORDER BY ordcol) AS median
FROM T1
GROUP BY groupcol;

Обратите внимание на предложение WITHIN GROUP, содержащее определение упорядочения — это не оконная функция.

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

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

Существует два основных варианта функций обратного распределения: PERCENTILE_DISC и PERCENTILE_CONT.

Функция PERCENTILE_DISC (DISC означает «discrete distribution model», то есть «модель дискретного распределения») возвращает первое значение в группе, интегральное распределение которого больше или равно входному значению, при этом предполагается, что группа трактуется как секция окна с тем же упорядочением, которое определено в группе. Посмотрите на запрос в предыдущем разделе, где вычисляется процентильный ранг и интегральное распределение результатов экзаменов. В данном случае функция PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) вернет результат 75 для теста «Test ABC», потому что этот результат относится к интегральному распределению 0,556, а это первое значение, большее или равное входному числу 0,5. Ниже показан приведенный ранее результат, в котором специально выделена эта строка:

Работу функции PERCENTILE_CONT (CONT означает «continuous distribution model», «модель непрерывного распределения») объяснить сложнее. Пусть у нас сеть функция PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score). Допустим, что n — число строк в группе. Пусть g равно @pct*(n - 1), при этом i — целая, а f — дробная часть g. Допустим, что row0 и row1 — строки с номерами FLOOR(g) и CEILING(g) (нумерация начинается с нуля). Здесь предполагается, что номера строк вычисляются на основе того же секционирования и упорядочения окна, что используется при группировке и упорядочении, что и в функции PERCENTILE_CONT.

Тогда PERCENTILE_CONT вычисляется как row0.score + f * (row1.score - row0.score). Это интерполяция значений в двух строках в предположении непрерывного распределения (основанного на дробной части g).

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

Вот пример вычисления медианы результатов экзаменов, в котором обе функции распределения используются как оконные:

DECLARE @pct AS FLOAT = 0.5;

SELECT testid, score,
  PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
  PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores;

Вот еще один пример вычисления десятого процентиля (0,1):

DECLARE @pct AS FLOAT = 0.1;

SELECT testid, score,
  PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
  PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores;