LeetCode MySQL 1501. 可以放心投资的国家
LeetCode MySQL 1501. 可以放心投资的国家
Michael阿明
发布于 2021-02-19 10:35:59
发布于 2021-02-19 10:35:59
文章被收录于专栏：Michael阿明学习之路
文章目录
1. 题目
表 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.com/problems/countries-you-can-safely-invest-in 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
2. 解题
- 先找出打电话的人是哪个国家的
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code{"headers": ["id", "country"],
"values": [
[3, "Peru"],
[12, "Peru"],
[1, "Morocco"],
[2, "Morocco"],
[7, "Israel"],
[9, "Israel"]]}
在这里插入图片描述
- 计算打出去的人的分钟数和人数
# Write your MySQL query statement below
with people_country as
(
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code
)
select country, count(*) num, sum(duration) calltime
from Calls c1 left join people_country
on c1.caller_id = people_country.id
group by country{"headers": ["country", "num", "calltime"],
"values": [
["Morocco", 4, 103],
["Peru", 3, 437],
["Israel", 3, 17]]}- 统计接听的人的人数和分钟数，并合并
# Write your MySQL query statement below
with people_country as
(
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code
)
select country, count(*) num, sum(duration) calltime
from Calls c1 left join people_country
on c1.caller_id = people_country.id
group by country
union all
select country, count(*) num, sum(duration) calltime
from Calls c2 left join people_country
on c2.callee_id = people_country.id
group by country{"headers": ["country", "num", "calltime"],
"values": [
["Morocco", 4, 103],
["Peru", 3, 437],
["Israel", 3, 17],
["Israel", 5, 58],
["Morocco", 2, 62],
["Peru", 3, 437]]}- 最终答案
# Write your MySQL query statement below
with people_country as
(
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code
)
select country
from
(
select country, sum(num) totalnum, sum(calltime) totaltime, sum(calltime)/sum(num) avgtime
from
(
select country, count(*) num, sum(duration) calltime
from Calls c1 left join people_country
on c1.caller_id = people_country.id
group by country
union all
select country, count(*) num, sum(duration) calltime
from Calls c2 left join people_country
on c2.callee_id = people_country.id
group by country
) t
group by country
) temp
where avgtime >
(
select avg(duration) avgtime
from
(
select caller_id, duration
from Calls
union all
select callee_id, duration
from Calls
) t
)- 更简洁一点
# Write your MySQL query statement below
with people_country as
(
select id, c.name country
from Person p left join Country c
on left(p.phone_number,3) = c.country_code
)
select country
from
(
select country, avg(duration) avgtime
from
(
select caller_id id, duration
from Calls
union all
select callee_id, duration
from Calls
) t left join people_country
using(id)
group by country
) temp
where avgtime >
(
select avg(duration) avgtime
from
(
select caller_id, duration
from Calls
union all
select callee_id, duration
from Calls
) t
)- 评论区更简洁的答案
# Write your MySQL query statement below
select c2.name as country
from Calls c1, Person p, Country c2
where (p.id=c1.caller_id or p.id=c1.callee_id) and c2.country_code=left(p.phone_number,3)
group by c2.name
having avg(duration)>(select avg(duration) from Calls)本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/07/30 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
