Сортирующие агрегатные функции

Эти функции иногда называются функциями «обратного распределения». Их агрегированные входные данные формируются указанием ORDER BY, а кроме того они могут принимать не агрегируемый непосредственный аргумент, который вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых данных. Для тех функций, которые принимают параметр fraction, его значение должно быть между 0 и 1; в противном случае выдаётся ошибка. Однако если значение fraction — NULL, они выдают NULL в результате.

mode() within group (order by anyelement) → anyelement

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

Простой пример, где часть значений указана один раз и два значения (3 и 4) повторяются по 2 раза.

select mode() within group (order by x)
from unnest(array[1, 2, 3, 3, 4, 4, 5]) x

mode|
----+
   3|

Нужно получить самое частое значение платежа

select mode() within group (order by amount)
from payment

mode|
----+
4.99|

percentile_cont ( fraction double precision ) within group ( order by double precision ) → double precision и percentile_cont ( fraction double precision ) within group ( order by interval ) → interval

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

Получение значения происходит по формуле: R = p ⋅ (N − 1) + 1

• R — непрерывный ранг,
• p — желаемый процентиль,
• N — общее количество элементов в выборке.

R делится на: k — целая часть R (индекс элемента, меньшего или равного процентилю) и d — дробная часть R (вес для интерполяции).
Если R является целым числом, то процентиль равен значению элемента на позиции R: P = x_R.
Если R не целое, выполните линейную интерполяцию между x_k и x_k+1: P = x_k + d ⋅ (x_k+1 − x_k)

Простой пример.

select percentile_cont(0.5) within group (order by x)
from unnest(array[10, 20, 30, 40]) x

N = 4, p = 0.5, R = 0.5 ⋅ (4 - 1) + 1 = 2.5
Так как R не целое, то k = 2, а d = 0.5 и нужно применить интерполяцию:
P = 20 + 0.5 ⋅ (30 - 20) = 20 + 5 = 25

percentile_cont|
---------------+
            25|

Нужно получить медиану (то есть 50 процентиль) всех платежей:

select percentile_cont(0.5) within group (order by amount)
from payment

percentile_cont|
---------------+
           3.99|

percentile_cont ( fractions double precision[] ) within group ( order by double precision ) → double precision[] и percentile_cont ( fractions double precision[] ) within group ( order by interval ) → interval[]

Вычисляет множественные непрерывные процентили. Возвращает массив той же размерности, что имеет параметр fractions, в котором каждый отличный от NULL элемент заменяется соответствующим данному перцентилю значением (возможно интерполированным).

Простой пример.

select percentile_cont(array[0.2, 0.4, 0.6]) within group (order by x)
from unnest(array[1, 2, 3, 4]) x

Так как R не целое, то будет применена интерполяция и получены результаты в соответствии с формулой из примера выше. 
N = 4, p = 0.2, R = 0.2 ⋅ (4 - 1) + 1 = 1.6
Так как R не целое, то k = 1, а d = 0.6
P = 1 + 0.6 ⋅ (2 - 1) = 1.6

percentile_cont|
---------------+
{1.6,2.2,2.8}  |

Нужно получить непрерывное распределение размеров платежей по квартилям и 95 процентилю:

select percentile_cont(array[0.25, 0.5, 0.75, 0.95]) within group (order by amount)
from payment

percentile_cont      |
---------------------+
{2.99,3.99,4.99,8.99}|

percentile_disc ( fraction double precision ) within group ( order by anyelement ) → anyelement

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

Получение значения происходит по формуле: R = p ⋅ N

• R — непрерывный ранг,
• p — желаемый процентиль,
• N — общее количество элементов в выборке.

Если R дробное значение, то его нужно округлить вверх до ближайшего целого числа: C = ⌈R⌉.
Искомое значение будет на позиции: P = x_C

Простой пример.

select percentile_disc(0.5) within group (order by x)
from unnest(array[1, 2, 3, 4]) x

N = 4, p = 0.5, R = 0.5 ⋅ 4 = 2
Так как R целое, то получаем 2 элемент.

percentile_disc|
---------------+
              2|

Нужно получить дискретный 50 процентиль размеров платежей:

select percentile_disc(0.5) within group (order by amount)
from payment

percentile_disc|
---------------+
           3.99|

percentile_disc ( fractions double precision[] ) within group ( order by anyelement ) → anyarray

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

Простой пример.

select percentile_disc(array[0.2, 0.4, 0.6]) within group (order by x)
from unnest(array[1, 2, 3, 4]) x

Расчеты производятся по формуле из примера выше. 
R = 0.2 ⋅ 4 = 0.8 и округляем в большую сторону - 1
R = 0.4 ⋅ 4 = 1.6 и округляем в большую сторону - 2
R = 0.6 ⋅ 4 = 2.4 и округляем в большую сторону - 3

percentile_disc|
---------------+
{1,2,3}        |

Нужно получить дискретное распределение размеров платежей по квартилям и 95 процентилю:

select percentile_disc(array[0.25, 0.5, 0.75, 0.95]) within group (order by amount)
from payment

percentile_disc      |
---------------------+
{2.99,3.99,4.99,8.99}|

Заключение

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

percentile_disc всегда возвращает существующие значения из набора данных через округление вверх. Он менее точен для непрерывных данных, но гарантирует, что результат реально присутствует в выборке.

Ключевое различие:

• cont = интерполяция → "теоретическое" значение
• disc = округление → "реальное" значение