描述一个数列

概述

当我们拿到一个全新的数据集时，通常首先要做的就是熟悉它。有些人将这个过程称为探索性数据分析（EDA）。SQL 提供了多种函数来生成描述性统计信息。

序列（series） 是指一维的值列表。例如，我们可以使用 VALUES 生成一个简单的序列：

1SELECT * FROM (VALUES (1), (2), (3)) AS t(n)

该查询使用 VALUES 生成一个数字列表：1、2 和 3。

另一种理解序列的方式是将其看作表中的一列。例如，如果我们有一张“sales”表，“sale_date”列可以被视为时间戳的序列，“charged_amount”列则可视为数值的序列。

在本课中，我们将学习一些聚合函数，帮助我们更好地了解序列中的数据。

统计行数

我们最常想了解的第一个问题是：有多少行数据？在 SQL 中，可以使用聚合函数 COUNT 来回答这个问题：

1WITH series AS (
2    SELECT * FROM (VALUES (1), (2), (3)) AS t(n)
3)
4SELECT 
5    COUNT(*) 
6FROM 
7    series;

要统计序列中的行数，可以使用聚合函数 COUNT(*)。

最小值与最大值

在处理数据序列时，了解数值的范围非常有用。例如，若有一个日期序列，我们希望知道其中最早和最晚的日期；对于价格序列，则希望找出最低价和最高价。

在 SQL 中，我们可以使用聚合函数 MIN 和 MAX 来获取序列的取值范围：

1WITH series AS (
2    SELECT * FROM (VALUES (1), (2), (3)) AS t(n)
3)
4SELECT 
5    MIN(n),
6    MAX(n) 
7FROM 
8    series;

这样我们对数据就有了更清晰的认识。

平均值与方差

仅知道序列的行数和数值范围，并不能完整反映数据的分布情况。另一个有用的描述性统计量是平均值：即所有数值之和除以数值个数所得的结果。以下是计算序列平均值的示例：

1WITH series AS (
2    SELECT * FROM (VALUES (1), (2), (3)) AS t(n)
3)
4SELECT 
5    AVG(n)
6FROM
7    series;

然而，仅靠平均值仍不足以全面了解数据的分布。我们还可以计算方差：即各个数值与平均值之差的平方的平均数。方差用于衡量数据围绕平均值的离散程度。此外还有标准差：方差的平方根。标准差越小，说明序列中的数值越接近平均值。以下是对序列同时计算平均值、方差和标准差的示例：

1WITH series AS (
2    SELECT * FROM (VALUES (1), (2), (3)) AS t(n)
3)
4SELECT 
5    AVG(n),
6    VARIANCE(n),
7    STDDEV(n)
8FROM
9    series;

中位数

均值是描述数据序列的一个常用指标，但它被认为是非稳健的。为了理解“稳健”的含义，请考虑以下序列：

注意：此处的“平均值”指的是数据的算术平均（均值）。

1SELECT AVG(n) FROM (VALUES (1), (2), (3), (4)) AS t(n);

序列 1、2、3、4 的平均值为 2.5。

现在，我们在序列中加入一个极大值，再次计算平均值：

1SELECT AVG(n) FROM (VALUES (1), (2), (3), (4), (1000)) AS t(n);

加入 1000 后，平均值变为 202，远高于之前的值。这表明平均值极易受到序列中极端值的影响，因此不具备稳健性。正因如此，在包含异常值或离群点的数据序列中，使用非稳健的统计量并不理想。

一种比均值更具稳健性的替代指标是中位数：即一半数值小于它，另一半大于它的那个值。

中位数是将数据集按升序排列后位于正中间的数值。在 SQL 中，我们可以通过 percentile_cont 函数来计算中位数。

percentile_cont 是一种有序集聚合函数，其运算依赖于特定排序，排序由 WITHIN GROUP (ORDER BY column) 子句决定。要计算序列的中位数，可以使用如下语句：

1WITH series AS (
2    SELECT * FROM (VALUES (1), (2), (3)) AS t(n)
3)
4SELECT 
5    percentile_cont(0.5) WITHIN GROUP (ORDER BY n)
6FROM
7    series;

序列 1、2、3 的中位数是 2。这里我们通过 WITHIN GROUP (ORDER BY n) 指定按照 n 的值进行排序。

为了展示中位数为何比平均值更不容易受极端值影响，我们可以重新运行前面的查询，并同时输出中位数：

1SELECT
2  avg(n) as average,
3  percentile_cont(0.5) WITHIN GROUP (ORDER BY n) AS median 
4FROM (
5  VALUES (1), (2), (3), (4)
6) AS t(n);

1SELECT 
2  avg(n),
3  percentile_cont(0.5) WITHIN GROUP (ORDER BY n) AS median 
4FROM (
5  VALUES (1), (2), (3), (4), (1000)
6) AS t(n);

加入极端值 1000 后，中位数从 2.5 变为 3，而平均值则从 2.5 飙升至 202。由此可见，中位数比平均值更加稳健——极端值对其影响远小于对平均值的影响。

percentile_cont 函数用于计算有序集合中的连续百分位数，其返回值不一定存在于原始序列中。以前面的例子为例，序列包含 1、2、3、4，计算出的中位数为 2.5，但该值并不存在于原序列中。

还有一种方式是计算离散型的百分位数。与连续型不同，离散型百分位数返回的是满足“一半数值大于它，一半小于它”的第一个实际存在的序列值。

在 PostgreSQL 中，可以使用 percentile_disc 函数来计算离散百分位数。以下示例展示了连续型与离散型百分位数的区别：

1SELECT 
2    percentile_cont(0.5) WITHIN GROUP (ORDER BY n) AS median_continuous,
3    percentile_disc(0.5) WITHIN GROUP (ORDER BY n) AS median_discrete
4FROM 
5    (VALUES (1), (2), (3), (4)) AS t(n);

其中，连续型 0.5 百分位数为 2.5，这个值不在原始序列中；而离散型 0.5 百分位数为 2，虽然不完全居中，但它是序列中第一个符合中位数定义的值。

百分位数

前一节中我们计算了中位数。实际上，中位数只是 50% 百分位数的一个特例。利用相同的函数，我们也可以计算其他百分位数。例如，计算 25% 百分位数：

1SELECT 
2    percentile_disc(0.25) WITHIN GROUP (ORDER BY n) AS percentile_25
3FROM 
4    (VALUES (1), (2), (3), (4)) AS t(n);

percentile_disc 和 percentile_cont 函数还支持传入数组参数，从而一次性计算多个百分位数。例如，可以在一条表达式中同时计算 25%、50% 和 75% 百分位数：

1SELECT 
2  percentile_disc(array[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY n) AS percentiles 
3FROM 
4  (VALUES (1), (2), (3), (4)) AS t(n);

当向百分位数函数传入数组时，返回结果也是一个数组，其中每个元素对应输入数组中的相应百分位。

我们甚至可以用百分位数函数来计算序列的最小值和最大值：

1SELECT 
2  min(n),
3  max(n),
4  percentile_disc(array[0, 1]) WITHIN GROUP (ORDER BY n) AS percentiles 
5FROM 
6  (VALUES (1), (2), (3), (4)) AS t(n);

0% 百分位数等价于序列的最小值，即 MIN 的结果；100% 百分位数等价于最大值，即 MAX 的结果。