【Hive】笔试题 03 （求每一年最大气温的那一天 + 温度）

1、说明

具体数据

2014010216
2014010410
2012010609
2012010812
2012011023
2001010212
2001010411
2013010619
2013010812
2013011023
2008010216
2008010414
2007010619
2007010812
2007011023
2010010216
2010010410
2015010649
2015010812
2015011023

数据解释

2010012325表示在2010年01月23日的气温为25度

2、 需求

比如：2010012325表示在2010年01月23日的气温为25度。现在要求使用hive，计算每一年出现过的最大气温的日期+温度。
要计算出每一年的最大气温。我用
select substr(data,1,4),max(substr(data,9,2)) from table2 group by substr(data,1,4);
出来的是 年份 + 温度 这两列数据例如 2015 99

但是如果我是想select 的是：具体每一年最大气温的那一天 + 温度 。例如 20150109 99
请问该怎么执行hive语句。
group by 只需要substr(data,1,4)，
但是select substr(data,1,8)，又不在group by 的范围内。
是我陷入了思维死角。一直想不出所以然。求大神指点一下。
在select 如果所需要的。不在group by的条件里。这种情况如何去分析？

3、解析

（1）创建一个临时表tmp_weather，将数据切分

create table tmp_weather as 
select substr(data,1,4) years,substr(data,5,2) months,substr(data,7,2) days,substr(data,9,2) temp from weather;

select * from tmp_weather;

+--------------------+---------------------+-------------------+-------------------+--+
| tmp_weather.years  | tmp_weather.months  | tmp_weather.days  | tmp_weather.temp  |
+--------------------+---------------------+-------------------+-------------------+--+
| 2014               | 01                  | 02                | 16                |
| 2014               | 01                  | 04                | 10                |
| 2012               | 01                  | 06                | 09                |
| 2012               | 01                  | 08                | 12                |
| 2012               | 01                  | 10                | 23                |
| 2001               | 01                  | 02                | 12                |
| 2001               | 01                  | 04                | 11                |
| 2013               | 01                  | 06                | 19                |
| 2013               | 01                  | 08                | 12                |
| 2013               | 01                  | 10                | 23                |
| 2008               | 01                  | 02                | 16                |
| 2008               | 01                  | 04                | 14                |
| 2007               | 01                  | 06                | 19                |
| 2007               | 01                  | 08                | 12                |
| 2007               | 01                  | 10                | 23                |
| 2010               | 01                  | 02                | 16                |
| 2010               | 01                  | 04                | 10                |
| 2015               | 01                  | 06                | 49                |
| 2015               | 01                  | 08                | 12                |
| 2015               | 01                  | 10                | 23                |
+--------------------+---------------------+-------------------+-------------------+--+

（2）创建一个临时表tmp_year_weather

create table tmp_year_weather as 
select substr(data,1,4) years,max(substr(data,9,2)) max_temp from weather group by substr(data,1,4);

select * from tmp_year_weather;

+-------------------------+----------------------------+--+
| tmp_year_weather.years  | tmp_year_weather.max_temp  |
+-------------------------+----------------------------+--+
| 2001                    | 12                         |
| 2007                    | 23                         |
| 2008                    | 16                         |
| 2010                    | 16                         |
| 2012                    | 23                         |
| 2013                    | 23                         |
| 2014                    | 16                         |
| 2015                    | 49                         |
+-------------------------+----------------------------+--+

（3）将2个临时表进行连接查询

select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;

+----------+-------------+----------+-----------+---------+---------+--+
| a.years  | a.max_temp  | b.years  | b.months  | b.days  | b.temp  |
+----------+-------------+----------+-----------+---------+---------+--+
| 2014     | 16          | 2014     | 01        | 02      | 16      |
| 2012     | 23          | 2012     | 01        | 10      | 23      |
| 2001     | 12          | 2001     | 01        | 02      | 12      |
| 2013     | 23          | 2013     | 01        | 10      | 23      |
| 2008     | 16          | 2008     | 01        | 02      | 16      |
| 2007     | 23          | 2007     | 01        | 10      | 23      |
| 2010     | 16          | 2010     | 01        | 02      | 16      |
| 2015     | 49          | 2015     | 01        | 06      | 49      |
+----------+-------------+----------+-----------+---------+---------+--+

4、其他方法

使用了窗口函数，可以看出还是非常迅速的！

select data from (
select data, rank() over (partition by substr(data, 0,4) order by substr(data, 9,10) desc) as rn from weather order by data) as temp
where temp.rn = 1;

+-------------+--+
|    data     |
+-------------+--+
| 2001010212  |
| 2007011023  |
| 2008010216  |
| 2010010216  |
| 2012011023  |
| 2013011023  |
| 2014010216  |
| 2015010649  |
+-------------+--+

也看到了其他朋友的方法，嗯，操作大同小异，这里给大家看下：

存原数据
create table tempertrue(
data string comment ‘row data’
);
视图切分日期与温度：
create view temp as select substr(data,1,8) as time,substr(data,9) as tep from tempertrue;
分区排序再筛选：
select time,tep from (select time,tep,rank() over(partition by substr(time,1,4) order by tep desc) as num from temp) as t1 where t1.num=1;
同理，num=2可取第二高温度