SQL窗口函数的使用

一.窗口函数的介绍

1.窗口函数实现的功能

1）具备分组和排序的功能
2）不改变原有数据库表行数
3）实现对分组后数据取值和聚合计算

2.与group by的区别

group by分组后会改变原表行数，一个类别只保留一行，窗口函数则会保留原有行

二.窗口函数的语法

1.窗口函数的构成

window_function_name(window_name/expression)
over
(
[partition_by]
[order_by]
[frame_definition]
)

窗口的数据集范围由[partition_by]，[order_by]，[frame_definition]共同确定

2.窗口函数的元素

1）窗口函数名window_function_name
静态窗口函数不能使用frame子句，滑动窗口函数使用order by 或frame子句函数区域变为当前限定的数据集
a.静态窗口函数

函数名	参数	描述
rank()	无	间断的组内排序，1，1，3，4
dense_rank()	无	不间断的组内排序，1，1，2，2，3，4
row_number()	无	依次排序，1，2，3，4

执行后结果如下：

b.滑动窗口函数
取值函数

函数名	参数	描述
first_value()	first_value(expr)	分组后取第一行的值
last_value()	last_value(expr)	分组后取最后一行的值
lag()	lag(expr,N,default)	从当前行开始往前取第N行，默认为1，若没有返回default，默认值为null,返回值的类型必须和字段类型一样，字段为int，default才能设置成0
lead()	lead(expr,N)	从当前行开始往后取第N行，默认为1，若没有返回default，默认值为null
nth_value()	nth_value(expr,N)	返回分组内截至当前行的第N行
ntile()	ntile(N)	返回分桶号，返回范围从1到N

c.聚合函数

函数名	参数	描述
sum(expr)	字段	求和，有orderby是累加求和
max(expr)	字段	取最大值，有order by截止当前行最大值
min(expr)	字段	取最小值，有order by截止当前行最小值
count(expr)	字段	统计记录数
avg(expr)	字段	取平均值，有order by截止当前行平均值
percent_rank()	无	累计百分比，（rank-1）/（rows-1）
cum_dist()	无	累计分布值函数

2）分区函数partition_defintion
窗口按照指定字段进行分区，可以选择多个分区字段

3）排序函数order_definition
按照指定字段进行排序，表面是排序功能，实际为累计功能，当和聚合函数（sum、avg、min、max等）连用的时候，order by就是起累计作用。

4）框架函数frame_definition()
框架frame是当前分区的一个子集，frame_unit有两种，分别是ROWS和Range

基于行：
通常使用between frame_start and between frame_end，frame_start和frame_end支持如下关键字
CURRENT ROW 边界是当前行，一般与其他范围关键字配合
UNBOUNDED PRECEDING 分区中的第一行
UNBOUNDED FOLLOWING 分区中的最后一行
N PRECEDING 当前行减去expr的值
N FOLLOWING 当前行加上expr的值
eg:rows between PRECEDING AND 1 FOLLOWING 当前行、前一行、后一行共三行
eg:rows UNBOUNDED FOLLOWING 当前行到最后一行
eg:rows between UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 当前分区所有行，等同于不写
基于范围：
有些范围是无法用行来表示的，比如一周前的订单数，可以使用范围来表示窗口：INTERVAL 7DAY PRECEDING（hive和impla无法实现）
窗口固定的函数：rank()、dense_rank()、row_number()、lag(expr,N,default)、lead(expr,N,default)、Ntile()等

row与range的区别
rows：指定以行号来确定frame范围，是物理意义上的行
ranges：根据range去加减上下界，是逻辑意义上的行

