2021-06-03

不同国家的天气类型

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

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

展示效果：

+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA          | Cold         |
| Austraila    | Cold         |
| Peru         | Hot          |
| China        | Warm         |
| Morocco      | Hot          |
+--------------+--------------+

建表语句：

Create table If Not Exists 70_Countries (country_id int, country_name varchar(20));
Create table If Not Exists 70_Weather (country_id int, weather_state int, day date);
Truncate table 70_Countries;
insert into 70_Countries (country_id, country_name) values ('2', 'USA');
insert into 70_Countries (country_id, country_name) values ('3', 'Australia');
insert into 70_Countries (country_id, country_name) values ('7', 'Peru');
insert into 70_Countries (country_id, country_name) values ('5', 'China');
insert into 70_Countries (country_id, country_name) values ('8', 'Morocco');
insert into 70_Countries (country_id, country_name) values ('9', 'Spain');
Truncate table 70_Weather;
insert into 70_Weather (country_id, weather_state, day) values ('2', '15', '2019-11-01');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-28');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-27');
insert into 70_Weather (country_id, weather_state, day) values ('3', '-2', '2019-11-10');
insert into 70_Weather (country_id, weather_state, day) values ('3', '0', '2019-11-11');
insert into 70_Weather (country_id, weather_state, day) values ('3', '3', '2019-11-12');
insert into 70_Weather (country_id, weather_state, day) values ('5', '16', '2019-11-07');
insert into 70_Weather (country_id, weather_state, day) values ('5', '18', '2019-11-09');
insert into 70_Weather (country_id, weather_state, day) values ('5', '21', '2019-11-23');
insert into 70_Weather (country_id, weather_state, day) values ('7', '25', '2019-11-28');
insert into 70_Weather (country_id, weather_state, day) values ('7', '22', '2019-12-01');
insert into 70_Weather (country_id, weather_state, day) values ('8', '25', '2019-11-05');
insert into 70_Weather (country_id, weather_state, day) values ('8', '27', '2019-11-15');
insert into 70_Weather (country_id, weather_state, day) values ('8', '31', '2019-11-25');
insert into 70_Weather (country_id, weather_state, day) values ('9', '7', '2019-10-23');
insert into 70_Weather (country_id, weather_state, day) values ('9', '3', '2019-12-23');

最终sql：

-- 内核使用hive来编写
-- 第一步
select
	country_name,
	sum(weather_state) sum,
	count(day) num
	
from 
	70_Countries t1
left
	70_Weather t2
on
	t1.country_id = t2.country_id
where
	date_fromat(t2.day,'yyyy-MM') == '2019-11'
group by
	country_name
	

--第二步
select
    country_name,
    if(sum/num <= 15,"cold",if(sum/num >= 25,"hot","warm") )  weather_type
from
    (select
         country_name,
         sum(weather_state) sum,
         count(day) num

     from
         70_Countries t1
             left join
         70_Weather t2
         on
                 t1.country_id = t2.country_id
     where
             date_format(t2.day,'yyyy-MM') = '2019-11'
     group by
         country_name) t3