目录
其他窗口函数可翻看:
窗口函数之(sum、avg、max、min)
窗口函数之(row_number, rank, dense_rank)
1.样例数据
id crtime pv
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
2.ntile(n)
ntile(n)用于将分组数据进行切片,n代表切成多少片。相当于把数据分成几等份,如果不能均匀等份,则多出来的从第一片开始加。
比如多出来1份,则加给第一片。
比如多出来2份,则分别加给第一片和第二片。
2.1实例
select id,crtime,pv,
ntile(2) over(partition by id order by crtime) n2, --分2片
ntile(3) over(partition by id order by crtime) n3, --分3片
ntile(4) over(partition by id order by crtime) n4, --分4片
ntile(5) over(partition by id order by crtime) n5 --分5片
from nt;
->
id crtime pv n2 n3 n4 n5
cookie1 2015-04-10 1 1 1 1 1
cookie1 2015-04-11 5 1 1 1 1
cookie1 2015-04-12 7 1 1 2 2
cookie1 2015-04-13 3 1 2 2 2
cookie1 2015-04-14 2 2 2 3 3
cookie1 2015-04-15 4 2 3 3 4
cookie