Medium之1501.可以放心投资的国家**

朴灿烈づ我的快乐病毒、 2022-08-31 12:22 79阅读 0赞

**表 Person:**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>phone_number</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

id 是该表主键.  
该表每一行包含一个人的名字和电话号码.  
电话号码的格式是:‘xxx-yyyyyyy’, 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.

**表 Country:**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>country_code</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

country\_code是该表主键.  
该表每一行包含国家名和国家码. country\_code的格式是’xxx’, x是数字.

**表 Calls:**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>caller_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>callee_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>duration</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

该表无主键, 可能包含重复行.  
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller\_id != callee\_id  
一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.

### 问题 ###

写一段 SQL, 找到所有该公司可以投资的国家.

返回的结果表没有顺序要求.

### 示例 ###

**Person 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>phone_number</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>Jonathan</td> 
   <td>051-1234567</td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>Elvis</td> 
   <td>051-7654321</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Moncef</td> 
   <td>212-1234567</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Maroua</td> 
   <td>212-6523651</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Meir</td> 
   <td>972-1234567</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>Rachel</td> 
   <td>972-0011100</td> 
  </tr> 
 </tbody> 
</table>

**Country 表:**

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>country_code</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Peru</td> 
   <td>051</td> 
  </tr> 
  <tr> 
   <td>Israel</td> 
   <td>972</td> 
  </tr> 
  <tr> 
   <td>Morocco</td> 
   <td>212</td> 
  </tr> 
  <tr> 
   <td>Germany</td> 
   <td>049</td> 
  </tr> 
  <tr> 
   <td>Ethiopia</td> 
   <td>251</td> 
  </tr> 
 </tbody> 
</table>

**Calls 表:**

<table> 
 <thead> 
  <tr> 
   <th>caller_id</th> 
   <th>callee_id</th> 
   <th>duration</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>9</td> 
   <td>33</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>9</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>59</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>12</td> 
   <td>102</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>12</td> 
   <td>330</td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>3</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>9</td> 
   <td>13</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>7</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>7</td> 
   <td>7</td> 
  </tr> 
 </tbody> 
</table>

**Result 表:**

<table> 
 <thead> 
  <tr> 
   <th>country</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Peru</td> 
  </tr> 
 </tbody> 
</table>

国家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  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：交叉联结 #####

select co.name as country
    from calls ca,country co,person p
    where (p.id = ca.caller_id or p.id= ca.callee_id) and co.country_code=left(p.phone_number,3)
    group by co.country_code
    having avg(duration)>(select avg(duration) from calls)

(p.id = ca.caller\_id or p.id= ca.callee\_id) 一定要加括号，不然结果不对。

##### 法二：UNION+LEFT JOIN #####

select c.name as country
    from 
    (   select caller_id id,duration from calls
        union 
        select callee_id id,duration from calls
    ) t left join person p on t.id = p.id 
    left join country c on c.country_code= left(p.phone_number,3)
    group by c.country_code
    having avg(duration)>(select avg(duration) from calls)