溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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;
求每一年最大氣溫的那一天 + 溫度
臨時數據存放在視圖中
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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
