Медианы в T-SQL

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

Ранее я рассказывал, как вычисляются процентили. Я говорил, что 50-й процентиль обычно называется медианой и, грубо говоря, представляет собой такое значение из набора, для которого 50% всех остальных значений набора данных меньше этого значения. Я показал решения для вычисления любых процентилей как в SQL Server 2012, так и предыдущих версиях SQL Server. Здесь я только напомню вам решение в SQL Server 2012 с использованием функции PERCENTILE_CONT (CONT здесь означает модель непрерывного распределения), а затем покажу интересные решения для вычисления медианы в более ранних версиях SQL Server.

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

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

WITH C AS
(
  SELECT testid,
    ROW_NUMBER() OVER(PARTITION BY testid ORDER BY (SELECT NULL)) AS rownum,
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS median
  FROM Stats.Scores
)
SELECT testid, median
FROM C
WHERE rownum = 1;

Оно немного неуклюжее, но свою работу делает.

До SQL Server 2012 приходилось быть более изобретательным, тем не менее для решения этой задачи все равно можно было использовать оконные функции. Одно из решений заключалось в вычислении для каждой строки ее позиции в результатах экзамена при упорядочении по оценкам (назовем это pos) и числу результатов для соответствующего экзамена (назовем это cnt). Для вычисления pos применяется функция ROW_NUMBER, а для расчета cnt - оконная функция агрегирования COUNT. Затем отбираются только строки, которые должны участвовать в вычислении медианы, а именно строки, у которых pos равно (cnt + 1) / 2 или (cnt + 2) / 2. Заметьте, что в этих выражениях используется целочисленное деление, а дробная часть отбрасывается. При нечетном числе элементов оба выражения возвращают одинаковое срединное значение.

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

WITH C AS
(
  SELECT testid, score,
    ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score) AS pos,
    COUNT(*) OVER(PARTITION BY testid) AS cnt
  FROM Stats.Scores
)
SELECT testid, AVG(1. * score) AS median
FROM C
WHERE pos IN( (cnt + 1) / 2, (cnt + 2) / 2 )
GROUP BY testid;

Другое интересное решение задачи в версиях, предшествующих SQL Server 2012, предусматривает вычисление двух номеров строк: первый при упорядочении по возрастанию по score и studentid (studentid добавлено для детерминизма), а второй - при упорядочении по убыванию. Вот код вычисления этих номеров и результат работы запроса:

SELECT testid, score,
  ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score, studentid) AS rna,
  ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score DESC, studentid DESC) AS rnd
FROM Stats.Scores;

Можно ли обобщить правило, определяющее строки, которые должны участвовать в вычислении медианы?

Заметим, что при нечетном количестве строк, медиана располагается там, где номера строк совпадают. При четном числе элементов медиана находится там, где разница между двумя номерами строк равна единице. Объединить два правила можно так: медиана находится в строках, где абсолютная разница между номерами строк меньше или равна единице. Вот готовое решение, основанное на этом обобщенном правиле:

WITH C AS
(
  SELECT testid, score,
    ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score, studentid) AS rna,
    ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score DESC, studentid DESC) AS rnd
  FROM Stats.Scores
)
SELECT testid, AVG(1. * score) AS median
FROM C
WHERE ABS(rna - rnd) <= 1
GROUP BY testid;