Я неожиданно не смог найти функцию nth percentile для postgresql.
Я использую это через mondrian olap tool, поэтому мне просто нужна сводная функция, которая возвращает 95-й процентиль.
Я нашел эту ссылку:
http://www.postgresql.org/message-id/[email protected]
Но по какой-то причине код в этой функции процентиля в некоторых случаях возвращает нули с определенными запросами. Я проверил данные, и нет ничего странного в данных, которые могли бы вызвать это!
В PostgreSQL 9.4 теперь имеется встроенная поддержка процентилей, реализованная в Упорядоченных агрегатных функциях:
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)непрерывный процентиль: возвращает значение, соответствующее указанному фракция в упорядочении, интерполирование между соседними элементами ввода при необходимости
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)несколько непрерывных процентилей: возвращает массив результатов, соответствующих форма параметра фракций, с каждым непустым элементом заменяется значением, соответствующим этому процентилю
Подробнее см. документацию: http://www.postgresql.org/docs/current/static/functions-aggregate.html
и см. здесь несколько примеров: https://github.com/michaelpq/michaelpq.github.io/blob/master/_posts/2014-02-27-postgres-9-4-feature-highlight-within-group.markdown
Функция ntile здесь очень полезна. У меня есть таблица test_temp:
select * from test_temp
score
integer
3
5
2
10
4
8
7
12
select score, ntile(4) over (order by score) as quartile from temp_test;
score quartile
integer integer
2 1
3 1
4 2
5 2
7 3
8 3
10 4
12 4
ntile(4) over (order by score) упорядочивает столбцы по счету, разбивает его на четыре четные группы (если число делится равномерно) и присваивает номер группы в соответствии с порядком.
Так как у меня есть 8 чисел, они представляют 0, 12, 25, 37, 5, 50, 62, 5, 75 и 87.5-й процентили. Поэтому, если я принимаю только те результаты, когда quartile равно 2, у меня будут 25-й и 37-й процентиль.
with ranked_test as (
select score, ntile(4) over (order by score) as quartile from temp_test
)
select min(score) from ranked_test
where quartile = 2
group by quartile;
возвращает 4, третье наибольшее число в списке 8.
Если у вас была более крупная таблица и она использовалась ntile(100), то столбцом, который вы фильтруете, будет процентиль, и вы можете использовать тот же запрос, что и выше.
Как и в комментариях выше, решение здесь, просто убедитесь, что добавлены функции сортировки массива и функции percentile_cont!