相信很多学习SQL的小伙伴都面临这样的困境，学习完书本上的SQL基础知识后，一方面想测试下自己的水平；另一方面想进一步提升，却不知道方法。
其实，对于技能型知识，我的观点一贯都是：多练习、多实践。正所谓实践出真知，学完书本的知识，很多时候也只能做到知道，距离熟练的应用还差的很远。
在咱们程序员圈子里，力扣（LeetCode）和牛客（nowcoder.com）是两个公认比较好的实践平台。题库比较多，还有不少大厂的笔试真题，特别适合找工作时撸一撸。当然，作为平时个人技术提升的练习题，也是非常不错的。
最近一段时间，我会先从力扣（LeetCode）的SQL题刷起。当然，顺序可能是随机的，欢迎小伙伴们点题。
题目：1501. 可以放心投资的国家
（通过次数8,646 | 提交次数14,189，通过率60.93%）
表Person:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| id | int |
| name | varchar |
| phone_number | varchar |
+----------------+---------+
id 是该表主键.
该表每一行包含一个人的名字和电话号码.
电话号码的格式是:'xxx-yyyyyyy', 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.
表Country:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| name | varchar |
| country_code | varchar |
+----------------+---------+
country_code是该表主键.
该表每一行包含国家名和国家码. country_code的格式是'xxx', x是数字.
表Calls:
+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id | int |
| callee_id | int |
| duration | int |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id
一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.
写一段 SQL,找到所有该公司可以投资的国家.
返回的结果表没有顺序要求.
查询的结果格式如下例所示.
Person 表:
+----+----------+--------------+
| id | name | phone_number |
+----+----------+--------------+
| 3 | Jonathan | 051-1234567 |
| 12 | Elvis | 051-7654321 |
| 1 | Moncef | 212-1234567 |
| 2 | Maroua | 212-6523651 |
| 7 | Meir | 972-1234567 |
| 9 | Rachel | 972-0011100 |
+----+----------+--------------+
Country 表:
+----------+--------------+
| name | country_code |
+----------+--------------+
| Peru | 051 |
| Israel | 972 |
| Morocco | 212 |
| Germany | 049 |
| Ethiopia | 251 |
+----------+--------------+
Calls 表:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1 | 9 | 33 |
| 2 | 9 | 4 |
| 1 | 2 | 59 |
| 3 | 12 | 102 |
| 3 | 12 | 330 |
| 12 | 3 | 5 |
| 7 | 9 | 13 |
| 7 | 1 | 3 |
| 9 | 7 | 1 |
| 1 | 7 | 7 |
+-----------+-----------+----------+
Result 表:
+----------+
| country |
+----------+
| Peru |
+----------+
国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000
全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.
来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/countries-you-can-safely-invest-in
#测试数据
Create table If Not Exists Person (id int, name varchar(15), phone_number varchar(11));
Create table If Not Exists Country (name varchar(15), country_code varchar(3));
Create table If Not Exists Calls (caller_id int, callee_id int, duration int);
insert into Person (id, name, phone_number) values ('3', 'Jonathan', '051-1234567');
insert into Person (id, name, phone_number) values ('12', 'Elvis', '051-7654321');
insert into Person (id, name, phone_number) values ('1', 'Moncef', '212-1234567');
insert into Person (id, name, phone_number) values ('2', 'Maroua', '212-6523651');
insert into Person (id, name, phone_number) values ('7', 'Meir', '972-1234567');
insert into Person (id, name, phone_number) values ('9', 'Rachel', '972-0011100');
insert into Country (name, country_code) values ('Peru', '051');
insert into Country (name, country_code) values ('Israel', '972');
insert into Country (name, country_code) values ('Morocco', '212');
insert into Country (name, country_code) values ('Germany', '049');
insert into Country (name, country_code) values ('Ethiopia', '251');
insert into Calls (caller_id, callee_id, duration) values ('1', '9', '33');
insert into Calls (caller_id, callee_id, duration) values ('2', '9', '4');
insert into Calls (caller_id, callee_id, duration) values ('1', '2', '59');
insert into Calls (caller_id, callee_id, duration) values ('3', '12', '102');
insert into Calls (caller_id, callee_id, duration) values ('3', '12', '330');
insert into Calls (caller_id, callee_id, duration) values ('12', '3', '5');
insert into Calls (caller_id, callee_id, duration) values ('7', '9', '13');
insert into Calls (caller_id, callee_id, duration) values ('7', '1', '3');
insert into Calls (caller_id, callee_id, duration) values ('9', '7', '1');
insert into Calls (caller_id, callee_id, duration) values ('1', '7', '7');
解题思路：
这道题的逻辑本身并不难：分别计算出每个国家的平均通话时长和全球的平均通话时长，然后做个简单的比较就可以了。
比较麻烦的是，题目涉及到的表比较多。
我之前的工作中，有碰到一些同事，只要看到表比较多就懵了，不知道怎么下手。
其实，表多一些没关系，只要理清楚表与表之间的关系就可以了。而表与表之间的关系，可以从以下两方面来理解。
一方面是业务上，比如：一个国家可以有很多人（这不是废话么，还需要理解？其实这只是本题涉及的业务是我们日常生活中，比较常见的。实际工作中，业务很可能比较复杂，理解起来还需要很深的专业知识才行。），一个人可以打很多通电话等。
另一方面是技术上，比如：一个表的主键、外键、索引等。
为方便理解，我画了下面的模型图。图中标示了表的主键、外键，以及表与表之间的关系。
从题目以及上图可以了解到，Calls表记录的是每一条通话记录，包括通话双方的人员ID、通话时长。
通过人员ID可以找到phone_number（电话号码）；phone_number的前3位为country_code（国家代码）；然后，通过country_code可以找到name（国家名称）；最后，就可以找出每个国家的通话记录，进而算出平均通话时长。
因为每一条通话记录，都需要按打出方和接收方分别计算。所以，在具体的SQL编写上，有两个思路。
一个是，把Calls表按打出方和接收方拆分出来（写2句select），再合并（union all）成一个临时表。具体SQL就不写了。
另一个是，使用Persion表与Calls表直接关联，将通话记录发散到每个人名下，然后再按country_code取出平均值。具体SQL语句如下：
select
left(a.phone_number, 3),
avg(b.duration)
from Person a
inner join Calls b
on a.id = b.caller_id
or a.id = b.callee_id
group by left(a.phone_number, 3);
注意：on后面的两个条件是or的关系。因为一条通话记录的打出方和接收方不会是同一个人，所以以上写法是没有问题的。否则，如果可以是同一个人，就会导致通话记录只被一次，从而影响计算结果的准确性。
至于全球平均通话时长，因为不用关心具体是哪个国家的通话记录，直接从Calls表就可以计算得出：select avg(duration) from calls。（虽然一条通话记录，需要分别计算到打出方和接收方，但因为所有通话记录都需要重复计算1次，所以在通话记录表直接使用avg聚合函数算出来的结果也是正确的）
参考SQL：
with country_avg_duration as
(
select
left(a.phone_number, 3) country_code,
avg(b.duration) avg_duration
from Person a
inner join Calls b
on a.id = b.caller_id
or a.id = b.callee_id
group by left(a.phone_number, 3)
)
select
b.name as country
from country_avg_duration a
inner join Country b
on a.country_code = b.country_code
where a.avg_duration > (select avg(duration) from Calls)
相关文章
您阅读本篇文章共花了：
发表评论