窗口函数有哪些?
窗口函数可以分为以下 3 类:
- 聚合(Aggregate):AVG(), COUNT(), MIN(), MAX(), SUM()...
sum(col) over() : 分组对col累计求和
count(col) over() : 分组对col累计计数
min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值
- 取值(Value):FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()...
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值,n可选,默认为1,DEFAULT当往上第n行为NULL时候,取默认值,如不指定,则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值,n可选,默认为1,DEFAULT当往下第n行为NULL时候,取默认值,如不指定,则为NULL
- 排序(Ranking):RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()...
ntile(n) : 用于将分组数据按照顺序切分成n片,返回当前切片值。注意:n必须为int类型。
row_number() over() : 排名函数,不会重复,适合于生成主键或者不并列排名
rank() over() : 排名函数,有并列名次,名次不连续。如:1,1,3
dense_rank() over() : 排名函数,有并列名次,名次连续。如:1,1,2
注:Frame 定义并非所有窗口函数都适用,比如 ROW_NUMBER()、RANK()、LEAD() 等。这些函数总是应用于整个分区,而非当前 Frame。
窗口函数使用差异
聚合-窗口
测试数据准备:表名:ptf_over_test_202105_1
table_name:tmp_hotel.ptf_over_test_202105_1
cookieid createtime 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
cookie