|
目錄 正文 概述
本文中介紹前幾個序列函數(shù)，NTILE,ROW_NUMBER,RANK,DENSE_RANK，下面會一一解釋各自的用途。
注意： 序列函數(shù)不支持WINDOW子句。（ROWS BETWEEN）
數(shù)據(jù)準備
數(shù)據(jù)格式
cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7
創(chuàng)建表
use cookie;
drop table if exists cookie2;
create table cookie2(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/home/hadoop/cookie2.txt" into table cookie2;
select * from cookie2;
玩一玩NTILE
說明
NTILE(n)，用于將分組數(shù)據(jù)按照順序切分成n片，返回當前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均勻，默認增加第一個切片的分布
查詢語句
select
cookieid,
createtime,
pv,
ntile(2) over (partition by cookieid order by createtime) as rn1, --分組內將數(shù)據(jù)分成2片
ntile(3) over (partition by cookieid order by createtime) as rn2, --分組內將數(shù)據(jù)分成2片
ntile(4) over (order by createtime) as rn3 --將所有數(shù)據(jù)分成4片
from cookie.cookie2
order by cookieid,createtime;
查詢結果
比如，統(tǒng)計一個cookie，pv數(shù)最多的前1/3的天
查詢語句
select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn
from cookie.cookie2;
查詢結果
--rn = 1 的記錄，就是我們想要的結果
玩一玩ROW_NUMBER
說明
ROW_NUMBER() –從1開始，按照順序，生成分組內記錄的序列
–比如，按照pv降序排列，生成分組內每天的pv名次
ROW_NUMBER() 的應用場景非常多，再比如，獲取分組內排序第一的記錄;獲取一個session中的第一條refer等。
分組排序
select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;
查詢結果
-- 所以如果需要取每一組的前3名，只需要rn<=3即可，適合TopN
玩一玩RANK 和 DENSE_RANK
—RANK() 生成數(shù)據(jù)項在分組中的排名，排名相等會在名次中留下空位
—DENSE_RANK() 生成數(shù)據(jù)項在分組中的排名，排名相等會在名次中不會留下空位
查詢語句
select
cookieid,
createtime,
pv,
rank() over (partition by cookieid order by pv desc) as rn1,
dense_rank() over (partition by cookieid order by pv desc) as rn2,
row_number() over (partition by cookieid order by pv desc) as rn3
from cookie.cookie2
where cookieid='cookie1';
查詢結果
ROW_NUMBER、RANK和DENSE_RANK的區(qū)別
row_number： 按順序編號，不留空位
rank： 按順序編號，相同的值編相同號，留空位
dense_rank： 按順序編號，相同的值編相同的號，不留空位
|
