SQL中的聚合函数

本文概述

• 设置数据库
• 简单的信息很重要
• 汇总功能
• 聚合函数+ GROUP BY +更多
• 向前推进

如果要使用数据, 则从给定的数据集构建报告是一项基本技能。因为最终, 你希望能够使用可支配的数据来回答关键业务问题。很多时候, 这些答案都以报告图表的形式呈现。但有时, 还需要表格形式的报告。在这两种情况下, 你可能都需要使用简单的计算来汇总数据。在SQL中, 你可以使用汇总函数汇总/汇总数据。使用这些功能, 你将能够回答以下问题：

• some_column_from_the_table的最大值是多少？要么
• some_column_from_the_table相对于another_column_from_the_table的最小值是多少？

还有很多。

让我们开始执行一些数据聚合。

注意：为了能够跟随本教程的进行, 你需要知道如何在PostgreSQL中编写基本查询(你将用它作为RDBMS)。本教程可以作为很好的复习。

设置数据库

首先, 我们设置一个PostgreSQL数据库并还原此备份, 其中包含你将在本教程中使用的表。如果要学习如何在PostgreSQL中还原数据库备份, 可以按照本教程的第一部分进行。

如果能够还原备份, 则应该在数据库中看到一个名为international_debt的表(尽管如果没有该表, 则需要首先创建一个数据库)。让我们快速看一下表的前几行(一个简单的select查询将帮助你完成此操作)：

下表包含有关本年度全球不同国家不同类别的债务统计信息(请参阅indicator_name和indicator_code列)。债务列显示特定国家/地区在特定类别中的债务金额(美元)。数据属于经济学领域, 通常用于分析不同国家的经济状况。数据是从世界银行收集的。

现在, 你已经成功建立了数据库, 让我们运行一些简单的查询以了解有关数据的更多信息。打开pgAdmin工具并开始使用。

简单的信息很重要

从上图可以看到, 一个国家有很多重复条目, 但类别却不同。从这里很快提出的一个问题是：

该表包含的记录有哪些不同的国家？

如果仅使用country_name列运行选择查询, 则不会获得正确的答案, 因为查询结果将包含重复项。让我们使用DISTINCT关键字来解决这个问题。

select distinct country_name from international_debt;

这应该返回如下内容：

现在, 你对上述问题有了一个正确的答案。现在, 在继续汇总功能之前的最后一个问题：

表格中有多少种不同的债务指标？

回答查询应与以上类似。你只需要更改列名。当作练习吧？结果应类似于以下内容：

汇总功能

让我们从运行带有聚合函数的查询开始, 然后进行相应的处理。在此过程中, 你将了解有关在SQL中应用聚合函数时需要遵循的语法和构造类型的更多信息。

select sum(debt) from international_debt;

结果：

使用SUM()聚合函数, 你可以计算一列(包含数值)的算术和。通过上面的查询, 你可以了解表中所列国家的未偿债务总额。

请注意, 在计算总和时, SUM()不会考虑NULL值。现在, 让我们找到问题的答案：

最大债务额是多少？

MAX()聚合函数可为你提供帮助：

select max(debt) from international_debt;

答案是：

像SUM()函数一样, MAX()在进行计算时不会考虑NULL条目。也有类似的MIN()函数。通过”评论”部分让我知道债务列的最小值吗？现在, 最好找出债务列中是否存在无效条目, 以确保到目前为止的结果都是正确的。

请注意, 如上所述, 你以小写形式使用这些功能。

当执行此查询时：从国际债务中选择*, 而债务为null；你应该得到一个空结果。现在让我们找出表格中存在的不同国家/地区的总数。

select count(distinct(country_name)) from international_debt;

你会看到表格中共有124个不同的国家/地区。请密切注意你在上述查询中应用的一系列功能。是的, 这里允许这样做, 即以逻辑方式连接多个聚合功能。

现在, 假设你想查看债务列的平均值。该函数是AVG()：

从international_debt中选择avg(debt);

你看到的值为1306633214.966397971(USD)。最好用合适的列名显示这些结果。从上面的结果中, 你可以看到PostgreSQL在返回结果时将列名更改为聚合函数名(包含在查询中)。因此, 为这些列提供适当的别名是个好主意。你可以这样操作：

select avg(debt) as Average_Debt_By_A_Country from international_debt;

结果更容易解释：

现在让我们将其扩展到更复杂的水平。为了能够回答诸如some_column_from_the_table相对于another_column_from_the_table的最小值是多少？之类的问题, 你需要将一个聚合函数与GROUP BY子句配对。让我们看看如何。

聚合函数+ GROUP BY +更多

假设你要生成一个报告, 其中将显示country_name及其债务的总和。以下是一个示例：

这样的报告在现实世界中经常使用。那么, 获取这样的报告的查询可能是什么？你将不得不对债务使用SUM()函数。而且, 你还需要显示country_name以及债务总额。让我们执行以下查询：

select country_name, sum(debt) from international_debt;

它不会产生以下错误吗？

错误：” international_debt.country_name”列必须出现在GROUP BY子句中或在聚合函数中使用

现在让我们了解它的实际含义。当你使用聚合函数(例如SUM()带有未聚合列, 例如country_name)时, 你需要将未聚合列传递给GROUP BY子句。因此, 正确的查询将是：

select country_name, sum(debt) as total_debt from international_debt group by country_name;

结果是正确的：

请注意在查询中使用别名。

现在, 假设你需要按照关于total_debt的降序对报告进行排序。还记得ORDER BY子句吗？是的, 你也可以将聚合函数与ORDER BY子句配对：

select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;

现在应该对结果进行反向排序：

请注意你在ORDER BY子句中使用的列。

现在另一个重要的问题：

不同类别(以反向排序的方式)的最高债务额是多少？

你将需要在此处使用MAX()函数。现在, 回答这个问题的查询应该不难。

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;

你会得到一份干净的报告：

你也可以像这样限制报告中的行数。假设你只想在上述报告中包括前五项。你可以使用LIMIT子句来完成。

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;

该教程的最终报告时间到了。你需要在以上报告中包括国家/地区的名称。你怎么能这样做？以下查询应允许你执行此操作：

select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;

另一个好的报告：

在上面的查询中, 你在SELECT子句之后添加了country_name列, 并且也在GROUP BY之后添加了它。你可以将此格式扩展为任意数量。

在生成这样的报告时, GROUP BY, ORDER BY和LIMIT的顺序非常重要。如果你错误地更改了订单, 则将不得不面对错误。自己看看这个：

select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;

你会得到：

ERROR:  syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...

在上面的查询中, 你将ORDER BY子句放置在GROUP BY之前, 这是不允许的。实际上, 当你不使用聚合函数时, 它也不适用。正确的顺序是-GROUP BY-> ORDER BY-> LIMIT。永远记住这一点。

向前推进

恭喜！你已完成本教程的结尾。在本教程中, 你将了解PostgreSQL中的各种聚合函数以及如何使用它们生成有用的报告。这些对于数据科学家而言确实是至关重要的技能。为了系统地提高你的SQL技能, 你可以参加以下srcmini课程：

• 在SQL中联接数据
• 中级SQL
• 用于探索性数据分析的SQL