8.0基本函数

MySQL 8.0 窗口函数

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_val() / last_val()
• 其他函数：nth_value() / nfile()

一、排名函数:序号函数：row_number() / rank() / dense_rank()

select *,函数() over(partition by 字段1 order by 字段2 desc or asc) as rank from table

1、row_number() :连续排名，顺序排序——>1、2、3

• 注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

2、RANK():并列跳跃排名，并列排序，跳过重复序号——1、1、3

3、DENSE_RANK():并列连续排序，并列排序，不跳过重复序号——1、1、2

二、分布函数：percent_rank() / cume_dist()

4、cume_dist():计算某个值在一组有序的数据中累计的分布;小于等于当前行值的行数/总行数

	计算结果为相对位置/总行数,返回值为(0,1]
	注意：对于重复值，计算的时候，取重复值的最后一行的位置

5、percent_rank():和cume_dist 的不同点在于计算分布结果的方法计算方法为（相对位置-1）/（总行数-1）

	注意：对于重复值，计算的时候，取重复值的第一行的位

三、头尾函数：first_val() / last_val()

6、FIRST_VALUE():按照某种方式排序，求第一个值，这里是按class分组，score排序取第一个id值

7、last_VALUE():按照某种方式排序，求最后一个值，这里是按class分组，class排序取最后一个id值

四、前后函数：lag() / lead()

8、LAG() :按某种方式排序求上一行的值

9、lead () 按某种方式排序求下一行的值

五、其他函数：nth_value() / nfile()

10、NTH_VALUE() OVER ()按某种方式排序求某一行的值

11、ntile():将数据按照某些排序分成N组

12、CTE 公用表达式，非递归的CTE，查询最新重复注册的用户

13、CTE 公用表达式，递归的CTE

六、聚合函数

14、sum():求和

例子：

执行后得到的结果如下：

• 这里，sum()函数充当了窗口函数，得到了根据fiscal_year计算出的sale的总和total_sales列，但是又不像它作为聚合函数使用时一样，这里的结果保留了每一行的信息。
• 原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP　BY的聚合过程已经完成了，所以不会再产生数据聚合

窗口函数的语法：

partition_defintion:分区

PARTITION BY < expression>[{,< expression>…}]

• 它会根据单个或者多个表达式的计算结果来分区（列名也是一种表达式，它的结果就是列名本身）

frame_definition：帧，作用是在分区里面再进一步细分窗口

frame_unit {< frame_start>|< frame_between>}

• frame_unit有两种，分别是ROWS和RANGE，由ROWS定义的frame是由开始和结束位置的行确定的，由RANGE定义的frame由在某个值区间的行确定。
• 如果只指定了frame的开始位置，那么结束位置就默认为当前行。frame_start有三种：
  • UNBOUNDED PRECEDING: 区间的第一行
  • N PRECEDING: 当前行之前的N行，N可以是数字，也可以是一个能计算出数字的表达式
  • CURRENT ROW: 当前行
• frame_between的可以取的值如下：
  • frame_start：如前面所列
  • UNBOUNDED FOLLOWING：区间的最后一行
  • N FOLLOWING：当前行之后的N行，N可以是数字，也可以是一个能计算出数字的表达式
    如果没显式指定frame的话，MySQL会认为frame是

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

例子：

执行结果如下：