SELECT  vin
        ,acquisition_time1
        ,data_avn_speed
        ,sum(cast(data_avn_speed as FLOAT)) over (PARTITION BY vin ORDER BY acquisition_time1 ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as row1
        ,sum(cast(data_avn_speed as float)) over (PARTITION BY vin ORDER BY acquisition_time1 RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as range1
      FROM

发现出现重复行，range会一次全部求和，row会一行一行计算求和

3.窗口函数的使用

1）窗口函数元素搭配

2）静态窗口函数使用方式
a.静态窗口函数由于是排序，必须使用order by，设定排序条件，否则会报错

b.partition by、order by和groupby一样，可以增加多个分组字段
c.静态窗口函数不能使用frame子句

SELECT      vin
            ,acquisition_time1
            ,rank1
            ,rank() over (PARTITION BY vin,rank1 ORDER BY acquisition_time1)
            ,dense_rank() over (PARTITION BY vin,rank1 ORDER BY acquisition_time1)
            ,row_number() over (PARTITION BY vin,rank1 ORDER BY acquisition_time1)
    

3）滑动窗口函数-取值函数的使用方式
问题：截止到当前行如何受order by和框架函数影响？
first_value()（建议使用）
a.partiton by、order by、frame_definition可以省略

SELECT      vin
            ,acquisition_time1
            ,rank1
            ,data_avn_longitude
            ,first_value(data_avn_longitude) over()
            ,first_value(data_avn_longitude) over(ORDER BY acquisition_time1)
            ,first_value(data_avn_longitude) over(PARTITION BY vin ORDER BY acquisition_time1)
      FROM

first_value(data_avn_longitude) over(PARTITION BY vin ORDER BY acquisition_time1 ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING)--加入框架函数，取前中后三行第一个值

last_value()
a.partiton by、order by、frame_definition可以省略
b.增加order by ,发现取的值不是分组中最后一个的值，Order的值变化了，基本上也就是当前行的值了，如果没有变化就取相同Order系列中的最后一项。
c.建议使用first_value()+desc方式实现

SELECT      vin
            ,acquisition_time1
            ,rank1
            ,data_avn_longitude
            ,last_value(data_avn_longitude) over()
            ,last_value(data_avn_longitude) over(PARTITION BY vin ORDER BY acquisition_time1)
      FROM

增加order by之后发现取的值不是分组中最后一个的值，因为默认是取到分组（可选）第一项到当前行的，如果要取整个组的最后一项可以增加RANGE参数来限定范围：

SELECT LAST_VALUE(ID) OVER (PARTITION BY G ORDER BY seq RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
FROM t

lag(expr,N,default) ，lead(expr,N,default),nth_value(expr,N)(hive不支持)，ntile(N)

SELECT      vin
            ,acquisition_time1
            ,rank1
            ,data_avn_speed
            ,first_value(data_avn_speed) over(PARTITION BY vin ORDER BY acquisition_time1) as first1
            ,lag(data_avn_speed,2) over(PARTITION BY vin ORDER BY acquisition_time1) as lag1
            ,lead(data_avn_speed,2) over(PARTITION BY vin ORDER BY acquisition_time1) as lead1
            ,ntile(4) over(PARTITION BY vin ORDER BY acquisition_time1) as nth
      FROM

ntile()会将数据集等分成N组，但是不支持frame_definition子句，必须包含order by子句
4）滑动窗口函数-取值函数的使用方式
max()、sum()、min()
结果取决于数据集范围，只有partition by，就是分区范围，增加order by 就是unbounded preceding and current row，增加框架函数就是框架函数的范围
count()
对数据集范围进行计数

SELECT      vin
            ,acquisition_time1
            ,rank1
            ,data_avn_speed
            ,count(data_avn_speed) over (partition by vin order by acquisition_time1 rows between 2 preceding and 2 following)
            ,count(data_avn_speed) over(PARTITION BY vin)
            ,count(data_avn_speed) over (PARTITION BY vin ORDER BY acquisition_time1)
      FROM

a.使用frame_definition,对数据集范围计数
b.只使用partition by 就是对分组计总数
c.使用partition by和order by 就是unbounded preceding and current row范围计数，但是遇到重复行，会把重复行同时计算（逻辑上计数）

percent_rank()
待定
cume_dist()
待定
5）窗口数据集
如果窗口数据集多次调用，可以简写

-- 求每个人成绩的均值、个数、总分、最小值和最大值
SELECT *,
    avg(成绩)   over w AS score_avg,
       count(成绩) over w AS score_count,
    sum(成绩)   over w AS score_sum,
       min(成绩)   over w AS score_min,
    max(成绩)   over w AS score_max
FROM score
WINDOW w AS (partition by 姓名 order by 学号)

三.窗口函数的应用

待更新

参考链接
MySQL 8.0窗口函数
最全的SQL窗口函数介绍及使用
SQL篇-常用窗口函数
MySQL累计求和问题及窗口函数orderby的原理
HIVE SQL 聚合函数与 rows between / range between详解
SQL LAST_VALUE使用问题记录