1.数据(c.txt)
2014010216
2014010410
2012010609
2012010812
2012011023
2001010212
2001010411
2013010619
2013010812
2013011023
2008010216
2008010414
2007010619
2007010812
2007011023
2010010216
2010010410
2015010649
2015010812
2015011023
2.要求
求每一年最大气温的那一天、温度
数据举例:2010012325表示在2010年01月23日的气温为25度
3.语句
1.create external table if not exists weather(data bigint);
2.load data local inpath "/home/hadoop/c.txt" into table weather;
3.create external 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;
4.create external 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);
5.select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;