首页 > 其他分享 >1501. 可以放心投资的国家#三种方法

1501. 可以放心投资的国家#三种方法

时间：2024-10-21 13:45:47浏览次数：9  
标签：10 Calls insert 1501 三种 2023 duration 放心 id

目录

题目和要求

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

1. 题目代码

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');

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');

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');

2. 解题思路流程图分析1 - 方法1

连接Person表 分组 计算平均通话时长 是 连接Country表 Calls表 提取电话号码前三位 按国家代码分组 平均时长 > 全局平均? 筛选国家代码 获取国家名称

2. 解题思路流程图分析2 - 方法2

转换为 Call_ID 和 Duration 连接Person表 连接Country表 分组并计算平均时长 是 Calls表 合并Caller和Callee为单一 Call_ID 提取电话号码前三位 获取国家名称 平均时长 > 全局平均? 筛选国家名称

3. 解题思路流程图分析1 - 方法1

2023-10-01 2023-10-01 2023-10-02 2023-10-02 2023-10-03 2023-10-03 2023-10-04 2023-10-04 2023-10-05 连接Calls与Person表获取电话号码前三位 按国家代码分组并计算平均通话时长 比较每个国家的平均时长与全局平均 筛选符合条件的国家并获取国家名称 数据准备 数据处理 数据输出 方法1 - 流程步骤

3. 解题思路流程图分析2 - 方法2

2023-10-01 2023-10-01 2023-10-02 2023-10-02 2023-10-03 2023-10-03 2023-10-04 2023-10-04 2023-10-05 2023-10-05 2023-10-06 2023-10-06 2023-10-07 转换Calls表为单一 Call_ID 列表 连接Person表获取电话号码前三位 连接Country表获取国家名称 按国家名称分组并计算平均通话时长 比较每个国家的平均时长与全局平均 筛选符合条件的国家名称 数据准备 数据处理 聚合与筛选 方法2 - 流程步骤

4. 难点分析

本题的难点主要在于：

  1. 多表连接：需要同时连接 Calls、Person 和 Country 三个表，确保正确匹配通话双方的国家代码。
  2. 数据转换与提取：从电话号码中提取国家代码部分，并将其与 Country 表中的国家代码进行匹配。
  3. 聚合与条件筛选：计算每个国家的平均通话时长，并与全局的平均通话时长进行比较，筛选出满足条件的国家。
  4. 优化查询性能：避免使用笛卡尔积等低效的查询方式，尤其在数据量大的情况下，确保查询的高效性。

5. 答案代码

-- 方法1 avg条件
with a1 as (
select left(phone_number,3) country_code -- 字符截取从左开始,截取3个,eg:left('asdf',3)=asd
from Calls c
left join Person p on c.caller_id=p.id or p.id=c.callee_id-- 因为只关心Calls中的数据,所以left join 更为严谨
group by left(phone_number,3)
having avg(case when id in (caller_id and callee_id) then duration
                when id in (caller_id and callee_id) then duration
                else duration end)>(select avg(duration) from Calls) )
-- 这里只能用case when,一开始我是用avg(if( id in (caller_id and callee_id),duration*2,duration))虽然提交都能通过(有时候力扣测试案例不全面,需自己多思考)
-- 但是我后思发现用avg(if())算出来的Morocco=44.5000不是27.5000 (用窗口函数验证出来的)
-- 这是因为duration*2或者duration+duration,被avg视为一个整体,即每次少一个分母个数
select name country
from Country c
join a1 on a1.country_code=c.country_code;

-- 方法2 重构表 left join
select c.name country
from
    (select caller_id as call_id, duration from Calls
    union all
    select callee_id as call_id, duration from Calls) as a
left join Person p on p.id=a.call_id-- 因为只关心Calls中的数据,所以left join 更为严谨
left join Country c on c.country_code=left(p.phone_number,3)
group by c.name
having avg(duration)> (select avg(duration) from Calls);

# 下面还有一个笛卡尔积的方法,虽然短但是非常不推荐,因为这么写笛卡尔积要把数据库炸了,实际生产环境中通话记录、用户数的数据量肯定巨大
# SELECT c.name AS country
# FROM Calls, Person, Country c
# WHERE (caller_id = id OR callee_id = id) AND country_code = LEFT(phone_number, 3)
# GROUP BY country_code
# HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls);

6. 关键总结

要掌握本题的关键知识点包括：

  • 多表连接（JOIN）：熟练使用 JOIN 进行多表数据的关联，特别是 LEFT JOIN 在确保数据完整性方面的重要性。
  • 字符串处理函数：如 LEFT()，用于从电话号码中提取国家代码部分。
  • 聚合函数与分组（GROUP BY）：能够按特定字段分组数据，并应用聚合函数如 AVG() 进行计算。
  • 子查询：在 HAVING 子句中使用子查询来计算全局的平均值，以便进行比较筛选。
  • 条件表达式：如 IF() 函数，用于根据特定条件调整计算逻辑。
  • CTE（公用表表达式）：使用 WITH 语句简化复杂查询，提升代码的可读性和维护性。
  • 性能优化：理解为什么某些查询方式（如笛卡尔积）在大数据量情况下效率低下，学会选择更高效的查询策略。

