了解 PostgreSQL 中的 percentile_cont() 和 percentile_disc()

PostgreSQL 有两个函数可以计算任何百分比的值列表的百分位数：percentile_cont() 和 percentile_disc()。这两个函数的工作原理类似，但它们在生成最终结果的方式上有所不同。两者都与 WITHIN GROUP 子句返回的有序集聚合一起使用。

percentile_disc() 函数从输入集中返回一个最接近请求百分位数的值。返回的值实际上存在于集合中。 

以下是 percentile_disc() 语法

SELECT
	percentile_disc(<fraction double precision>) WITHIN GROUP (<sort_expression>)
FROM <table>;

percentile_cont() 函数根据分布返回多个值之间的插值。它更准确，但它可能会在输入集中返回两个值之间的小数值。

percentile_cont() 语法

SELECT
	percentile_cont(<fraction double precision>) WITHIN GROUP (<sort_expression>)
FROM <table>;

示例

• 计算中位数

• 计算多个百分位数

• 计算一系列百分位数

对于以下示例，我们将使用存储在名为 city_data: 的表中的这组天气数据

计算中位数

中位数也称为第 50 个百分位数。您可以使用以下查询从数据集中计算它

SELECT
	percentile_disc(0.5) WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果是

因为查询使用了 percentile_disc()，所以结果是数据集中存在的值。如果要查找真正的中位数，它不是此数据中的值，则必须使用 percentile_cont()。以下是查询

SELECT
	percentile_cont(0.5) WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果如下

但由于有两个城市，您可能希望通过添加 GROUP BY 子句来计算每个城市的中位数温度。以下是该查询

SELECT
	city,
	percentile_cont(0.5) WITHIN GROUP (
ORDER BY
	temperature)
FROM
	city_data
GROUP BY
	city;

结果是

计算多个百分位数

在本例中，我们将使用名为 conditions 的数据库表，其中包含以下值

假设我们想要计算每个设备湿度的各种百分位数。以下是一个示例查询

SELECT
	device_id,
	percentile_cont(0.25) WITHIN GROUP(
ORDER BY
	humidity) AS percentile_25,
	percentile_cont(0.50) WITHIN GROUP(
ORDER BY
	humidity) AS percentile_50,
	percentile_cont(0.75) WITHIN GROUP(
ORDER BY
	humidity) AS percentile_75,
	percentile_cont(0.95) WITHIN GROUP(
ORDER BY
	humidity) AS percentile_95
FROM
	conditions
GROUP BY
	device_id ;

以下是结果的一部分

计算一系列百分位数

在本例中，我们将回到最初的 city_data 数据集，因为此查询在大型数据集上运行可能需要很长时间。我们将使用 generate_series() 函数创建每个整数百分比，然后在 percentile_cont 函数中使用这些值。以下是查询

SELECT
	city,
	percentile,
	percentile_cont(p) WITHIN GROUP (
ORDER BY
	temperature)
FROM
	city_data,
	generate_series(0.01, 1, 0.01) AS percentile
GROUP BY
	city, percentile;

由于查询生成了 200 行结果，以下是一些结果的选取

为什么要使用 Timescale 的 approx_percentile() 函数而不是 PostgreSQL 的百分位数函数？

在大型数据集（例如 Timescale 数据库中的时序数据）上计算百分位数可能涉及大量昂贵的计算。它可能会增加数据库的内存占用量，导致更高的网络成本，并使流数据变得不可行。聚合函数也不能部分化或并行化。

很多时候，你不需要这种类型的准确性，而近似百分位数计算就足够接近了。这就是 Timescale 引入 approx_percentile() 超函数 的原因。approx_percentile() 函数实现了 UDDSketch 算法，该算法使用修改后的直方图来近似分布的形状。这允许在不需要使用所有数据或在返回结果之前对其进行排序的情况下计算“足够好”的百分位数。

approx_percentile() 语法

approx_percentile(
    percentile DOUBLE PRECISION,
    sketch  uddsketch
) RETURNS DOUBLE PRECISION

第二个参数是要对其执行 approx_percentile 操作的草图，通常从 percentile_agg() 调用返回。以下是一个示例查询

SELECT
    approx_percentile(0.01, percentile_agg(data))
FROM generate_series(0, 100) data;

结果

后续步骤

要详细了解如何在 PostgreSQL 中使用 percentile_cont() 和 percentile_disc() 函数，请参阅 PostgreSQL 文档。

• 要详细了解 Timescale 的 approx_percentile() 函数，请阅读 Timescale 文档 中的更多信息。

有关如何在查询中使用这些函数的示例，请参阅 Timescale 文档的以下部分

• 其他 approx_percentile() 文档

• 有关聚合函数和访问器函数的信息

• 高级百分位数聚合

• 百分位数近似高级聚合方法

• 百分位数近似