窗口函数over

https://www.cnblogs.com/erlou96/p/13590358.html#_label0_0

开窗函数

over()窗口函数的语法结构

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

• over()
  默认此时每一行的窗口都是所有的行
  select *,count(1) over() from d;

  
  image.png

• over(order by orderdate)
  orderdate=1的窗口只有一行，orderdate=2的窗口包括orderdate=2017-01-01,orderdate=2017-01-02
  select *,count(1) over(order by b) from d;

  
  image.png

• over(partition by name)每一行根据 name来区分窗口
  select *,count(1) over(partition by a) from d;

  
  image.png

• over(partition by name order by id) 每一行根据 name来区分窗口,再根据order by 取具体的范围

  
  image.png

rows between 开始位置 and 结束位置

FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

常与over()一起使用的分析函数

• 聚合类
  avg()、sum()、max()、min()

• 排名类
  row_number()按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）
  rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、2、3、3、3、6）
  dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、3、3、3、4）

• 其他类
  lag(列名,往前的行数,[行数为null时的默认值，不指定为null])，可以计算用户上次购买时间，或者用户下次购买时间。或者上次登录时间和下次登录时间
  lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
  ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号