PERCENTILE_CONT 开窗函数 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅中国的 Amazon Web Services 服务入门

# PERCENTILE_CONT 开窗函数

PERCENTILE_CONT 是一种假定连续分布模型的逆分布函数。该函数具有一个百分比值和一个排序规范，并返回一个在有关排序规范的给定百分比值范围内的内插值。

PERCENTILE_CONT 在对值进行排序后计算值之间的线性内插。通过在聚合组中使用百分比值 `(P)` 和非 null 行数 `(N)`，该函数会在根据排序规范对行进行排序后计算行号。根据公式 `(RN)` 计算此行号 `RN = (1+ (P*(N-1))`。聚合函数的最终结果通过行号 `CRN = CEILING(RN)``FRN = FLOOR(RN)` 的行中的值之间的线性内插计算。

```(CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)```.

PERCENTILE_CONT 是仅计算节点函数。如果查询不引用用户定义的表或 Amazon Redshift 系统表，该函数将返回错误。

## Syntax

``````PERCENTILE_CONT ( percentile )
WITHIN GROUP (ORDER BY expr)
OVER (  [ PARTITION BY expr_list ]  )
``````

## Arguments

percentile

WITHIN GROUP ( ORDER BY expr)

OVER

PARTITION BY expr

## Returns

INT2、INT4、INT8、NUMERIC、DECIMAL DECIMAL
FLOAT、DOUBLE DOUBLE
DATE DATE
TIMESTAMP TIMESTAMP

## 使用说明

``````select salesid, sum(pricepaid), percentile_cont(0.6)
within group (order by sum(pricepaid) desc) over()
from sales where salesid < 10 group by salesid;``````

``````select salesid, sum(pricepaid), percentile_cont(0.6)
within group (order by sum(pricepaid)::decimal(30,2) desc) over()
from sales where salesid < 10 group by salesid;``````

## Examples

``````select sellerid, qty, percentile_cont(0.5)
within group (order by qty)
over() as median from winsales;

sellerid | qty | median
----------+-----+--------
1 |  10 |   20.0
1 |  10 |   20.0
3 |  10 |   20.0
4 |  10 |   20.0
3 |  15 |   20.0
2 |  20 |   20.0
3 |  20 |   20.0
2 |  20 |   20.0
3 |  30 |   20.0
1 |  30 |   20.0
4 |  40 |   20.0
(11 rows)
``````
``````select sellerid, qty, percentile_cont(0.5)
within group (order by qty)
over(partition by sellerid) as median from winsales;

sellerid | qty | median
----------+-----+--------
2 |  20 |   20.0
2 |  20 |   20.0
4 |  10 |   25.0
4 |  40 |   25.0
1 |  10 |   10.0
1 |  10 |   10.0
1 |  30 |   10.0
3 |  10 |   17.5
3 |  15 |   17.5
3 |  20 |   17.5
3 |  30 |   17.5
(11 rows)
``````

``````SELECT sellerid, state, sum(qtysold*pricepaid) sales,
percentile_cont(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over(),
percentile_disc(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over()
from sales s, users u
where s.sellerid = u.userid and state = 'WA' and sellerid < 1000
group by sellerid, state;

sellerid | state |  sales  | percentile_cont | percentile_disc
----------+-------+---------+-----------------+-----------------
127 | WA    | 6076.00 |         2044.20 |         1531.00
787 | WA    | 6035.00 |         2044.20 |         1531.00
381 | WA    | 5881.00 |         2044.20 |         1531.00
777 | WA    | 2814.00 |         2044.20 |         1531.00
33 | WA    | 1531.00 |         2044.20 |         1531.00
800 | WA    | 1476.00 |         2044.20 |         1531.00
1 | WA    | 1177.00 |         2044.20 |         1531.00
(7 rows)``````