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).

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

语法

PERCENTILE_CONT(percentile) WITHIN GROUP(ORDER BY expr)

参数

percentile

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

expr

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

返回值

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

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

使用说明

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

如果语句包括对基于排序的聚合函数 (LISTAGG、PERCENTILE_CONT 或 MEDIAN) 的多个调用,则它们必须全都使用相同的 ORDER BY 值。请注意,MEDIAN 对表达式值应用隐式排序依据。

例如,以下语句将返回错误。

SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid; An error occurred when executing the SQL command: SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid; ERROR: within group ORDER BY clauses for aggregate functions must be the same

以下语句将成功运行。

SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(salesid) FROM sales GROUP BY salesid, pricepaid;

示例

以下示例使用 TICKIT 示例数据库。有关更多信息,请参阅示例数据库

以下示例显示 PERCENTILE_CONT(0.5) 生成与 MEDIAN 相同的结果。

SELECT TOP 10 DISTINCT sellerid, qtysold, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold), MEDIAN(qtysold) FROM sales GROUP BY sellerid, qtysold; +----------+---------+-----------------+--------+ | sellerid | qtysold | percentile_cont | median | +----------+---------+-----------------+--------+ | 2 | 2 | 2 | 2 | | 26 | 1 | 1 | 1 | | 33 | 1 | 1 | 1 | | 38 | 1 | 1 | 1 | | 43 | 1 | 1 | 1 | | 48 | 2 | 2 | 2 | | 48 | 3 | 3 | 3 | | 77 | 4 | 4 | 4 | | 85 | 4 | 4 | 4 | | 95 | 2 | 2 | 2 | +----------+---------+-----------------+--------+

以下示例为 SALES 表中每个 sellerid 的销售量找到 PERCENTILE_CONT(0.5) 和 PERCENTILE_CONT(0.75)。

SELECT sellerid, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold) as pct_05, PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY qtysold) as pct_075 FROM sales GROUP BY sellerid ORDER BY sellerid LIMIT 10; +----------+--------+---------+ | sellerid | pct_05 | pct_075 | +----------+--------+---------+ | 1 | 1.5 | 1.75 | | 2 | 2 | 2.25 | | 3 | 2 | 3 | | 4 | 2 | 2 | | 5 | 1 | 1.5 | | 6 | 1 | 1 | | 7 | 1.5 | 1.75 | | 8 | 1 | 1 | | 9 | 4 | 4 | | 12 | 2 | 3.25 | +----------+--------+---------+

要验证第一个 sellerid 的上一次查询的结果,请使用以下示例。

SELECT qtysold FROM sales WHERE sellerid=1; +---------+ | qtysold | +---------+ | 2 | | 1 | +---------+