原以为仅仅 greenplum 含有分窗函数，查了下 gg, 发现 mysql 也含有 window 的概念，下面来介绍下 mysql 的分窗逻辑。

窗口(window) 介绍

window 是在满足某种条件的记录集合上执行一个特殊函数。记录集合即为窗口，特殊函数就在该窗口上执行函数。

window function 剖析

select func_name over (window_definition) from (....)

注 名词解释：

• window_definition 定义需要计算的记录的集合
• func_name 指定对于集合要使用的函数

例如：

# 创建销售记录表
CREATE TABLE sales(
    -- 员工信息
    sales_employee VARCHAR(50) NOT NULL,
    -- 年度信息
    fiscal_year INT NOT NULL,
    -- 销售金额
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);

# 初始化插入数据
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

avg 函数

按 window 执行指定字段上的数据求平均值，如下图所示：

按 window 求平均

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    AVG(sale) OVER (PARTITION BY fiscal_year)  avg_sales
FROM
    sales;

sum 函数

按 window 执行指定字段上的数据求和

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year)  total_sales
FROM
    sales;

window 分类

通常存在两个基准进行分类：窗口大小是否固定 、按照函数功能分类

1. 按窗口大小区分

• 静态窗口函数： 窗口大小固定的函数
• 动态窗口函数：不同的记录对应着不同的窗口

2. 按功能划分

类型	函数列表
序号函数	row_number(); rank(); dense_rank()
分布函数	percent_rank(); cume_dist()
前后函数	lag(expr, n); lead(expr, n)
头尾函数	first(expr); last_value(expr)
其他函数	nth_value(expr, n); ntile(n);

window 定义

over 为定义窗口集合的关键字，格式如下：

<函数名>(expression) OVER ( 
   [partition_defintion]
   [order_definition]
   [frame_definition]
)

定义集合的方式有如下四种：

1. 无任何语句     表示该窗口包含所有满足 where 语句的所有数据，即基于所有数据进行计算, 比如： 
      格式:    `over()`
2. `partition` 语句     窗口按哪些字段进行分组
      格式:    `PARTITION BY <expression>[{,<expression>...}]`
3. `order by` 语句     按照哪些字段进行排序
      格式:    `ORDER BY <expression> [ASC|DESC], [{,<expression>...}]`
4. `frame` 语句     frame 是当前分区的一个子集，子句用来定义子集的规则通常用来作为滑动窗口使用
      格式:    `frame_unit {<frame_start>|<frame_between>}`
      比如： `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
      比如： `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

注 对于 frame 语句的区间来说，如下图所示：

区间分布图

滑动窗口

1. 基于行

比如，基于员工按时间排序计算计算每年及前后一年销售额的平均值

select
    s.sales_employee,
    avg(s.sale) over (
      partition by s.sales_employee
      ORDER by s.fiscal_year 
      -- 基于行进行分窗，格式如下：
      -- 格式：ROWS BETWEEN <expr> AND <expr>
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as 2 avg
from
    sales s;

注： CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
注： UNBOUNDED PRECEDING 边界是分区中的第一行
注： UNBOUNDED FOLLOWING 边界是分区中的最后一行
注： <expr> PRECEDING 边界是当前行减去expr的值
注： <expr> FOLLOWING 边界是当前行加上expr的值

比如基于前两年数据进行计算平均值：

select
    s.sales_employee,
    avg(s.sale) over (
      partition by s.sales_employee
      ORDER by s.fiscal_year 
      -- 基于行进行分窗，格式如下：
      -- 格式：ROWS BETWEEN <expr> AND <expr>
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as 3yavg
from
    sales s;

1. 基于范围

有一些范围不是可以直接使用行数表示，这种情况就要用范围，比如窗口范围是一周前的订单开始，截止到当前行； INTERVAL 7 DAY PRECEDING

select
    s.sales_employee,
    avg(s.sale) over (
      partition by s.sales_employee
      ORDER by s.fiscal_year 
      -- 使用 RANGE 来计算与当前时间间隔2两年到当前时间的值
      RANGE BETWEEN INTERVAL 2 YEAR PRECEDING AND CURRENT ROW
    ) as 3yavg
from
    sales s;

注 ROWS 表示在行数开始及结束位置，前后的起始位置是基于当前 row 的位置
注 RANGE 表示基于当前row 所在值区间，区间的起始值是基于当前 row 的值

常见 window 函数

1. rank 函数

按分区进行排行的功能，格式如下：

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

比如, 计算每年销售排行榜的榜单信息：

SELECT
    sales_employee,
    fiscal_year,
    sale,
    RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;

2. lag 函数

从窗尾向前进行取值，比如获取上一年的销售额度：

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    lag(sale, 1) OVER (PARTITION BY s.sales_employee ORDER BY s.fiscal_year) last_sale
FROM
    sales s;