MySQL窗口函数

参考文章：
1、https://baijiahao.baidu.com/s?id=1728966619393719484&wfr=spider&for=pc
2、https://blog.csdn.net/nmsLLCSDN/article/details/123287490

1 使用前提：MySQL8.0

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将分组的结果置于每一条数据记录中。

OVER 关键字指定窗口的范围;

如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。

PARTITION BY 子句: 指定窗口函数按照哪些字段进行分组, 分组后, 窗口函数可以在每个分组中分别执行;
ORDER BY 子句: 指定窗口函数按照哪些字段进行排序, 执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号;
FRAME 子句: 为分区中的某个子集定义规则, 可以用来作为滑动窗口使用;

2 语法

1. 函数 OVER (PARTITION BY 分组字段 ORDER BY 排序字段 ASC|DESC)

   SELECT 
   	*,
   	ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num
   FROM goods;
   

2. 函数 OVER w_name ... WINDOW w_name as (PARTITION BY 分组字段 ORDER BY 排序字段 ASC|DESC)

   SELECT 
   	*,
   	ROW_NUMBER() OVER w AS row_num
   FROM goods 
   WINDOW w as (PARTITION BY category ORDER BY price DESC);
   
   SELECT
   NTILE(4) OVER w AS nf,
   order_id, username, cost
   FROM tb_customer_shopping tcs
   WHERE username IN ('Jack','King')
   WINDOW w AS (PARTITION BY username ORDER BY cost);
   

3 分类

1 排序

• row_number()

  会多出一个序号字段，每组单独进行排序，这个字段就标明 行在分组中的序号

• rank()

  与row_number()类似，但有所区别

  若组中存在多行 排序字段值相同，则这几行序号相同，但这几行后 的序号不连续（两个字段，一个排序，一个序号）

  如 1，1，3，4

• dense_rank()

  与row_number()类似，但有所区别

  若组中存在多行 排序字段值相同，则这几行序号相同，但这几行后 的序号连续（两个字段，一个排序，一个序号）

  如 1，1，2，3

2 比例

• percent_rank()

  每行按照公式(rank-1)/ (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口(当前组)的总行数

• cume_dist()

  降序时的公式：所在组中排序字段值大于等于当前行的行数 / 分组内总行数

  升序时的公式：所在组中排序字段值小于等于当前行的行数 / 分组内总行数

3 跨行

分组中获取当前行前/后的第N行的expr值，若目标expr值不存在（当前分组越界，如取第一行的前一行的name属性值），则取默认值

• LAG(expr，N，default)
• LEAD(expr，N，default)

4 头尾

• first_value(expr)

  分组中第一行到当前行的 第一行的 expr值

• last_value(expr)

  分组中第一行到当前行的 最后一行的 expr值，即总获取当前行。

  这样就无法获得预期值，我们想要的是分组中最后一行的expr值，所以需要在 order by column后加上 ROWS BETWEEN unbounded preceding AND unbounded following

  select last_value(price) over (PARTITION BY category ORDER BY price DESC ROWS BETWEEN unbounded preceding AND unbounded following)  AS l
  from goods
  

5 其他

• NTH_VALUE(expr，n)

  返回分组中 排名第n的expr值

• NTILE(n)

  用途：将分区中的有序数据分为n个等级，记录等级数

6 聚合

SELECT 
	category, NAME,price,
	SUM(price) over w AS `sum`,
	COUNT(price) over w AS `count`,
	MIN(price) over w AS `min`,
	MAX(price) over w AS `max`,
	AVG(price) over w AS `avg`
FROM goods window w AS (PARTITION BY category ORDER BY price DESC);