第6章 使用SQL实现基本的算术和统计¶
如果你的数据包含我们在第4章中探讨过的任何一种数字数据类型——比如整数、小数或者浮点数，那么你迟早需要在分析里面包含某些计算。你可能想要知道同一列中所有美元数值的平均数，又或者将每行中两列的值相加以得出总和。SQL不仅可以完成上述计算，它从处理基本算术到完成高级统计都不在话下。
本章将从基础算术开始，逐渐过渡至数学函数和入门统计。此外本章还会讨论与百分比以及百分比变化相关的计算。本章中出现的某些练习将会用到第5章中导入的2019年美国人口普查估算数据。
了解数学运算符和函数¶
为了避免你因为时间久远而忘记过去学习过的某些知识，我们将从小学时学习的基础数学知识开始。表6-1展示了我们在计算中最经常会用到的九个数学运算符。其中前四个（加减乘除）是ANSI SQL标准的一部分，所有数据库系统都实现了它们，而表中的其他运算符则是PostgreSQL特有的，不过很多别的数据库管理器同样也实现了执行这些运算的函数或者操作符。比如取模运算符（%）在Microsoft SQL Server、MySQL和PostgreSQL上都是可用的。如果你使用的是其他数据库系统，那么请参考其自身的文档。
表6-1：基本数学运算符
运算符 |
作用描述 |
|---|---|
|
加法 |
|
减法 |
|
乘法 |
|
除法（只返回商，不返回余数） |
|
取模（只返回余数） |
|
指数 |
|
平方根 |
|
立方根 |
|
阶乘 |
为了了解上表列出的各个运算符，我们将对普通的数字执行简单的SQL查询，而不是对表或者其他数据库对象进行操作。你可以在pgAdmin的查询工具中分别键入语句，然后逐一执行，又或者从本书的资源库中复制本章的代码，然后通过高亮每一行来分别执行它们：https://www.nostarch.com/practical-sql-2nd-edition/。
了解数学和数据类型¶
在实践这些例子的过程中，请注意每个计算结果的数据类型，它们会在pgAdmin结果网格中每一列的名称下面出现。计算返回的类型将根据操作符和输入数字的数据类型而变化。当我们在两个数字之间使用加减乘除运算符时，返回的数据类型将遵循以下模式：
两个
integer输入将返回一个integer结果。如果运算中的一方或者双方都是
numeric，那么返回一个numeric。任何涉及浮点数的运算都将返回一个
double precision类型的浮点数。
然而根和阶乘函数的情况则有所不同。这些函数都只接受一个数字作为输入，并将其用在操作符的前面或者后面，然后返回numeric或者浮点类型作为结果，即便输入是一个integer。
有些时候，运算符返回的计算结果正好就是你需要的类型；但是在别的情况下，比如你需要将结果传递给只接受某种特定类型的函数时，你可能就需要像第4章“使用CAST将值从一种类型转换为另一种类型”一节中提到的那样，通过CAST改变数据的类型。本书在遇到这种情况时将作出相应的提醒。
注意
PostgreSQL在一个名为
pg_operator的表中定义了操作符接受的参数、它们调用的内部函数以及它们返回的数据类型。比如+运算符就分别进行了多次定义，以便接受integer和numeric等多种数据类型。
加法、减法和乘法¶
让我们首先从简单的整数加法、减法和乘法开始。代码清单6-1展示了三个例子，其中每个SELECT关键字后面都跟着一个数学公式。从第3章开始，我们使用SELECT的主要目的通常是为了从表中检索数据。但是在PostgreSQL、Microsoft
SQL
Server、MySQL以及某些别的数据库管理系统中，你可以像这里一样省略表名，只执行一些简单的数学或者字符串操作符。为了方便阅读，最好在数学运算符的前面和后面都放置一个空格，尽管这些空格对于代码的运行并非必要，但这却是一种很好的实践。
➊ SELECT 2 + 2;
➋ SELECT 9 - 1;
➌ SELECT 3 * 4;
代码清单6-1：使用SQL实现基本的加法、减法和乘法
上面展示的这些语句都非常简单，所以你应该不会对SELECT 2 + 2;这个语句➊在查询工具中返回结果4感到惊讶。与此类似，减法➋和乘法➌的例子将分别返回预期中的结果：8和12。正如其他查询结果一样，数学运算符的结果也会以列的形式显示。但由于这些操作并未实际地查询某个表的某一列，所以结果列的名字将被标记为?column?，以此来表示一个未知的列。
?column?
--------
4
就这样。我们没有修改表中的任何数据，只是打印了一个计算结果。如果你想为列指定一个名字，那么可以通过提供别名来完成，就像这样：
SELECT 3 * 4 AS result; 。
执行除法和取模¶
由于整数数学和小数数学之间的差异，使用SQL执行除法会比之前介绍过的其他运算更复杂一些。再加上在除法运算中只返回余数的取模操作符，情况可能会变得更让人迷惑。为了厘清这些操作之间的细微差别，代码清单6-2展示了四个例子。
➊ SELECT 11 / 6;
➋ SELECT 11 % 6;
➌ SELECT 11.0 / 6;
➍ SELECT CAST(11 AS numeric(3,1)) / 6;
代码清单6-2：使用SQL实现整数和小数的除法
在语句➊中，/操作符将整数11除以另一个整数6。只要稍微思考一下，我们就会知道这个计算的答案是1，而余数则为5。但是由于SQL处理整数之间的除法时只会返回整数商而不返回任何余数，所以实际执行这个语句只会得到结果1。如果你想要获取整数的余数，就需要像语句➋那样，使用取模运算符%。这一语句只会返回余数，在这个例子中也即是5。直至今天，仍然没有任何操作可以同时你为提供整数的商和余数，但未来可能会出现有进取心的开发者为我们实现这个功能。
取模不仅仅可以用于获取余数：它还可以用作测试条件。比如说，你可以通过将一个数与2取模来测试它是否为偶数。如果结果为0，没有余数，那么它就是偶数。
有两种方法可以将两个数相除并让其返回numeric类型的结果。首先，如果其中一个或者两个数字都是numeric，那么结果默认也会被解释为numeric。这就是11.0除以6时出现的情况➌，执行这一查询将得到结果1.83333（根据你的PostgreSQL和系统设置，小数的位数可能会有所不同）。
其次，如果你正在处理的都是储存为整数的数据，并且你想要让它们强制执行小数除法，那么你可以像语句
➍
那样，使用CAST把其中一个整数转换为numeric类型。执行该语句也会得到结果1.83333。
计算指数、根和阶乘¶
除了基本的数学运算之外，PostgreSQL风味的SQL还提供了计算平方、次方、基于底数的指数、阶乘以及查找根的运算符和函数。代码清单6-3展示了这些运算符的使用示例。
➊ SELECT 3 ^ 4;
➋ SELECT |/ 10;
SELECT sqrt(10);
➌ SELECT ||/ 10;
➍ SELECT factorial(4);
SELECT 4 !;
代码清单6-3：使用SQL计算指数、根和阶乘
取幂运算符（^）可以将给定的底数提升为指数，在语句➊中，3 ^ 4（也即是我们常说的求3的4次方）的结果为81。
查找平方根的工作可以通过使用|/操作符或者sqrt(n)函数这两种方式完成➋，至于查找次方根则需要用到
||/
操作符➌。由于这类操作符需要放置在单个值的前面，所以我们把它们称为前缀操作符。
为了计算一个数的阶乘，我们需要用到factorial(n)函数或者!操作符。其中!只在PostgreSQL
13或以前的版本中可用，它是一个后置操作符，需要放置在单个值的后面。阶乘在数学上有很多用处，最常见的一种可能就是确定给定数量的物品有多少种排列方式。举个例子，如果你有四张照片，那么有多少种方式可以将它们排列在墙上？为了找出答案，你在计算阶乘的时候，需要将物品的数量乘以所有比它小的正整数。因此，对于语句➍，执行函数factorial(4)相当于计算4
x 3 x 2 x
1，而结果24则说明了4张照片可能存在的排列方式——怪不得我们要花那么多时间来装饰房间了！
再次申明，这些操作符是PostgreSQL特有的：它们并不属于SQL标准。如果你使用的是其他数据库应用程序，那么请查看其文档以了解它们如何实现这些操作。
注意操作的顺序¶
你可能还记得，在早期的数学课上，数学表达式的运算顺序或是运算符的优先级。那么SQL是根据什么来决定计算的先后顺序呢？一个并不让人吃惊的答案是，SQL遵循既定的数学标准。对于目前讨论过的PostgreSQL操作符，它们的顺序是：
指数和根
乘法、除法、取模
加法和减法
基于以上规则，如果你想要以不同的顺序进行计算，那么就需要用小括号把操作包裹起来。比如说，以下两个表达式将产生不同的结果：
SELECT 7 + 8 * 9;
SELECT (7 + 8) * 9;
因为乘法操作具有更高的优先级，它会在加法操作执行之前计算，所以第一个表达式将返回79；而第二个表达式则由于括号使得加法运算必须首先被执行，所以它将返回135：
以下是另一个使用指数的例子：
SELECT 3 ^ 3 - 1;
SELECT 3 ^ (3 - 1);
因为指数运算优先于减法运算，所以在没有括号的情况下，整个表达式将从左往右求值：首先计算3的3次幂，然后再减去1，得出结果26。然而在第二个例子中，括号迫使减法运算优先执行，所以这个表达式的结果为9，也即是3的2次幂。
一定要把操作符的优先级牢牢记住，这样你在以后的分析中才不会犯错！
对人口普查表中的列进行数学运算¶
接下来，我们将对第5章导入的2019年美国人口普查人口估算表us_counties_pop_est_2019进行挖掘，尝试在真实数据中使用最常用的SQL数学运算符。这些查询不需要我们提供具体数字，只需要指定包含数字的列即可。当查询执行时，计算将在表的每一行上进行。
为了唤醒我们对上述数据的记忆，请执行代码清单6-4的脚本。这段代码应该会返回3,142个行，其中展示了美国每个县的名称及其所属的州，还有各县在2019年出现人口变化的各个部分：出生、死亡、国际移民和国内移民等。
SELECT county_name AS➊ county,
state_name AS state,
pop_est_2019 AS pop,
births_2019 AS births,
deaths_2019 AS deaths,
international_migr_2019 AS int_migr,
domestic_migr_2019 AS dom_migr,
residual_2019 AS residual
FROM us_counties_pop_est_2019;
代码清单6-4：使用别名获取人口普查人口估算列
这个查询并没有返回表中的所有列，只是返回了跟人口估算有关的数据。因为这个查询中的所有数据都来自2019年，所以我使用AS关键字➊在结果集中为每个列都设置了一个较短的列名，去掉了它们的年份，以此来减少pgAdmin输出结果时可能需要的屏幕滚动。你也可以根据自己的需求对此进行相应的调整。
对列执行加法或减法¶
现在，让我们尝试用其中的两列进行简单的计算。代码清单6-5将每个县的出生人数减去死亡人数，以此来测算人口普查中的自然增长。让我们来看看这会得到一个怎样的结果。
SELECT county_name AS county,
state_name AS state,
births_2019 AS births,
deaths_2019 AS deaths,
➊ births_2019 - deaths_2019 AS natural_increase
FROM us_counties_pop_est_2019
ORDER BY state_name, county_name;
代码清单6-5：对us_counties_pop_est_2019中的两个列执行减法
这个SELECT语句将把births_2019 - deaths_2019➊看作一列来进行计算。跟前面一样，这段代码也使用了AS关键字为该列提供更易读的别名。如果我们不这样做的话，那么PostgreSQL就会使用无意义的?column?作为该列的名字。
运行查询以获取结果，前面的几行应该会产生以下输出：
county state births deaths natural_increase
-------------- ------- ------ ------ ----------------
Autauga County Alabama 624 541 83
Baldwin County Alabama 2304 2326 -22
Barbour County Alabama 256 312 -56
Bibb County Alabama 240 252 -12
你可以用计算器或者纸笔快速检查一下，确认自然增长一栏等于两栏相减之差，这是非常值得一做的！当你滚动浏览输出结果的时候，你可能会注意到有些县的出生人数多于死亡人数，而有些县则相反。通常情况下，居民组成较为年轻的县，出生人数会超过死亡人数；而那些人口较多的县，比如农村地区和退休热点地区，死亡人数往往会多于出生人数。
现在，让我们在上述基础上进行数据测试，并验证被导入的列是否正确。正如代码清单6-6所示，2019年的人口估算值应该等于2018年的估算值加上出生、死亡、移民和剩余因子这四个列的总和。
SELECT county_name AS county,
state_name AS state,
➊ pop_est_2019 AS pop,
➋ pop_est_2018 + births_2019 - deaths_2019 +
international_migr_2019 + domestic_migr_2019 +
residual_2019 AS components_total,
➌ pop_est_2019 - (pop_est_2018 + births_2019 - deaths_2019 +
international_migr_2019 + domestic_migr_2019 +
residual_2019) AS difference
FROM us_counties_pop_est_2019
➍ ORDER BY difference DESC;
代码清单 6-6 ：检查人口普查数据的总数
这个查询包含了2019年的人口估算值➊，后面还跟着由各个部分以及2018年人口估算值相加而成的component_total➋。如果一切正常，那么2018年的估算值加上各个部分的总和应该就等于2019年的估算值。为了验证这一点，我们还增加了一个名为difference的列，该列使用2019年的估算值减去各个部分的总和➌。如果所有数据都准确无误，那么所有行difference列的值应该都为零。最后，为了避免逐一检查全部3,142个行，我们为difference列添加了一个ORDER BY语句➍：如果该列中出现了任何不为零的值，那么它将被放置到查询结果的顶部。
运行这个查询，它的前面几行应该会提供这样的结果：
county state pop components_total difference
-------------- ------- ------ ---------------- ----------
Autauga County Alabama 55869 55869 0
Baldwin County Alabama 223234 223234 0
Barbour County Alabama 24686 24686 0
通过确保difference列的值为零，我们可以肯定被导入的数据是干净的。每当我遇到或者导入一个新的数据集时，我总是喜欢执行类似的小测试。它们能帮助我更好地理解数据，并在我进行分析之前排除任何可能出现的问题。
计算全局占比¶
发现数据集中项目（item）差异的其中一个方法，就是计算特定数据点在全局所占的百分比。通过比较数据集中所有项目的百分比，我们可以从中得到有意义的见解甚至是惊喜。
计算全局占比需要将相关数字除以总数。举个例子，如果你有一篮子共12各苹果，并使用了其中9个做苹果派，那么它的占比就为9/12或者0.75——通常也表示为75%。
通过使用人口普查数据中代表县地理要素体积的两个列，我们可以尝试执行类似的计算。通过使用代码清单6-7中的代码，我们可以计算出水体在每个县的面积中所占的比例，其中需要用到area_land和area_water这两个列：它们以平方米为单位，分别记录了县的陆地面积和水体面积。
SELECT county_name AS county,
state_name AS state,
➊ area_water::numeric / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;
代码清单6-7：计算水体在县面积中的百分比
这个查询的关键在于将area_water除以area_land和area_water的总和，后者代表该县的总面积➊。
考虑到原始的面积数据都是整数类型，如果直接对它们进行计算，那么将无法得到所需的分数结果：每一行都只会显示除法计算所得的商，也即是0。为了解决这个问题，我们需要将其中一个整数转换为小数类型以强制执行小数除法，并且为了简洁起见，这次转换是在首次引用area_water之后，对它使用PostgreSQL特有的双冒号标记法来实现的，但你也可以使用第4章介绍的ANSI
SQL标准的CAST函数来达到同样的效果。最后，代码将结果乘以100从而实现以百分数的形式展示结果，这也是绝大部分人理解百分比的方式。
通过将百分比从高到低排序，输出结果的最前面一部分将是这个样子的：
county state pct_water
------------------ ------------- -----------------------
Keweenaw County Michigan 90.94723747453215452900
Leelanau County Michigan 86.28858968116583102500
Nantucket County Massachusetts 84.79692499185512352300
St. Bernard Parish Louisiana 82.48371149202893908400
Alger County Michigan 81.87221940647501072300
如果你在维基百科上查看关于基威诺（Keweenaw）县的条目，那么就会发现为什么它的总面积超过90%都是水：它的土地面积包含了苏必利尔湖的其中一个岛，并且湖水的面积也包含在了人口普查报告的总面积中。别忘了这个小知识！
跟踪百分比变化¶
数据分析中的另一个关键指标就是百分比变化：一个数字比另一个数字大多少或者小多少？百分比变化计算通常用于分析随时间产生的变化，并且它们也特别适用于比较相似项目之间的变化。
以下是其中一些例子：
各家汽车制造商每年同比汽车销售数量的变化
营销公司属下的每份电子邮件列表的月度订阅量变化
全国学校年度招生人数的增减变化
计算百分比变化的公式可以这样表示：
因此，如果你拥有一个柠檬水摊位，并且今天卖出了73杯柠檬水，昨天卖出了59杯，那么这两天的百分比变化可以通过以下计算得出：
为了实际演示这样的操作，我们将使用虚构的地方政府部门支出作为测试数据集。代码清单6-8展示了如何找出具有最大增幅和降幅的部门。
➊ CREATE TABLE percent_change (
department text,
spend_2019 numeric(10,2),
spend_2022 numeric(10,2)
);
➋ INSERT INTO percent_change
VALUES
('Assessor', 178556, 179500),
('Building', 250000, 289000),
('Clerk', 451980, 650000),
('Library', 87777, 90001),
('Parks', 250000, 223000),
('Water', 199000, 195000);
SELECT department,
spend_2019,
spend_2022,
➌ round( (spend_2022 - spend_2019) /
spend_2019 * 100, 1) AS pct_change
FROM percent_change;
代码清单6-8：计算百分比变化
这段代码创建了一个名为percent_change的表➊，并向其插入六个行➋，其中包含2019年和2022年的部门支出数据。查询首先使用spend_2022减去spend_2019，然后再将其结果除以spend_2019从而计算出百分比变化➌，最后再将其乘以100从而以百分数形式展示结果。
为了简化输出，查询还使用了round()函数以移除小数点后一位以外的其他小数。这个函数接受两个参数：需要被舍入的列或者表达式，还有想要保留的小数位数。因为计算涉及的两个数字都是numeric类型，所以它们的结果也会是numeric类型。
执行上述代码将产生以下结果：
department spend_2019 spend_2022 pct_change
---------- ---------- ---------- ----------
Assessor 178556.00 179500.00 0.5
Building 250000.00 289000.00 15.6
Clerk 451980.00 650000.00 43.8
Library 87777.00 90001.00 2.5
Parks 250000.00 223000.00 -10.8
Water 199000.00 195000.00 -2.0
从结果可以看出，书记部门新增的支出要远远超过县里的其他部门。
使用聚合函数计算平均数以及总和¶
到目前为止，我们执行的数学运算符针对的都是表中每一行的某个列或者某些列，但实际上SQL也允许你使用聚合函数对同一列中的多个值进行计算。以下文档列出了PostgreSQL支持的所有聚合函数，它们能够通过多个输入计算出单个结果：https://www.postgresql.org/docs/current/functions-aggregate.html，而数据分析中最常用的两个聚合函数就是avg()和sum()。
正如代码清单6-9所示，为了通过人口普查表us_counties_pop_est_2019计算出所有县的总人口以及平均人口，我们需要对记录2019年人口估算值的pop_est_2019列执行avg()函数和sum()函数，并使用之前提到过的round()函数来移除平均计算结果中的小数点。
SELECT sum(pop_est_2019) AS county_sum,
round(avg(pop_est_2019), 0) AS county_average
FROM us_counties_pop_est_2019;
代码清单6-9：使用聚合函数sum()和avg()
上述计算将产生如下结果：
county_sum county_average
---------- --------------
328239523 104468
根据计算结果可知，2019年美国所有县的估算人口总数为3.282亿，其中各县的平均估算人口为104,468人。
查找中位数¶
对于一组数字来说，中位数是比平均数更为重要的指标。以下是中位数和平均数的区别：
**平均数** 所有值的总和除以值的数量 中位数 所有值在排序之后处于“中间”的那个值
因为中位数可以减少离群值的影响，所以它在数据分析中尤为重要。考虑以下这个例子：有六个孩子参加野外郊游，他们的年龄分别为10岁、11岁、10岁、9岁、13岁和12岁。只要将这群人的年龄相加然后除以数量六，就能够得到他们的平均年龄：
因为上述年龄都处于一个狭小的范围内，所以10.8这个平均数能够很好地代表这个群体。但如果数值是聚拢的，或者偏向分布的某一端，又或者其中包含了离群值，那么平均数的作用就会大大减少。
举个例子，如果一位年长的46岁监护人也加入到前述的野外郊游中，那么这个小组的平均年龄将大大增加：
因为离群值导致的偏向，现在平均数已经无法很好地代表这个群体，它已经是一个不可靠的指标了。
在这种情况下，更好的选择是找到中位数，也即是多个值在有序排列之后位于中间位置的值——这个值比一半的值要大，又比另一半的值要小。同样以野外郊游为例，我们可以将参与者的年龄从低到高进行排序：
位于这组年龄中间的值（中位数）为11。对于这个群体，中位数11比平均数15.9更能说明参与者的年龄特征。
如果值的数量为偶数，那么可以取两个中间位置数字的平均值作为中位数。让我们把另一个12岁的学生添加到野外郊游的队伍中：
现在位于中间的两个值是11和12，它们两的平均值11.5就是我们要寻找的中位数。
对中位数的报道常常见诸于金融新闻。关于住房价格的报道通常就会使用中位数，因为几笔伪豪宅 [1]_的销售很可能就会让一个不富裕地区的平均房价失去意义。这个道理对于体育运动员的薪资也是说得通的：一两个超级明星可能就会令整个球队的平均薪资产生偏差。
一个有益的测试是同时计算一组值的平均数和中位数。如果这两个数结果相近，那么这组值可能就是正态分布的（也即是我们熟悉的钟形曲线），这时平均数就是有用的。反之，如果平均数和中位数相差甚远，那么这组值就不是正态分布的，这时中位数就是更好的代表。
通过百分位函数查找中位数¶
跟Excel以及其他电子表格程序不一样，PostgreSQL和其他大多数关系数据库都没有提供内置的median()函数，并且这个函数也并没有包含在ANSI
SQL标准之内。作为替代，我们会使用SQL的百分位函数来计算中位数，并通过分位数和割点来将一组数字分成相等的大小。百分位函数是ANSI
SQL标准的一部分。
在统计学中，百分位数表示在一组有序的数据中，数据低于某个点的百分比。举个例子，如果医生跟你说，你的身高在这个年龄段的成年人中处于第60个百分点，那么这意味着你的身高比60%的人都要高。
中位数相当于第50个百分位数，它比一半的值要高，又比另一半的值要低。有两个版本的百分位函数，它们分别为percentile_cont(n)和percentile_disc(n)。这两个函数都是ANSI
SQL标准的一部分，并且都存在于PostgreSQL、Microsoft SQL
Server以及其他数据库中。
percentile_cont(n)函数以连续值的方式计算百分位数，这就意味着它的结果不一定是数据集中的某个数字，而可能是两个数字之间的一个小数值。这遵循了对偶数数量的值计算中位数的方法，也即是中位数是中间两个数字的平均数。另一方面，只返回离散值的percentile_disc(n)函数在同样情况下会将结果四舍五入为集合中的某个数字。
在代码清单6-10中，我们制作了一个有六个数字的测试表，并找出其中的百分位数。
CREATE TABLE percentile_test (
numbers integer
);
INSERT INTO percentile_test (numbers) VALUES
(1), (2), (3), (4), (5), (6);
SELECT
➊ percentile_cont(.5)
WITHIN GROUP (ORDER BY numbers),
➋ percentile_disc(.5)
WITHIN GROUP (ORDER BY numbers)
FROM percentile_test;
代码清单6-10：测试SQL的百分位函数
在连续和离散的百分位函数中，我们输入.5以代表第50个百分位数，相当于中位数。代码的运行结果如下：
percentile_cont percentile_disc
--------------- ---------------
3.5 3
percentile_cont()函数返回了我们预期中的中位数：3.5。但由于percentile_disc()计算的是离散值，它返回的却是3，也即是前50%数字中的最后一个值。由于在偶数数量的集合中取两数的平均数作为中位数已经是公认的做法，所以我们将使用percentile_cont(.5)来查找中位数。
查找人口普查数据的中位数以及百分位数¶
人口普查数据可以再一次说明中位数和平均数的不同。代码清单6-11在已有的sum()和avg()这两个聚合函数的基础上加上了percentile_cont()函数，以此来计算各县人口的总数、平均数和中位数。
SELECT sum(pop_est_2019) AS county_sum,
round(avg(pop_est_2019), 0) AS county_average,
percentile_cont(.5)
WITHIN GROUP (ORDER BY pop_est_2019) AS county_median
FROM us_counties_pop_est_2019;
代码清单6-11：使用聚合函数sum()
、avg()和percentile_cont()
以上查询将得到以下结果：
county_sum county_avg county_median
---------- ---------- -------------
328239523 104468 25726
从结果可见，中位数和平均数相差甚远，在这种情况下平均数很可能会造成误导。正如2019年估算值的中位数显示，美国有一半县的人口少于25,726人，而另一半则多于这个数。如果你在做一个关于美国人口统计的演讲，并告诉听众“美国平均每个县的人口为104,468人”，那么你将给他们留下一个歪曲的印象。根据估算，2019年共有40多个县的人口达到或者超过100万，而洛杉矶县的人口甚至超过了1000万，这导致平均数被推高了。
使用百分位函数查找其他分位数¶
我们有时候需要把数据切分为更小的相等分组（equal
group）以便进行分析。其中最常见的是四分位数（四组相等）、五分位数（五组相等）和十分位数（十组相等）。无论我们想要查找的是什么分位数，只需要将它填入百分位函数中即可。举个例子，如果你想要查找第一个四分位数或是位于底部25%的数据，那么你需要将参数设置为.25：
percentile_cont(.25)
但是，如果你想要生成多个割点，那么一次输入一个值将是相当费时费力的。与此相反，你可以使用数组，也即是一个由多个项（item）组成的列表，一次将多个值传递给percentile_count()函数。
代码清单6-12展示了如何一次计算所有四个四分位数。
SELECT percentile_cont(➊ARRAY[.25,.5,.75])
WITHIN GROUP (ORDER BY pop_est_2019) AS quartiles
FROM us_counties_pop_est_2019;
代码清单6-12：向percentile_cont()传递一个由值组成的数组
在这个例子中，我们通过在数组构造器ARRAY[]中包围值来创建割点。数组构造器➊是一个表达式，它们会用包含在方括号内的元素来构建数组。方括号内部的值由逗号分隔，它们是用于创建四个四分位数的三个割点。运行这个查询，你应该会得到以下输出：
quartiles
------------------------
{10902.5,25726,68072.75}
因为查询传入的是数组，所以PostgreSQL也返回了一个数组，并通过在结果中使用大括号来表示这一点。每个四分位数都使用逗号隔开。第一个四分位数为10,902.5，这意味着有25%的县人口等于或者低于这个数。第二个四分位数与中位数相等：25,726。第三个四分位数为68,072.75，这意味着人口最多的那25%的县，其人口将不会低于这个数。（因为讨论人口的时候通常不使用分数，所以在报告这些数字的时候可能需要对它们进行四舍五入。）
ANSI SQL标准定义了数组，并且除了上例中展示的数组用法之外，PostgreSQL还提供了其他几种使用数组的方式。比方说，你可以把表的列定义为特定数据类型的数组。当你在处理类似博客文章标签集合这样的数据，但是又想把它们储存在同一列而不是独立的表里面时，这种功能就会非常有用。关于声明、搜索和修改数组的更多例子，可以参考文档：https://www.postgresql.org/docs/current/arrays.html。
数组还带有大量函数，它们允许你执行诸如添加值、移除值又或者统计元素数量等任务，详细信息请见文档：https://www.postgresql.org/docs/current/functions-array.html。对于代码清单6-12返回的结果，有一个非常方便的函数unnest()，它通过将数组转变为行来让数组变得更易读。代码清单6-13展示了这个函数的具体用法。
SELECT unnest(
percentile_cont(ARRAY[.25,.5,.75])
WITHIN GROUP (ORDER BY pop_est_2019)
) AS quartiles
FROM us_counties_pop_est_2019;
代码清单6-13：使用unnest()将数组转变为行
现在输出将以多行的形式展示：
quartiles
---------
10902.5
25726
68072.75
当我们在计算十分位数时，这种拉取结果数组中的每个数字并逐行展示的做法将会相当有用。
发现模式¶
通过使用PostgreSQL的mode()函数，我们可以找出最常出现的值，以便发现数据中的模式。这个函数并非标准SQL的一部分，它的语法跟百分位函数类似。代码清单6-14展示了对births_2019列执行mode()计算的结果，该列记录了各县的出生婴儿数量。
SELECT mode() WITHIN GROUP (ORDER BY births_2019)
FROM us_counties_pop_est_2019;
代码清单6-15：使用mode()查找最常出现的值
查询结果为86，有16个县的出生人数都是这个数字。
小结¶
跟数字打交道是从数据中获取意义的重要一环，有了本章介绍的数学技能，你就可以用SQL完成基本的数值分析了。到目前为止，我们已经掌握了关于求和、平均数和百分位数的基本知识，在稍后的章节中，我们还会学习更深层次的统计学概念，包括回归和相关。此外，我们还知道了如何通过中位数而不是平均数来更公平地评估一组数值，这一点能够帮助我们避免不准确的结论。
在接下来的一章，我们将要开始探索如何连接两个或多个表，以及如何通过这种强大的力量来增加我们在数据分析中的可选项。其中将会用到已经加载至analysis数据库中的2019年美国人口普查数据，此外还会用到额外的数据集。
实战演练
编写一条SQL语句来计算半径为5英寸的圆形面积。（如果你不记得公式的话那么可以上网搜索一下。）你的语句中是否需要用到括号？原因是什么？
使用2019年美国人口普查的县级估算数据，计算出纽约州每个县的出生率和死亡率。2019年该州哪个地区的出生人数和死亡人数的比率普遍较高？
加利福尼亚州和纽约州两者谁的2019年县人口估算值中位数更高？