Как эмулировать агрегатную функцию MEDIAN () с помощью обратных функций распределения

Некоторые базы данных достаточно хороши для реализации MEDIAN()агрегатной функции. Помните, что MEDIAN()он немного отличается от (и часто более полезен, чем) MEAN()или AVG()(в среднем).

В то время как среднее значение рассчитывается как SUM(exp) / COUNT(exp), MEDIAN()говорит вам, что 50% всех значений в выборке выше, чем в MEDIAN()то время как другие 50% набора ниже, чем MEDIAN().

Итак, другими словами, если вы берете следующий запрос:

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 3   FROM DUAL
)
SELECT
  avg(value),
  median(value)
FROM
  t;

… Тогда и среднее, и среднее значение одинаковы:

avg   median
2     2

Но если вы сильно искажаете свои данные следующим образом:

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 100 FROM DUAL
)
SELECT
  avg(value),
  median(value)
FROM
  t;

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

avg      median
34.333   2

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

Изображение лицензии CC-BY-SA 3.0. Загрузил в Википедии по Cmglee

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

Этот блог не о политике, а о Java и SQL, поэтому давайте вернемся к подсчету фактов.

Использование прецентилей в SQL

Как мы уже видели, MEDIAN()образец делится на две группы одинакового размера и принимает значение «между» этими двумя группами. Это конкретное значение также называется 50-й процентиль, потому что 50% всех значений в выборке ниже, чем MEDIAN(). Таким образом, мы можем установить:

• MIN(exp): 0-процентиль
• MEDIAN(exp): 50-процентиль
• MAX(exp): Сотый процентиль

Все вышеперечисленное является частным случаем процентилей и, хотя MIN()и MAX()поддерживается во всех базах данных SQL (и стандарте SQL), MEDIAN()не входит в стандарт SQL и поддерживается только следующими базами данных jOOQ :

• CUBRID
• HSQLDB
• оракул
• Sybase SQL Anywhere

Существует еще один способ вычисления MEDIAN()конкретного и любого вида процентиля в целом в стандарте SQL, и, поскольку PostgreSQL 9.4 также используется в PostgreSQL с использованием…

Упорядоченный набор агрегатных функций

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

Одной из таких функций является стандартная percentile_contфункция SQL , которая принимает процентиль в качестве аргумента, а затем принимает дополнительное WITHIN GROUPпредложение, которое принимает ORDER BYпредложение в качестве аргумента. Эти конкретные функции упорядоченного набора также называют обратными функциями распределения , потому что мы хотим найти, где находится конкретный процентиль в распределении всех значений в образце ( если вас не пугает математика, ознакомьтесь со статьей в Википедии )

Итак, в PostgreSQL 9.4+ MEDIAN()функцию можно эмулировать так:

WITH t(value) AS (
  SELECT 1   UNION ALL
  SELECT 2   UNION ALL
  SELECT 100
)
SELECT
  avg(value),
  percentile_cont(0.5) WITHIN GROUP (ORDER BY value)
FROM
  t;

Этот интересный синтаксис стандартизирован и может быть известен некоторым из вас из Oracle LISTAGG () , который позволяет объединять значения в объединенные строки:

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 100 FROM DUAL
)
SELECT
  listagg(value, ', ') WITHIN GROUP (ORDER BY value)
FROM
  t;

Этот запрос дает просто:

listagg
---------
1, 2, 100

С другой стороны: LISTAGG()это, конечно, совершенно бесполезно, потому что он возвращает VARCHAR2, который снова имеет максимальную длину 4000 в Oracle. Бесполезный…

Эмуляция из коробки с JOOQ

Как всегда, JOOQ будет эмулировать подобные вещи из коробки. Вы можете использовать DSL.median()функцию, или с выходом jOOQ 3.6, новую DSL.percentileCont()функцию для получения того же значения:

DSL.using(configuration)
   .select(
       median(T.VALUE),
       percentileCont(0.5).withinGroupOrderBy(T.VALUE)
   )
   .from(T)
   .fetch();