Shulou(Shulou.com)06/03 Report--

2014010216

2014010410

2012010609

2012010812

2012011023

2001010212

2001010411

2013010619

2013010812

2013011023

2008010216

2008010414

2007010619

2007010812

2007011023

2010010216

2010010410

2015010649

2015010812

2015011023

Create table t_wendu_line (

Line string

);

Load data local inpath'/ root/wendu.txt' into table t_wendu_line

Find the day of the maximum temperature of each year + temperature.

Temporary data is stored in the view

Create view v_temperature_data

As

Select substr (line,1,4) as year

From_unixtime (unix_timestamp (substr (line,1,8), 'yyyymmdd'),' yyyy-mm-dd') tdate

, cast (substr (line,9,2) as smallint) as temperature_num

From t_wendu_line

Select year,tdate,temperature_num

From (

Select year,tdate,temperature_num

Row_number () over (partition by year order by temperature_num desc) as rn

, rank () over (partition by year order by temperature_num desc) as rn1

, dense_rank () over (partition by year order by temperature_num desc) as rn2

From v_temperature_data

) tmp

Where tmp.rn = 1

2001 2001-01-02 12

2007 2007-01-10 23

2008 2008-01-02 16

2010 2010-01-02 16

2012 2012-01-10 23

2013 2013-01-10 23

2014 2014-01-02 16

2015 2015-01-06 49

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.