PostgreSQL 百分位数计算及窗口函数

在本文中，我们将介绍如何使用窗口函数在 PostgreSQL 中进行百分位数计算。

什么是百分位数

百分位数是统计学中用来描述一组数据中某个特定百分比处的值。例如，如果我们有一组数据 [1, 2, 3, 4, 5]，那么第25百分位数就是3，表示有25%的数据小于等于3。

在 PostgreSQL 中，我们可以使用窗口函数来计算百分位数。

使用窗口函数计算百分位数

为了演示百分位数的计算，我们首先创建一个名为 “scores” 的表，该表包含学生姓名和他们的考试分数。以下是我们将在本文中使用的示例数据：

CREATE TABLE scores (
    student_name text,
    score int
);

INSERT INTO scores VALUES
    ('Alice', 85),
    ('Bob', 90),
    ('Charlie', 75),
    ('David', 92),
    ('Eve', 88),
    ('Frank', 82);

接下来，我们可以使用 PERCENT_RANK() 窗口函数来计算每个学生分数的百分位数。该函数返回一个介于 0 和 1 之间的值，表示当前行在结果集中的位置。

SELECT
    student_name,
    score,
    PERCENT_RANK() OVER (ORDER BY score) AS percentile
FROM
    scores;

上述查询将返回一个包含学生姓名、分数和百分位数的结果集。注意，我们使用 OVER 子句指定了一个排序条件，以便按照分数进行排序。

现在让我们执行上述查询，看看结果是什么：

 student_name | score | percentile 
--------------+-------+------------
 Charlie      |    75 |       0.00
 Frank        |    82 |       0.20
 Alice        |    85 |       0.40
 Eve          |    88 |       0.60
 Bob          |    90 |       0.80
 David        |    92 |       1.00

可以看到，每个学生的分数都有对应的百分位数，从0到1进行了归一化。

使用窗口函数计算指定百分位数

在上一节中，我们使用了 PERCENT_RANK() 窗口函数来计算每个学生分数的百分位数。然而，有时我们可能需要计算指定的百分位数，而不是返回介于 0 和 1 之间的值。在这种情况下，我们可以使用 PERCENTILE_CONT() 或者 PERCENTILE_DISC() 窗口函数。

PERCENTILE_CONT() 函数计算连续分布的指定百分位数。例如，如果我们要计算第50百分位数（中位数），我们可以这样写：

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER () AS median
FROM
    scores;

上述查询将返回一个包含中位数的结果。PERCENTILE_CONT() 函数使用 WITHIN GROUP 子句指定排序条件，并且 OVER () 表示计算所有行的百分位数。

PERCENTILE_DISC() 函数计算离散分布的指定百分位数。例如，如果我们要计算第50百分位数（中位数），我们可以这样写：

SELECT
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) OVER () AS median
FROM
    scores;

上述查询也将返回一个包含中位数的结果。PERCENTILE_DISC() 函数使用 WITHIN GROUP 子句指定排序条件，并且 OVER () 表示计算所有行的百分位数。

总结

本文介绍了如何使用窗口函数在 PostgreSQL 中进行百分位数的计算。我们首先了解了什么是百分位数，然后演示了如何使用 PERCENT_RANK() 窗口函数来计算每个学生分数的百分位数。接着，我们介绍了如何使用 PERCENTILE_CONT() 和 PERCENTILE_DISC() 窗口函数来计算指定的百分位数。通过掌握这些技巧，我们能够更好地处理和分析数据，从而得到有价值的洞察。