postgresql中的第n个百分点计算 [英] nth percentile calculations in postgresql

问题描述

我通过mondrian olap工具使用这个工具，所以我只需要一个聚合函数，它返回第95百分位。

我确实找到了这个链接：

http://www.postgresql.org/message-id/[e-mail]

但由于某些原因，该百分位函数中的代码在某些情况下会返回空值，并带有某些查询。我已经检查过数据，并且数据中没有什么奇怪的东西会导致这种情况！

解决方案

使用PostgreSQL 9.4本地支持百分位数，在 Ordered-Set Aggregate Functions 中实现：

  percentile_cont（fraction）WITHIN GROUP（ORDER BY sort_expression）
  

连续百分位数：返回一个对应于如果需要，在相邻输入项之间插入

  percentile_cont（分数） WITHIN GROUP（ORDER BY sort_expression）
  

多个连续百分位数：返回匹配
的结果数组fraction参数的形状，其中每个非空元素
被对应于该百分位数的值替换

请参阅有关更多详细信息： 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

I've been surprisingly unable to find an nth percentile function for postgresql.

I am using this via mondrian olap tool so i just need an aggregate function which returns a 95th percentile.

I did find this link:

http://www.postgresql.org/message-id/[e-mail]

But for some reason the code in that percentile function is returning nulls in some cases with certain queries. I've checked the data and there's nothing odd in the data that would seem to cause that!

解决方案

With PostgreSQL 9.4 there is native support for percentiles now, implemented in Ordered-Set Aggregate Functions:

percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) 

continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed

percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)

multiple continuous percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile

See the documentation for more details: http://www.postgresql.org/docs/current/static/functions-aggregate.html

and see here for some examples: https://github.com/michaelpq/michaelpq.github.io/blob/master/_posts/2014-02-27-postgres-9-4-feature-highlight-within-group.markdown

这篇关于postgresql中的第n个百分点计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！