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

PERCENTILE_CONT 开窗函数

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

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

最终结果将如下所示。

如果 (CRN = FRN = RN),则结果为 (value of expression from row at RN)

否则,结果将如下所示:

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

您在 OVER 子句中只能指定 PARTITION 子句。如果为每个行指定 PARTITION,则 PERCENTILE_CONT 会返回位于给定分区内一组值中的指定百分比范围内的值。

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

语法

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

参数

percentile

介于 0 和 1 之间的数字常数。计算中将忽略 Null。

WITHIN GROUP ( ORDER BY expr)

指定用于排序和计算百分比的数字或日期/时间值。

OVER

指定窗口分区。OVER 子句不能包含窗口排序或窗口框架规范。

PARTITION BY expr

设置 OVER 子句中每个组的记录范围的可选参数。

返回值

返回类型由 WITHIN GROUP 子句中的 ORDER BY 表达式的数据类型决定。下表显示了每种个 ORDER BY 表达式数据类型的返回类型。

输入类型 返回类型
INT2、INT4、INT8、NUMERIC、DECIMAL DECIMAL
FLOAT、DOUBLE DOUBLE
DATE DATE
TIMESTAMP TIMESTAMP

使用说明

如果 ORDER BY 表达式是使用 38 位最大精度定义的 DECIMAL 数据类型,则 PERCENTILE_CONT 可能将返回不准确的结果或错误。如果 PERCENTILE_CONT 函数的返回值超过 38 位,则结果将截断以符合规范,这将导致精度降低。如果在插值期间,中间结果超出最大精度,则会发生数值溢出且函数会返回错误。要避免这些情况,建议使用具有较低精度的数据类型或将 ORDER BY 表达式转换为较低精度。

例如,带 DECIMAL 参数的 SUM 函数返回 38 位的默认精度。结果的小数位数与参数的小数位数相同。因此,例如,DECIMAL(5,2) 列的 SUM 返回 DECIMAL(38,2) 数据类型。

以下示例在 PERCENTILE_CONT 函数的 ORDER BY 子句中使用 SUM 函数。PRICEPAID 列的数据类型是 DECIMAL (8,2),因此 SUM 函数返回 DECIMAL(38,2)。

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

要避免潜在的精度降低或溢出错误,请将结果转换为具有较低精度的 DECIMAL 数据类型,如以下示例所示。

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;

示例

以下示例使用 WINSALES 表。有关 WINSALES 表的说明,请参阅窗口函数示例的示例表

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)

以下示例计算华盛顿州的卖家的门票销售的 PERCENTILE_CONT 和 PERCENTILE_DISC。

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)