SQL之计算每一年出现过的最大气温值【TOPN问题】--HiveSQL面试题34

目录

0 需求分析

1 数据准备

2 数据分析

3 小结

---

0 需求分析

需求：现在要求使用hive，计算每一年出现过的最大气温的日期+温度。

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

数据如下

temp
2021010114
2021010216
2021010317
2021010410
2021010506
2021010609
2021010732
2021010812
2021010919

1 数据准备

(1) 数据

vim temp.txt

2021010114
2021010216
2021010317
2021010410
2021010506
2021010609
2021010732
2021010812
2021010919

(2)创建表

drop table if exists temp
CREATE TABLE dan_test.temp ( 
        temp string
        )
ROW format delimited FIELDS TERMINATED BY "\t";

(3)  导入数据

load data local inpath "/home/centos/dan_test/temp.txt" into table temp;

(4)查询数据

hive> select * from temp;
OK
2021010114
2021010216
2021010317
2021010410
2021010506
2021010609
2021010732
2021010812
2021010919
Time taken: 1.248 seconds, Fetched: 9 row(s)

2 数据分析

目标：计算每一年出现过的最大气温的日期+温度。

由于本题只有一个temp字段，该字段由时间和温度组成，所以先需要截串处理。利用substr()函数进行截取。截取日期，substr(temp,1,8),截取温度，substr(temp,-2,2),截取年substr(temp,1,4),截取月substr(temp,5,2).于是基本的底表SQL如下：

select substr(temp,1,4) as dyear
      ,substr(temp,5,2) as dmonth
      ,substr(temp,1,8) as dt
      ,substr(temp,-2,2) as ttemp
from temp

计算结果如下：

OK
2021	01	20210101	14
2021	01	20210102	16
2021	01	20210103	17
2021	01	20210104	10
2021	01	20210105	06
2021	01	20210106	09
2021	01	20210107	32
2021	01	20210108	12
2021	01	20210109	19
Time taken: 0.208 seconds, Fetched: 9 row(s)

由于要求每一年中最大的日期和温度，如果只是求每一年中最大的温度，我们就可以按年分组，求max(ttemp),但要求中出现可其他字段，且不在分组中，因此对于此类问题我们最好是用开窗函数。对于求topN,或最大值问题，我们往往利用排名函数，如row_number()函数。若题目中出现每一，并且求最值，或top N时候，除去分组字段含有其他字段的时候，往往使用该方法。

利用row_number()函数排序SQL如下：

select *
     ,row_number() over(partition by dyear order by ttemp desc)
from(
    select substr(temp,1,4) as dyear
          ,substr(temp,5,2) as dmonth
          ,substr(temp,1,8) as dt
          ,substr(temp,-2,2) as ttemp
    from temp
) t

 计算结果如下：

OK
2021	01	20210107	32	1
2021	01	20210109	19	2
2021	01	20210103	17	3
2021	01	20210102	16	4
2021	01	20210101	14	5
2021	01	20210108	12	6
2021	01	20210104	10	7
2021	01	20210106	09	8
2021	01	20210105	06	9
Time taken: 17.996 seconds, Fetched: 9 row(s)

因此我们根据以上结果过滤出每年中最大的温度值及对应的日期。SQL如下：

select dt
      ,ttemp
from(
    select *
         ,row_number() over(partition by dyear order by ttemp desc) as rn 
    from(
        select substr(temp,1,4) as dyear
              ,substr(temp,5,2) as dmonth
              ,substr(temp,1,8) as dt
              ,substr(temp,-2,2) as ttemp
        from temp
    ) t
) m
where rn=1

计算结果如下：

--------------------------------------------------------------------------------
OK
20210107	32
Time taken: 9.049 seconds, Fetched: 1 row(s)

3 小结

本题相对比较容易，是求TOPN问题的典型案例，利用排名函数可以轻松解决。