简介
在进行分组聚合以后 , 我们还想操作集合以前的数据 使用到窗口函数
相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化
CURRENT ROW:当前行 current row
n PRECEDING:往前n行数据 n preceding
n FOLLOWING:往后n行数据 n following
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点 unbound preceding unbound following
LAG (scalar_expression [,offset] [,default]) : 往前第n行的数据
LEAD (scalar_expression [,offset] [,default]):往后第n行数据
NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返
- 表内的信息
窗口函数
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化
select
*,
count(1)over() total_persion,
sum(money)over() total_money
from
tb_orders
order by name,ctime
;
控制窗口的大小
1 partition by
select
*,
count(1) over(partition by name) total_persion,
sum(money) over(partition by name) total_money
from
tb_orders;
2 UNBOUNDED PRECEDING 和 CURRENT ROW
计算每个人的开始的消费金额到当前时间消费金额的和
select
*,
sum(money)over(partition by name rows between unbounded preceding and current row)total_money
from
tb_orders;
- 注意:当进行排序后 默认从起始行到当前行
select
*,
sum(money)over(partition by name order by ctime)
from
tb_orders;
3 n PRECEDING 和 n FOLLOWING
当前数据的前n行到到当前数据的后n行
select
*,
sum(money)over(partition by name rows between 1 preceding and 1 following)
from
tb_orders;
4 lag和lead
LAG (scalar_expression [,offset] [,default]) : 往前第n行的数据
LEAD (scalar_expression [,offset] [,default]):往后第n行数据
scalar_expression:关键字
offset:偏移量
default:默认值
select
*,
lag(ctime,1,"第一次购买")over(partition by name order by ctime)
from
tb_orders;
select
*,
lag(ctime,2)over(partition by name order by ctime)
from
tb_orders;
select
*,
lead(ctime,1)over(partition by name order by ctime)
from
tb_orders;
5 NTILE(n)
将数据分成n份
select
*,
ntile(5)over(order by ctime)
from
tb_orders;