通过以上知识点的掌握，可以有效解决类似的复杂SQL查询问题。

标签：10,Calls,insert,1501,三种,2023,duration,放心,id
From： https://blog.csdn.net/weixin_74002941/article/details/143109369

相关文章

  • Java-三种线程的实现方式
    1.继承Thread类可以通过创建一个新的类继承Thread类，并重写其run方法来实现线程。classMyThreadextendsThread{@Overridepublicvoidrun(){System.out.println("线程运行中："+Thread.currentThread().getName());//线程要执行的代码......
  • EF Core 中避免 SQL 注入的三种写法
    SQL注入攻击可能会对我们的应用程序产生严重影响，导致敏感数据泄露、未经授权的访问和应用程序受损。EFCore提供了三种内置机制来防止SQL注入攻击。1、利用LINQ查询语法和参数化查询，这是比较推荐的做法。awaitusingvarcontext=newPostgresContext();varauthor=......
  • PCB三种特殊走线技巧，你都get到了吗？
    布线的质量直接关系到整个系统的效能，走线的好坏将直接影响到整个系统的性能，大多数高速的设计理论也要最终经过Layout得以实现并验证本文将探讨在布线过程中常见的直角走线、差分走线和蛇形线三种情况，下面将针对实际布线中可能遇到的一些情况，分析其合理性，并给出一些比较优化......
  • CSV、XML、JSON三种形式进行存储并读取
    下面是一个完整的Python示例代码，它可以生成简单的算式（加法、减法、乘法、除法），并将生成的算式和习题长期保存到CSV、XML和JSON三种格式中。代码包括生成算式的功能，以及将数据保存和读取的功能。1.代码实现pythonimportcsvimportjsonimportxml.etree.ElementTreeas......
  • 三种Transformer模型中的注意力机制介绍及Pytorch实现：从自注意力到因果自注意力
    本文深入探讨Transformer模型中三种关键的注意力机制：自注意力、交叉注意力和因果自注意力。这些机制是GPT-4、Llama等大型语言模型（LLMs）的核心组件。通过理解这些注意力机制，我们可以更好地把握这些模型的工作原理和应用潜力。我们不仅会讨论理论概念，还将使用Python和PyTorch从零开......
  • 软件构造，生成算式采用CSV、XML、JSON三种形式进行存储并读取。
    编写代码完成将生成的算式及习题长期保存下来，采用CSV、XML、JSON三种形式进行存储并读取。提交相关代码及运行截图。importrandomimportcsvimportjsonimportxml.etree.ElementTreeasETfromxml.domimportminidom#生成随机算式数据defgenerate_exercises(count......
  • mongo对文档中数组进行过滤的三种方法
    前言在mongo中数据类型有很多种，常见的包括：数据类型例子描述String{"x":"foot"}字符串。存储数据常用的数据类型。在MongoDB中，UTF-8编码的字符串才是合法的。Integer{"x":1}整型数值。用于存储数值。根据你所采用的服务器，可分为32位或64位。......
  • Fmoc-Val-Ala-OH|N-[芴甲氧羰基]-L-缬氨酰-L-丙氨酸|CAS号：150114-97-9
    Fmoc-Val-Ala-OH（也称为Fmoc-Val-Ala-O-t-butyl酯）是一种重要的化学物质，以下是对其的详细介绍：一、基本信息化学名称：N-[芴甲氧羰基]-L-缬氨酰-L-丙氨酸CAS号：150114-97-9分子式：C23H26N2O5分子量：410.47结构式：二、化学性质Fmoc-Val-Ala-OH是一种可降解的ADClinker，可用于合成抗......
  • 缓存常用的三种读写策略
    在现代软件系统中，缓存的使用至关重要，它可以极大地提高系统的性能和响应速度。本文将详细介绍缓存常用的三种读写策略：CacheAsidePattern（旁路缓存模式）、Read/WriteThroughPattern（读写穿透）、WriteBehindPattern（异步缓存写入）。一、CacheAsidePattern（旁路缓存模式）Cac......
  • msvcp110丢失怎么解决？三种方法教你修复msvcp110.dll丢失
    1.msvcp110.dll概述1.1定义与作用msvcp110.dll是MicrosoftVisualC++2012RedistributablePackage的一部分，它是一个动态链接库（DLL）文件，对运行时环境至关重要。这个文件包含了C++标准库的实现，为基于VisualC++2012编译的应用程序提供了必要的运行时支持。msvcp1......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99