三十、统计设施数量表机构详见Postgresql基础实践教程一题目这是我们第一次接触聚合函数先从简单的开始。我们想知道一共有多少个设施——只需要算出总数就行。预期结果count9【答案与解析】selectcount(*)fromcd.facilities;聚合函数的用法其实挺直观的上面的 SQL 语句从 facilities 表中查询所有数据然后统计结果集中的行数。count 函数有几种常见的用法COUNT(*) 直接返回行数COUNT(address) 统计结果集中 address 字段非空的记录数COUNT(DISTINCT address) 统计 facilities 表中有多少个不同的地址聚合函数的基本原理是它接收一列数据对这些数据进行某种计算最后输出一个标量值也就是单个值。除了 COUNT还有很多其他聚合函数比如 MAX、MIN、SUM、AVG 等。看名字大概就能猜到它们是干啥的 很多人对聚合函数感到困惑的地方在于类似下面的查询selectfacid,count(*)fromcd.facilities你试试运行这条语句会发现它报错了。这是因为 count(*) 想把整个 facilities 表聚合成一个值但问题是表里有很多不同的 facidPostgres 不知道该把 count 的结果跟哪个 facid 配对。那如果你想查出每个 facid 以及对应的计数该怎么办呢可以把聚合部分写成子查询像这样selectfacid,(selectcount(*)fromcd.facilities)fromcd.facilities当子查询返回的是一个标量值时Postgres 会自动把这个值重复填充到 cd.facilities 表的每一行中。三十一、统计高价设施数量题目统计对客人收费在 10 及以上的设施数量。预期结果count6【答案与解析】selectcount(*)fromcd.facilitieswhereguestcost10;这道题只是在前一题的基础上做了点小改动我们需要把便宜的设施过滤掉。用 WHERE 子句就能轻松搞定。这样一来聚合函数就只会看到那些收费较高的设施了。三十二、统计每个会员的推荐人数题目统计每个会员推荐了多少人。按会员 ID 排序。预期结果recommendedbycount15233142516192111132151161201301【答案与解析】selectrecommendedby,count(*)fromcd.memberswhererecommendedbyisnotnullgroupbyrecommendedbyorderbyrecommendedby;之前我们见过聚合函数是作用在一列数据上然后把它们聚合成一个标量值。这很有用但我们经常会发现自己想要的不是单个聚合结果比如说我可能不想知道俱乐部这个月总共赚了多少钱而是想知道每个设施分别赚了多少钱或者哪些时间段最赚钱。为了支持这种需求SQL 提供了 GROUP BY 子句。它的作用是把数据分成若干组然后对每组分别运行聚合函数。当你指定了 GROUP BY数据库会为指定列中的每个不同值生成一个聚合结果。在这个例子里我们的意思是对于 recommendedby 的每个不同值统计它出现了多少次。三十三、列出每个设施的总预订时段数题目列出每个设施的总预订时段数。目前只需要生成一个包含设施 ID 和时段数的输出表按设施 ID 排序。预期结果facidTotal Slots01320112782120938304140452286110479088911【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidorderbyfacid;除了引入了 SUM 聚合函数之外这道题没什么特别需要说明的。对于每个不同的设施 IDSUM 函数会把 slots 列中的所有值加起来。三十四、列出指定月份每个设施的总预订时段数题目列出 2012 年 9 月每个设施的总预订时段数。生成一个包含设施 ID 和时段数的输出表按时段数排序。预期结果facidTotal Slots512234227426847165402570158805914648【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingswherestarttime2012-09-01andstarttime2012-10-01groupbyfacidorderbysum(slots);这道题只是在前一题的基础上做了点小改动。记住聚合是在 WHERE 子句执行之后才进行的所以我们用 WHERE 来限制要聚合的数据范围这样聚合函数就只会看到一个月的数据了。三十五、列出每个月每个设施的总预订时段数题目列出 2012 年每个月每个设施的总预订时段数。生成一个包含设施 ID、月份和时段数的输出表按设施 ID 和月份排序。预期结果facidmonthTotal Slots0727008459095911720718483195882718028459295703710438304394224726448492496485724588259122671646840069540771567832679426871178832289471【答案与解析】selectfacid,extract(monthfromstarttime)asmonth,sum(slots)asTotal Slotsfromcd.bookingswhereextract(yearfromstarttime)2012groupbyfacid,monthorderbyfacid,month;这道题新引入的主要功能是 EXTRACT 函数。EXTRACT 可以从时间戳中提取出各个组成部分比如日、月、年等。我们按照这个函数的输出进行分组就能得到每月的统计值。如果需要区分不同年份的相同月份可以使用 DATE_TRUNC 函数它能把日期截断到指定的粒度。另外值得一提的是这是我们第一次真正用到按多列分组的功能。关于这个答案有一点需要注意在 WHERE 子句中使用 EXTRACT 函数可能会在大表上导致严重的性能问题。如果 timestamp 列上有普通索引Postgres 无法利用索引来加速查询只能全表扫描。你有以下几种解决方案考虑在 timestamp 列上创建表达式索引。有了合适的索引Postgres 就能用索引来加速包含函数调用的 WHERE 子句。修改查询虽然写得稍微啰嗦一点但使用更标准的比较方式例如selectfacid,extract(monthfromstarttime)asmonth,sum(slots)asTotal Slotsfromcd.bookingswherestarttime2012-01-01andstarttime2013-01-01groupbyfacid,monthorderbyfacid,month;对于这种标准比较Postgres 无需额外帮助就能使用索引。三十六、统计至少预订过一次的会员数量题目统计至少预订过一次的会员包括访客总数。预期结果count30【答案与解析】selectcount(distinctmemid)fromcd.bookings你第一反应可能是用子查询像这样selectcount(*)from(selectdistinctmemidfromcd.bookings)asmems这样确实能正常工作但我们可以借助 COUNT DISTINCT 稍微简化一下。它的作用正如你所料统计指定列中不同值的数量。三十七、列出预订时段超过 1000 的设施题目列出预订时段总数超过 1000 的设施。生成一个包含设施 ID 和时段数的输出表按设施 ID 排序。预期结果facidTotal Slots0132011278212094140461104【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidhavingsum(slots)1000orderbyfacid原来 SQL 中有一个专门用来过滤聚合函数输出的关键字这就是 HAVING。HAVING 的行为很容易和 WHERE 混淆。最好的理解方式是在包含聚合函数的查询中WHERE 用来过滤进入聚合函数的数据而 HAVING 用来过滤聚合函数输出的数据。你可以多试试体会一下它们的区别三十八、计算每个设施的总收入题目列出每个设施及其总收入。输出表应包含设施名称和收入按收入排序。记住访客和会员的收费是不一样的预期结果namerevenueTable Tennis180Snooker Table240Pool Table270Badminton Court1906.5Squash Court13468.0Tennis Court 113860Tennis Court 214310Massage Room 215810Massage Room 172540【答案与解析】selectfacs.name,sum(slots*casewhenmemid0thenfacs.guestcostelsefacs.membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.nameorderbyrevenue;这道题唯一的复杂之处在于访客会员 ID 为 0的收费和其他人不一样。我们用 case 语句来计算每次预订的费用然后按设施分组把每次预订的费用加起来。三十九、找出总收入低于 1000 的设施题目列出总收入低于 1000 的设施。输出表应包含设施名称和收入按收入排序。记住访客和会员的收费是不一样的预期结果namerevenueTable Tennis180Snooker Table240Pool Table270【答案与解析】selectname,revenuefrom(selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.name)asaggwhererevenue1000orderbyrevenue;你可能尝试过用我们在前面练习中介绍的 HAVING 关键字写出类似下面的查询selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.namehavingrevenue1000orderbyrevenue;可惜这样行不通你会收到类似这样的错误ERROR: column “revenue” does not exist。和 SQL Server、MySQL 等其他关系型数据库不同Postgres 不支持在 HAVING 子句中使用列别名。所以要让这个查询正常工作你得写成下面这样selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.namehavingsum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)1000orderbyrevenue;像这样重复大量的计算代码显得很 messy所以我们推荐的解决方案是把主查询体包装成子查询然后用 WHERE 子句从中筛选。一般来说对于简单的查询我建议用 HAVING因为它更清晰。而对于复杂的情况这种子查询的方式往往更好用。四十、输出预订时段数最多的设施 ID题目输出预订时段数最多的设施 ID。如果要挑战一下试着写一个不用 LIMIT 子句的版本。这个版本可能会看起来很复杂预期结果facidTotal Slots41404【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidorderbysum(slots)descLIMIT1;我们先从最简单的方法开始生成一个包含设施 ID 和总预订时段数的列表按总时段数降序排序然后只取第一条结果。不过需要注意的是这种方法有个明显的缺陷如果出现平局多个设施的时段数相同我们仍然只能得到一个结果要想得到所有符合条件的结果我们可以尝试用 MAX 聚合函数像这样selectfacid,max(totalslots)from(selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacid)assubgroupbyfacid这个查询的意图是获取最高的 totalslots 值及其对应的 facid。可惜这样行不通如果有多个 facid 的时段数相同Postgres 就无法确定应该把哪个 facid 和 MAX 函数输出的单个标量值配对。所以 Postgres 会提示你 facid 应该放在 GROUP BY 子句中但这不会产生我们想要的结果。让我们先尝试写一个能正常工作的查询selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacidhavingsum(slots)(selectmax(sum2.totalslots)from(selectsum(slots)astotalslotsfromcd.bookingsgroupbyfacid)assum2);这个查询先生成设施 ID 和对应的时段数列表然后用 HAVING 子句计算出最大的 totalslots 值。我们的逻辑基本上是“生成 facid 及其预订时段数的列表然后过滤掉那些时段数不等于最大值的结果。”虽然 HAVING 很有用但我们的查询写得有点丑。为了改进它我们再引入一个新概念公用表表达式CTE。CTE 可以让你在查询中内联定义一个数据库视图。在像这种需要重复代码的场景中它特别有用。CTE 的声明形式是 WITH CTE名称 as (SQL表达式)。你可以看到我们用 CTE 重写了上面的查询withsumas(selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacid)selectfacid,totalslotsfromsumwheretotalslots(selectmax(totalslots)fromsum);可以看到我们把重复的 cd.bookings 查询提取到了一个 CTE 中让整个查询变得更易读了等等还没完。其实还可以用窗口函数来解决这个问题。我们后面再讲窗口函数但对于这类问题确实有更好的解决方案。一道练习题讲了这么多内容。如果现在不能完全理解也不用太担心——我们会在后面的练习中继续用到这些概念。
Postgresql基础实践教程（四）
三十、统计设施数量表机构详见Postgresql基础实践教程一题目这是我们第一次接触聚合函数先从简单的开始。我们想知道一共有多少个设施——只需要算出总数就行。预期结果count9【答案与解析】selectcount(*)fromcd.facilities;聚合函数的用法其实挺直观的上面的 SQL 语句从 facilities 表中查询所有数据然后统计结果集中的行数。count 函数有几种常见的用法COUNT(*) 直接返回行数COUNT(address) 统计结果集中 address 字段非空的记录数COUNT(DISTINCT address) 统计 facilities 表中有多少个不同的地址聚合函数的基本原理是它接收一列数据对这些数据进行某种计算最后输出一个标量值也就是单个值。除了 COUNT还有很多其他聚合函数比如 MAX、MIN、SUM、AVG 等。看名字大概就能猜到它们是干啥的 很多人对聚合函数感到困惑的地方在于类似下面的查询selectfacid,count(*)fromcd.facilities你试试运行这条语句会发现它报错了。这是因为 count(*) 想把整个 facilities 表聚合成一个值但问题是表里有很多不同的 facidPostgres 不知道该把 count 的结果跟哪个 facid 配对。那如果你想查出每个 facid 以及对应的计数该怎么办呢可以把聚合部分写成子查询像这样selectfacid,(selectcount(*)fromcd.facilities)fromcd.facilities当子查询返回的是一个标量值时Postgres 会自动把这个值重复填充到 cd.facilities 表的每一行中。三十一、统计高价设施数量题目统计对客人收费在 10 及以上的设施数量。预期结果count6【答案与解析】selectcount(*)fromcd.facilitieswhereguestcost10;这道题只是在前一题的基础上做了点小改动我们需要把便宜的设施过滤掉。用 WHERE 子句就能轻松搞定。这样一来聚合函数就只会看到那些收费较高的设施了。三十二、统计每个会员的推荐人数题目统计每个会员推荐了多少人。按会员 ID 排序。预期结果recommendedbycount15233142516192111132151161201301【答案与解析】selectrecommendedby,count(*)fromcd.memberswhererecommendedbyisnotnullgroupbyrecommendedbyorderbyrecommendedby;之前我们见过聚合函数是作用在一列数据上然后把它们聚合成一个标量值。这很有用但我们经常会发现自己想要的不是单个聚合结果比如说我可能不想知道俱乐部这个月总共赚了多少钱而是想知道每个设施分别赚了多少钱或者哪些时间段最赚钱。为了支持这种需求SQL 提供了 GROUP BY 子句。它的作用是把数据分成若干组然后对每组分别运行聚合函数。当你指定了 GROUP BY数据库会为指定列中的每个不同值生成一个聚合结果。在这个例子里我们的意思是对于 recommendedby 的每个不同值统计它出现了多少次。三十三、列出每个设施的总预订时段数题目列出每个设施的总预订时段数。目前只需要生成一个包含设施 ID 和时段数的输出表按设施 ID 排序。预期结果facidTotal Slots01320112782120938304140452286110479088911【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidorderbyfacid;除了引入了 SUM 聚合函数之外这道题没什么特别需要说明的。对于每个不同的设施 IDSUM 函数会把 slots 列中的所有值加起来。三十四、列出指定月份每个设施的总预订时段数题目列出 2012 年 9 月每个设施的总预订时段数。生成一个包含设施 ID 和时段数的输出表按时段数排序。预期结果facidTotal Slots512234227426847165402570158805914648【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingswherestarttime2012-09-01andstarttime2012-10-01groupbyfacidorderbysum(slots);这道题只是在前一题的基础上做了点小改动。记住聚合是在 WHERE 子句执行之后才进行的所以我们用 WHERE 来限制要聚合的数据范围这样聚合函数就只会看到一个月的数据了。三十五、列出每个月每个设施的总预订时段数题目列出 2012 年每个月每个设施的总预订时段数。生成一个包含设施 ID、月份和时段数的输出表按设施 ID 和月份排序。预期结果facidmonthTotal Slots0727008459095911720718483195882718028459295703710438304394224726448492496485724588259122671646840069540771567832679426871178832289471【答案与解析】selectfacid,extract(monthfromstarttime)asmonth,sum(slots)asTotal Slotsfromcd.bookingswhereextract(yearfromstarttime)2012groupbyfacid,monthorderbyfacid,month;这道题新引入的主要功能是 EXTRACT 函数。EXTRACT 可以从时间戳中提取出各个组成部分比如日、月、年等。我们按照这个函数的输出进行分组就能得到每月的统计值。如果需要区分不同年份的相同月份可以使用 DATE_TRUNC 函数它能把日期截断到指定的粒度。另外值得一提的是这是我们第一次真正用到按多列分组的功能。关于这个答案有一点需要注意在 WHERE 子句中使用 EXTRACT 函数可能会在大表上导致严重的性能问题。如果 timestamp 列上有普通索引Postgres 无法利用索引来加速查询只能全表扫描。你有以下几种解决方案考虑在 timestamp 列上创建表达式索引。有了合适的索引Postgres 就能用索引来加速包含函数调用的 WHERE 子句。修改查询虽然写得稍微啰嗦一点但使用更标准的比较方式例如selectfacid,extract(monthfromstarttime)asmonth,sum(slots)asTotal Slotsfromcd.bookingswherestarttime2012-01-01andstarttime2013-01-01groupbyfacid,monthorderbyfacid,month;对于这种标准比较Postgres 无需额外帮助就能使用索引。三十六、统计至少预订过一次的会员数量题目统计至少预订过一次的会员包括访客总数。预期结果count30【答案与解析】selectcount(distinctmemid)fromcd.bookings你第一反应可能是用子查询像这样selectcount(*)from(selectdistinctmemidfromcd.bookings)asmems这样确实能正常工作但我们可以借助 COUNT DISTINCT 稍微简化一下。它的作用正如你所料统计指定列中不同值的数量。三十七、列出预订时段超过 1000 的设施题目列出预订时段总数超过 1000 的设施。生成一个包含设施 ID 和时段数的输出表按设施 ID 排序。预期结果facidTotal Slots0132011278212094140461104【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidhavingsum(slots)1000orderbyfacid原来 SQL 中有一个专门用来过滤聚合函数输出的关键字这就是 HAVING。HAVING 的行为很容易和 WHERE 混淆。最好的理解方式是在包含聚合函数的查询中WHERE 用来过滤进入聚合函数的数据而 HAVING 用来过滤聚合函数输出的数据。你可以多试试体会一下它们的区别三十八、计算每个设施的总收入题目列出每个设施及其总收入。输出表应包含设施名称和收入按收入排序。记住访客和会员的收费是不一样的预期结果namerevenueTable Tennis180Snooker Table240Pool Table270Badminton Court1906.5Squash Court13468.0Tennis Court 113860Tennis Court 214310Massage Room 215810Massage Room 172540【答案与解析】selectfacs.name,sum(slots*casewhenmemid0thenfacs.guestcostelsefacs.membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.nameorderbyrevenue;这道题唯一的复杂之处在于访客会员 ID 为 0的收费和其他人不一样。我们用 case 语句来计算每次预订的费用然后按设施分组把每次预订的费用加起来。三十九、找出总收入低于 1000 的设施题目列出总收入低于 1000 的设施。输出表应包含设施名称和收入按收入排序。记住访客和会员的收费是不一样的预期结果namerevenueTable Tennis180Snooker Table240Pool Table270【答案与解析】selectname,revenuefrom(selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.name)asaggwhererevenue1000orderbyrevenue;你可能尝试过用我们在前面练习中介绍的 HAVING 关键字写出类似下面的查询selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.namehavingrevenue1000orderbyrevenue;可惜这样行不通你会收到类似这样的错误ERROR: column “revenue” does not exist。和 SQL Server、MySQL 等其他关系型数据库不同Postgres 不支持在 HAVING 子句中使用列别名。所以要让这个查询正常工作你得写成下面这样selectfacs.name,sum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)asrevenuefromcd.bookings bksinnerjoincd.facilities facsonbks.facidfacs.facidgroupbyfacs.namehavingsum(casewhenmemid0thenslots*facs.guestcostelseslots*membercostend)1000orderbyrevenue;像这样重复大量的计算代码显得很 messy所以我们推荐的解决方案是把主查询体包装成子查询然后用 WHERE 子句从中筛选。一般来说对于简单的查询我建议用 HAVING因为它更清晰。而对于复杂的情况这种子查询的方式往往更好用。四十、输出预订时段数最多的设施 ID题目输出预订时段数最多的设施 ID。如果要挑战一下试着写一个不用 LIMIT 子句的版本。这个版本可能会看起来很复杂预期结果facidTotal Slots41404【答案与解析】selectfacid,sum(slots)asTotal Slotsfromcd.bookingsgroupbyfacidorderbysum(slots)descLIMIT1;我们先从最简单的方法开始生成一个包含设施 ID 和总预订时段数的列表按总时段数降序排序然后只取第一条结果。不过需要注意的是这种方法有个明显的缺陷如果出现平局多个设施的时段数相同我们仍然只能得到一个结果要想得到所有符合条件的结果我们可以尝试用 MAX 聚合函数像这样selectfacid,max(totalslots)from(selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacid)assubgroupbyfacid这个查询的意图是获取最高的 totalslots 值及其对应的 facid。可惜这样行不通如果有多个 facid 的时段数相同Postgres 就无法确定应该把哪个 facid 和 MAX 函数输出的单个标量值配对。所以 Postgres 会提示你 facid 应该放在 GROUP BY 子句中但这不会产生我们想要的结果。让我们先尝试写一个能正常工作的查询selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacidhavingsum(slots)(selectmax(sum2.totalslots)from(selectsum(slots)astotalslotsfromcd.bookingsgroupbyfacid)assum2);这个查询先生成设施 ID 和对应的时段数列表然后用 HAVING 子句计算出最大的 totalslots 值。我们的逻辑基本上是“生成 facid 及其预订时段数的列表然后过滤掉那些时段数不等于最大值的结果。”虽然 HAVING 很有用但我们的查询写得有点丑。为了改进它我们再引入一个新概念公用表表达式CTE。CTE 可以让你在查询中内联定义一个数据库视图。在像这种需要重复代码的场景中它特别有用。CTE 的声明形式是 WITH CTE名称 as (SQL表达式)。你可以看到我们用 CTE 重写了上面的查询withsumas(selectfacid,sum(slots)astotalslotsfromcd.bookingsgroupbyfacid)selectfacid,totalslotsfromsumwheretotalslots(selectmax(totalslots)fromsum);可以看到我们把重复的 cd.bookings 查询提取到了一个 CTE 中让整个查询变得更易读了等等还没完。其实还可以用窗口函数来解决这个问题。我们后面再讲窗口函数但对于这类问题确实有更好的解决方案。一道练习题讲了这么多内容。如果现在不能完全理解也不用太担心——我们会在后面的练习中继续用到这些概念。
