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

PERCENTILE_DISC 开窗函数

PERCENTILE_DISC 是一种假定离散分布模型的逆分布函数。该函数具有一个百分比值和一个排序规范,并返回给定集合中的元素。

对于给定的百分比值 P,PERCENTILE_DISC 在 ORDER BY 子句中对表达式的值进行排序,并返回带有大于或等于 P 的最小累积分布值(相对于同一排序规范)的值。

您在 OVER 子句中只能指定 PARTITION 子句。

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

语法

PERCENTILE_DISC ( 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 表达式相同的数据类型。

示例

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

SELECT sellerid, qty, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY qty) OVER() AS MEDIAN FROM winsales; +----------+-----+--------+ | sellerid | qty | median | +----------+-----+--------+ | 3 | 10 | 20 | | 1 | 10 | 20 | | 1 | 10 | 20 | | 4 | 10 | 20 | | 3 | 15 | 20 | | 2 | 20 | 20 | | 2 | 20 | 20 | | 3 | 20 | 20 | | 1 | 30 | 20 | | 3 | 30 | 20 | | 4 | 40 | 20 | +----------+-----+--------+ SELECT sellerid, qty, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY qty) OVER(PARTITION BY sellerid) AS MEDIAN FROM winsales; +----------+-----+--------+ | sellerid | qty | median | +----------+-----+--------+ | 4 | 10 | 10 | | 4 | 40 | 10 | | 3 | 10 | 15 | | 3 | 15 | 15 | | 3 | 20 | 15 | | 3 | 30 | 15 | | 2 | 20 | 20 | | 2 | 20 | 20 | | 1 | 10 | 10 | | 1 | 10 | 10 | | 1 | 30 | 10 | +----------+-----+--------+

要在按卖家 ID 分区时为数量找到 PERCENTILE_DISC(0.25) 和 PERCENTILE_DISC(0.75),请使用以下示例。

SELECT sellerid, qty, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY qty) OVER(PARTITION BY sellerid) AS quartile1 FROM winsales; +----------+-----+-----------+ | sellerid | qty | quartile1 | +----------+-----+-----------+ | 4 | 10 | 10 | | 4 | 40 | 10 | | 2 | 20 | 20 | | 2 | 20 | 20 | | 3 | 10 | 10 | | 3 | 15 | 10 | | 3 | 20 | 10 | | 3 | 30 | 10 | | 1 | 10 | 10 | | 1 | 10 | 10 | | 1 | 30 | 10 | +----------+-----+-----------+ SELECT sellerid, qty, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY qty) OVER(PARTITION BY sellerid) AS quartile3 FROM winsales; +----------+-----+-----------+ | sellerid | qty | quartile3 | +----------+-----+-----------+ | 3 | 10 | 20 | | 3 | 15 | 20 | | 3 | 20 | 20 | | 3 | 30 | 20 | | 4 | 10 | 40 | | 4 | 40 | 40 | | 2 | 20 | 20 | | 2 | 20 | 20 | | 1 | 10 | 30 | | 1 | 10 | 30 | | 1 | 30 | 30 | +----------+-----+-----------+