70，SQL训练之，力扣，1501. 可以放心投资的国家

最新推荐文章于 2024-11-16 17:15:55 发布

凡梦_leo

最新推荐文章于 2024-11-16 17:15:55 发布

阅读量723

点赞数 14

分类专栏： SQL实战训练文章标签： linux python sql 数据库力扣 mysql leetcode

本文链接：https://blog.csdn.net/qq_55006020/article/details/142169969

版权

SQL实战训练专栏收录该内容

153 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表 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

一家电信公司想要投资新的国家。该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长。

写一个解决方案, 找到所有该公司可以投资的国家。

返回的结果表 无顺序要求。

结果格式如下例所示。

示例 1:

输入：
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        |
+-----------+-----------+----------+
输出：
+----------+
| 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 是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

三，建表语句


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);
Truncate table Person;
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');
Truncate table Country;
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');
Truncate table Calls;
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');

四，分析

图解：

思路:

表分析：

表一：人类表

字段：编号，姓名，电话号码

表二：国家表

字段：国家名称，国家编号（是电话号码- 前面的编号）

表三：

通话记录表

字段：呼叫方，接通方，通话时长

第一步：把人类表的电话号码转化为国家编号：

第二步：拼接人类表和国家表

第三步：处理通话表：

第四步：拼接三个表：

第五步：

求两个平均值，最后取通话时长大于平均时长的国家名称；

五，SQL解答

with t1 as (
    select id, name, phone_number,
       substring_index(phone_number, '-', 1)  as country_code
    from person
),t2 as (
    select
        t1.id, t1.name, t1.phone_number, t1.country_code, c1.name as c_name, c1.country_code as c1_count_code
        from t1 left join country c1 on t1.country_code=c1.country_code
),t3 as (
    select caller_id as c1, callee_id as c2, duration from calls
),t4 as (
    select distinct c1, c2, duration from t3
    union
    select distinct c2,c1,duration from t3
),t5 as (
    select c1, c2, duration,
        avg(duration) over() as 平均国家的通话
from t4
),t6 as (
    select * from t5 left join t2 on t5.c1=t2.id
),t7 as (
    select
        *,
        avg(duration) over(partition by c_name) as name_avg
    from t6
),t8 as (
    select distinct case when name_avg>平均国家的通话 then c_name end as c_name from t7
),t9 as (
    select c_name as country  from t8 where c_name is not null
)
select * from t9;

简化后的代码：

with t1 as (
    select id, p1.name, phone_number,
       substring_index(phone_number, '-', 1)  as country_code,c1.name as c_name, c1.country_code as c_country_code
    from person p1 left join country c1 on c1.country_code=substring_index(p1.phone_number, '-', 1)
),t2 as (
    select distinct caller_id c1, callee_id c2, duration from calls
),t3 as (
    select c1, c2, duration from t2
    union
    select c2,c1,duration from t2
),t4 as (
    select *,
           avg(duration) over() as avg1,
           avg(duration) over(partition by c_name) as avg2
        from t3 left join t1 on t3.c1=t1.id
),t5 as (
    select distinct if(avg2>avg1,c_name,null ) country  from t4
)
select country from t5 where country is not null;