Easy之1294.不同国家的天气类型

谁践踏了优雅 2022-10-11 01:23 146阅读 0赞

### 国家表：Countries ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>country_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>country_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

country\_id 是这张表的主键。  
该表的每行有 country\_id 和 country\_name 两列。

### 天气表：Weather ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>country_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>weather_state</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>day</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

(country\_id, day) 是该表的复合主键。  
该表的每一行记录了某个国家某一天的天气情况。

### 问题 ###

写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

天气类型的定义如下：当 weather\_state 的平均值小于或等于15返回 Cold，当 weather\_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。

你可以以任意顺序返回你的查询结果。

### 示例 ###

查询结果格式如下所示：

Countries table:

<table> 
 <thead> 
  <tr> 
   <th>country_id</th> 
   <th>country_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>USA</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Australia</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Peru</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>China</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>Morocco</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>Spain</td> 
  </tr> 
 </tbody> 
</table>

Weather table:

<table> 
 <thead> 
  <tr> 
   <th>country_id</th> 
   <th>weather_state</th> 
   <th>day</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>15</td> 
   <td>2019-11-01</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>12</td> 
   <td>2019-10-28</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>12</td> 
   <td>2019-10-27</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>-2</td> 
   <td>2019-11-10</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>0</td> 
   <td>2019-11-11</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>2019-11-12</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>16</td> 
   <td>2019-11-07</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>18</td> 
   <td>2019-11-09</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>21</td> 
   <td>2019-11-23</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>25</td> 
   <td>2019-11-28</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>22</td> 
   <td>2019-12-01</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>20</td> 
   <td>2019-12-02</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>25</td> 
   <td>2019-11-05</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>27</td> 
   <td>2019-11-15</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>31</td> 
   <td>2019-11-25</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>7</td> 
   <td>2019-10-23</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>3</td> 
   <td>2019-12-23</td> 
  </tr> 
 </tbody> 
</table>

Result table:

<table> 
 <thead> 
  <tr> 
   <th>country_name</th> 
   <th>weather_type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>USA</td> 
   <td>Cold</td> 
  </tr> 
  <tr> 
   <td>Austraila</td> 
   <td>Cold</td> 
  </tr> 
  <tr> 
   <td>Peru</td> 
   <td>Hot</td> 
  </tr> 
  <tr> 
   <td>China</td> 
   <td>Warm</td> 
  </tr> 
  <tr> 
   <td>Morocco</td> 
   <td>Hot</td> 
  </tr> 
 </tbody> 
</table>

##### 解释 #####

USA 11 月的平均 weather\_state 为 (15) / 1 = 15 所以天气类型为 Cold。  
Australia 11 月的平均 weather\_state 为 (-2 + 0 + 3) / 3 = 0.333 所以天气类型为 Cold。  
Peru 11 月的平均 weather\_state 为 (25) / 1 = 25 所以天气类型为 Hot。  
China 11 月的平均 weather\_state 为 (16 + 18 + 21) / 3 = 18.333 所以天气类型为 Warm。  
Morocco 11 月的平均 weather\_state 为 (25 + 27 + 31) / 3 = 27.667 所以天气类型为 Hot。  
我们并不知道 Spain 在 11 月的 weather\_state 情况所以无需将他包含在结果中。

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/weather-type-in-each-country  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一 #####

select c.country_name,
        case when avg(weather_state) <= 15 then 'Cold'
          when avg(weather_state) >=25  then 'Hot'
         else 'Warm' end as weather_type
    from weather w  left join countries c 
    on w.country_id = c.country_id 
    where left(w.day,7)=11
    group by c.country_name

##### 知识点 #####

CASE WHEN  
关于选择2019年11月的方法：

1.  left(day,7)=11
2.  day between 2019-11-01 and 2019-11-30
3.  month(day)=11
4.  day like ‘2019-11%’