窗口函数

窗口函数

语法：

<窗口函数> over(partition by 分组字段 order by 排序字段 rows between 开始 and 结束)

1. partition by 字段

   对指定字段进行分组，后续以组为单位，把每个分组单独作为一个窗口进行统计分析操作。

2. order by 字段

   • order by与partition by连用的时候，可以对各个分组内的数据按照指定的字段进行排序，如果没有partition by指定分组字段就对全局数据排序

   • 当为聚合函数时，不仅对窗口内数据排序，还起到窗口内从当前行到之前所有行的聚合作用

   • 当order by排序的维度不存在重复时，使用order by + sum()，可以产生整体累计计数的效果。但当order by排序的维度存在重复时，会在不重复的数据中产生累计效果，而重复的数据中，也会把整体的累计结果分发到每条重复的数据中

     user_id	count	sum_all
     A	4	4
     A	5	9
     B	1	1
     D	2	4
     D	2	4

3. rows between 开始位置 and 结束位置

   n preceding ：从当前行往前n行

   n following ： 从当前行往后n行

   current row：当前行

   unbounded：起点（一般结合preceding，following使用）

   unbounded preceding：表示该窗口最前面的行（起点）

   unbounded following：表示该窗口最后面的行（终点）

   # 表示从起点到当前行的数据
   rows between unbounded preceding and current row
   
   # 表示从当前行到终点的数据
   rows between current row and unbounded following
   
   # 表示从起点到终点的数据
   rows between unbounded preceding and unbounded following
   

窗口函数分类：

1. 聚合函数：avg、sum、count、max、min等

2. 排序窗口函数：

   row_number：排序并产生自增编号，编号不重复且连续 1，2，3，4

   rank：排序并产生自增编号，编号会重复且不连续 1，2，2，4

   dense_rank：排序并产生自增编号，编号会重复且连续 1，2，2，3

3. 偏移窗口函数：lag、lead

   获取分组中往前n行的值

   基础语法：lead(field, n, default_value) over()

   获取分组中往后n行的值

   基础语法：lag(field, n, default_value) over()