mysql_窗口函数

作者：YXN-sql 阅读量：211 发布日期：2021-05-08

标准语法：

over([partition by 字段名] [order by 字段名 asc|desc])

语法讲解：

over()中两个子句为可选项
partition by指定分区依据
order by指定排序依据

#排序（专用）窗口函数
rank()over()是并列排序，会跳过重复序号   如：99，99，90，89 排序后为：1，1，3，4
 
dense_rank()over()是并列排序，不会跳过重复序号   如：99，99，90，89 排序后为：1，1，2，3
 
row_number()over()是顺序排序，不跳过任何一个序号，就是行号   如：99，99，90，89 排序后为：1，2，3，4

#偏移分析函数
lag(字段名,偏移量[,默认值])over()
访问相同结果集中 先前行 的数据；
 
lead(字段名,偏移量[,默认值])over()
访问相同结果集的 后续行 的数据

#MySQL支持的窗口函数分为如下几类：(详解见下面功能函数)
  序号函数：row_number()、rank()、dense_rank()
  分布函数：percent_rank()、cume_dist()
  前后函数：lag()、lead()
  头尾函数：first_value()、last_value()
  其他函数：nth_value()、ntile()
  聚合函数：sum(),avg(),min(),max(),count()

窗口函数功能函数

名称	描述
cume_dist()	计算一组值中一个值的累积分布
lag(expr [,N,default])	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回NULL
lead()	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回NULL
first_value()	返回当前窗口的第一个值
last_value()	返回当前窗口的最后一个值
nth_value()	从窗口框架的第N行返回参数的值
ntile()	将每个窗口分区的行分配到指定数量的排名组中(按指定条数分配组序号)
percent_rank()	计算分区或结果集中行的百分数等级
rank	当两行或更多行具有相同的等级时，等级值序列中存在间隙1,1,3,4
dense_rank()	如果两行或更多行具有相同的排名，则排名值序列中将没有间隙1,1,2,3
row_number()	为分区中的每一行分配一个顺序整数1,2,3,4

窗口函数的相关语法是：

[WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...]

window_function_name(window_name/expression)
    OVER (
        [partiton_defintion]
        [order_definition]
        [frame_definition]
    ) as 别名

window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读.
partition_defintion：窗口按照指定字段进行分区，两个分区由分区边界分隔，窗口功能在分区内执行，并在跨越分区边界时重新初始化。
order_definition：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。
frame子句：frame是当前分区的一个子集，在分区里面再进一步细分窗口，子句用来定义子集的规则，通常用来作为滑动窗口使用。

具体语法如下:

frame_unit {<frame_start>|<frame_between>}

frame_unit有两种，分别是ROWS和RANGE，由ROWS定义的frame是由开始和结束位置的行确定的，由RANGE定义的frame由在某个值区间的行确定。

滑动窗口

通常使用between frame_start and frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

current row # 边界是当前行，一般和其他范围关键字一起使用
unbounded preceding # 边界是分区中的第一行
unbounded following # 边界是分区中的最后一行
expr preceding # 当前行之前的expr(数字或表达式)行
expr following # 当前行之后的expr(数字或表达式)行
# rows是基于行数，range是基于值的大小

# 比如，下面都是合法的范围：
rows between 1 preceding and 1 following # 窗口范围是当前行、前一行、后一行一共三行记录。
rows unbounded following # 窗口范围是当前行到分区中的最后一行
rows between unbounded preceding and unbounded following # 窗口范围是当前分区中所有行，等同于不写。

案例

select name,orderdate,cost,
 
sum(cost) over() as sample1,-- 所有行相加
sum(cost) over(partition by name) as sample2,-- 按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,-- 按 name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between
unbounded preceding and current row ) as sample4 ,-- 和 sample3 一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1
preceding and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1
preceding AND 1 following ) as sample6,-- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current
row and unbounded following ) as sample7 -- 当前行及后面所有行

from business;

YXN-sql

2021-05-08