什么是窗口函数
- 扫描多个输入行来计算每个输出值,为每行数据生成一行结果
- 可以通过窗口函数来实现复杂的计算和聚合
- 语法 :`Function (arg1,…, arg n) OVER ([PARTITION BY <…>] [ORDER BY <…>] [<window_clause>])
- 功能:排序,聚合,分析
`
窗口函数 - 排序
- ROW_NUMBER()
对所有数值输出不同的序号,序号唯一连续
select * ,row_number() over(partition by channel order by uid)from video
- rank()
用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。
- dense_rank
dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。
- NTILE
NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布.
select name,orderdate,cost,
ntile(3) over() as sample1 , --全局数据切片
ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
ntile(3) over(partition by name order by cost ) --按照name分组,在分组内按照cost升序排列,数据切成3份
from t_window
得到的数据如下:
name orderdate cost sample1 sample2 sample3 sample4
jack 2015-01-01 10 3 1 1 1
jack 2015-02-03 23 3 1 1 1
jack 2015-04-06 42 2 2 2 2
jack 2015-01-05 46 2 2 2 2
jack 2015-01-08 55 2 3 2 3
mart 2015-04-08 62 2 1 2 1
mart 2015-04-09 68 1 2 3 1
mart 2015-04-11 75 1 3 3 2
mart 2015-04-13 94 1 1 3 3
neil 2015-05-10 12 1 2 1 1
neil 2015-06-12 80 1 1 3 2
tony 2015-01-02 15 3 2 1 1
tony 2015-01-04 29 3 3 1 2
tony 2015-01-07 50 2 1 2 3
窗口函数 - 聚合
- count()用来计算查询结果的行数的个数
- sum()相加函数
如果over()中有排序,则按照排序的顺序进行累加 - avg()求平均值
- max()求最大值
- min()求最小值
窗口函数 - 分析
- LAG()函数:返回上行的字段值
select * ,lag(uid,1) over(partition by channel order by uid)from video
可以设置向上的行数
- LEAD()函数::返回下行的字段值
- first_value():取分组内排序后,截止到当前行,第一个值
- last_value取分组内排序后,截止到当前行,最后一个值
windows子句
我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.
我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.
当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.
window子句:
- PRECEDING:往前
- FOLLOWING:往后
- CURRENT ROW:当前行
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点
我们按照name进行分区,按照购物时间进行排序,做cost的累加.
如下我们结合使用window子句进行查询
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from t_window;
得到查询结果如下:
name orderdate cost sample1 sample2 sample3 sample4 sample5 sample6 sample7
jack 2015-01-01 10 661 176 10 10 10 56 176
jack 2015-01-05 46 661 176 56 56 56 111 166
jack 2015-01-08 55 661 176 111 111 101 124 120
jack 2015-02-03 23 661 176 134 134 78 120 65
jack 2015-04-06 42 661 176 176 176 65 65 42
mart 2015-04-08 62 661 299 62 62 62 130 299
mart 2015-04-09 68 661 299 130 130 130 205 237
mart 2015-04-11 75 661 299 205 205 143 237 169
mart 2015-04-13 94 661 299 299 299 169 169 94
neil 2015-05-10 12 661 92 12 12 12 92 92
neil 2015-06-12 80 661 92 92 92 92 92 80
tony 2015-01-02 15 661 94 15 15 15 44 94
tony 2015-01-04 29 661 94 44 44 44 94 79
tony 2015-01-07 50 661 94 94 94 79 79 50
- 窗口函数的搭配
1.支持两类窗口定义:行类型窗口,范围类型窗口
2.不支持的:RANK、NTILE、DENSE_RANK、CUME_DIST
PERCENT_RANK、LEAD、LAG和ROW_NUMBER函数
不支持与窗口子句一起使用