Leetcode 1501题: Countries You Can Safely Invest In

题目描述

这道题，相对来说比较复杂，总共需要用到3张表，以及多次子查询。下面是3张表的信息。

Person表

±---------------±--------+
| Column Name | Type |
±---------------±--------+
| id | int |
| name | varchar |
| phone_number | varchar |
±---------------±--------+
总共3列,分别是用户id,用户姓名以及用户的电话号码，其中电话号码的前3位为地区号。

Country表

±---------------±--------+
| Column Name | Type |
±---------------±--------+
| name | varchar |
| country_code | varchar |
±---------------±--------+
总共2列，分别是国家名称以及国家代号，同上面的电话号码地区号。

Calls 表

±------------±-----+
| Column Name | Type |
±------------±-----+
| caller_id | int |
| callee_id | int |
| duration | int |
±------------±-----+
总共3列，分别是电话发起方的id,电话接收方的id以及通话时长。

题目要求:找到平均通话时长大于global平均通话时长的那个国家的名称。

My Solution

1.因为duration需要考虑通话双方，因此，第一步我们需要将Calls表中的拨打和接收方信息合并成一列，duration单独一列。这里我使用的是mysql中的union all方法。

2.将上述得到的表的用户id转换得到对应的区号，这里需要需要将第1步得到的表与Person表进行左连接，同时在获取区号的时候，我们需要用到字符串的截取，使用left(phone_number,3)。

3.将2步得到结果表按照区号进行group by,并计算平均通话时长sum(duration)/count(*)

4.另外构建一张子查询表用于计算global通话时长。

5.使用where条件筛选出3表中平均通话时长大于从4表中得到的global通话时长的国家区号

6.将5的到的结果与Country表再进行左连接，并选取对应的国家名称。

完整代码

select name country
 from 
(select country_code from (select country_code,sum(duration)/count(*) avg from (select country_code,duration from (select caller_id calleree_id,duration from Calls union all select callee_id calleree_id,duration from Calls) as t0 left join (select id,left(phone_number,3) country_code from Person) as t1 on t0.calleree_id=t1.id) as t2 group by country_code) as t3,(select sum(duration)*2/(count(*)*2) global_avg from Calls) as t4 where t3.avg>t4.global_avg) as t5
left join
Country
on t5.country_code=Country.country_code;