MySQL8.0新增SQL语法对窗口函数和CTE的支持

MySQL8.0新增SQL语法对窗⼝函数和CTE的⽀持
尝试了⼀下MySQL 8.0的部分新特性。

如果⽤过MSSQL或者是Oracle中的窗⼝函数（Oracle中叫分析函数），
然后再使⽤MySQL 8.0之前的时候，就知道需要在使⽤窗⼝函数处理逻辑的痛苦了，虽然纯SQL也能实现类似于窗⼝函数的功能，但是这种SQL在可读性和以及使⽤⽅式上⼤打折扣，看起来写起了都⽐较难受。

在MSSQL和Oracle以及PostgreSQL都已经完整⽀持窗⼝函数的情况下，MySQL 8.0中也加⼊了窗⼝函数的功能，这⼀点实实在在⽅便了sql的编码，可以说是MySQL8.0的亮点之⼀。

对于窗⼝函数，⽐如row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()等等，在MSSQL和Oracle以及PostgreSQL，使⽤的语法和表达的逻辑，基本上完全⼀致。

这⼀点，⼏个数据库⼚商做的还是⽐较统⼀的，如果熟悉任何⼀种关系数据中的窗⼝函数（分析函数），在MySQL 8.0之后就放⼼的⽤吧。

通过⼀个case来体验⼀下窗⼝函数的⽅便性，熟悉MSSQL或者Oracle或者PostgreSQL的⽼司机就不⽤看了。

测试case，简单模拟⼀个订单表，字段分别是订单号，⽤户编号，⾦额，创建时间
drop table if exists order_info
create table order_info
(
order_id int primary key,
user_no varchar(10),
amount int,
create_date datetime
);
insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');
insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');
要求sql查询求每个⽤户的最新的⼀个订单。

传统的⽅式，尽量格式化的好读⼀点的情况下，说实话，这句sql咋⼀看有点莫名其妙，不知所以。

SELECT*FROM
(
SELECT
IF(@y=er_no, @x:=@x+1, @x:=1) X ,
IF(@y=er_no, @y, @y:=er_no) Y,
a.*
FROM order_info a, (SELECT@x:=0, @y:=NULL) b
ORDER BY er_no, a.create_date desc
) a
WHERE X <=1;
如下是执⾏结果，当然执⾏结果是可以满⾜需求的。

如果采⽤新的窗⼝函数的⽅法，
就是使⽤row_number()over(partition by user_no order by create_date desc) as row_num 给原始记录编⼀个号，
然后取第⼀个编号的数据，⾃然就是“⽤户的最新的⼀条订单”，实现逻辑上清晰了很多，代码也简洁，可读了很多。

select*from
(
select row_number()over(partition by user_no order by create_date desc) as row_num,
order_id,user_no,amount,create_date
from order_info
)t where row_num=1;
需要注意的是，MySQL中的使⽤窗⼝函数的时候，是不允许使⽤*的，必须显式指定每⼀个字段。

row_number()
(分组)排序编号，正如上⾯的例⼦， row_number()over(partition by user_no order by create_date desc) as row_num，按照⽤户分组，按照create_date排序，对已有数据⽣成⼀个编号。

当然也可以不分组，对整体进⾏排序。

任何⼀个窗⼝函数，都可以分组统计或者不分组统计（也即可以不要partition by ***都可以，看你的需求了）
rank()
类似于 row_number()，也是排序功能，但是rank()有什么不⼀样？新的事物的出现必然是为了解决潜在的问题。

如果再往测试表中写⼊⼀条数据：insert into order_info values (11,'u0002',800,'2018-1-22');
对于测试表中的U002⽤户来说，有两条create_date完全⼀样的数据（假设有这样的数据），那么在row_number()编号的时候，这两条数据却被编了两个不同的号
理论上讲，这两条的数据的排名是并列最新的。

因此rank()就是为了解决这个问题的，也即：排序条件⼀样的情况下，其编号也⼀样。

dense_rank()
dense_rank()的出现是为了解决rank()编号存在的问题的，
rank()编号的时候存在跳号的问题，如果有两个并列第1，那么下⼀个名次的编号就是3，结果就是没有编号为2的数据。

如果不想跳号，可以使⽤dense_rank()替代。

avg，sum等聚合函数在窗⼝函数中的的增强
可以在聚合函数中使⽤窗⼝功能，⽐如sum(amount)over(partition by user_no order by create_date) as sum_amont，达到⼀个累积计算sum的功能
这种需求在没有窗⼝函数的情况下，⽤纯sql写起来，也够蛋疼的了，就不举例了。

NTILE(N) 将数据按照某些排序分成N组
举个简单的例⼦，按照分数线的倒序排列，将学⽣成绩分成上中下3组，可以得到哪个程序数据上中下三个组中哪⼀部分，就可以使⽤NTILE(3) 来实现。

这种需求倒是⽤的不是⾮常多。

如下还是使⽤上⾯的表，按照时间将user_no = 'u0002'的订单按照时间的纬度，划分为3组，看每⼀⾏数据数据哪⼀组。

first_value(column_name) and last_value(column_name)
first_value和last_value基本上见名知意了，就是取某⼀组数据，按照某种⽅式排序的，最早的和最新的某⼀个字段的值。

看结果体会⼀下。

nth_value(column_name,n)
从排序的第n⾏还是返回nth_value字段中的值，这个函数⽤的不多，要表达的这种逻辑，说实话，很难⽤语⾔表达出来，看个例⼦体会⼀下就⾏。

n = 3
n = 4
cume_dist
在某种排序条件下，⼩于等于当前⾏值的⾏数/总⾏数，得到的是数据在某⼀个纬度的分布百分⽐情况。

⽐如如下⽰例
第1⾏数据的⽇期（create_date）是2018-01-05 00:00:00，⼩于等于2018-01-05 00:00:00的数据是1⾏，计算⽅式是：1/6 =
0.166666666
第2⾏数据的⽇期（create_date）是2018-01-06 00:00:00，⼩于等于2018-01-06 00:00:00的数据是2⾏，计算⽅式是：2/6 =
0.333333333
依次类推
第4⾏数据的⽇期（create_date）是2018-01-16 00:00:00，⼩于等于2018-01-16 00:00:00的数据是4⾏，计算⽅式是：4/6 =
0.6666666666
第⼀⾏数据的0.6666666666 意味着，⼩于第四⾏⽇期（create_date）的数据占了符合条件数据的66.66666666666%
percent_rank()
同样是数据分布的计算⽅式，只不过算法变成了：当前RANK值-1/总⾏数-1 。

具体算法不细说，这个实际中⽤的也不多。

lag以及lead
lag(column,n)获取当前数据⾏按照某种排序规则的上n⾏数据的某个字段，lead(column,n)获取当前数据⾏按照某种排序规则的下n⾏数据的某个字段，
确实很拗⼝。

举个实际例⼦，按照时间排序，获取当前订单的上⼀笔订单发⽣时间和下⼀笔订单发⽣时间，（可以计算订单的时间上的间隔度或者说买买买的频繁程度）
select order_id,
user_no,
amount,
create_date,
lag(create_date,1) over (partition by user_no order by create_date asc) 'last_transaction_time',
lead(create_date,1) over (partition by user_no order by create_date asc) 'next_transaction_time'
from order_info ;
CTE 公⽤表表达式
CTE有两种⽤法，⾮递归的CTE和递归的CTE。

⾮递归的CTE可以⽤来增加代码的可读性，增加逻辑的结构化表达。

平时我们⽐较痛恨⼀句sql⼏⼗⾏甚⾄上上百⾏，根本不知道其要表达什么，难以理解，对于这种SQL，可以使⽤CTE分段解决，
⽐如逻辑块A做成⼀个CTE，逻辑块B做成⼀个CTE，然后在逻辑块A和逻辑块B的基础上继续进⾏查询，这样与直接⼀句代码实现整个查询，逻辑上就变得相对清晰直观。

举个简单的例⼦，当然这⾥也不⾜以说明问题，⽐如还是第⼀个需求，查询每个⽤户的最新⼀条订单
第⼀步是对⽤户的订单按照时间排序编号，做成⼀个CTE，第⼆步对上⾯的CTE查询，取⾏号等于1的数据。

另外⼀种是递归的CTE，递归的话，应⽤的场景也⽐较多，⽐如查询⼤部门下的⼦部门，每⼀个⼦部门下⾯的⼦部门等等，就需要使⽤递归的⽅式。

这⾥不做细节演⽰，仅演⽰⼀种递归的⽤法，⽤递归的⽅式⽣成连续⽇期。

当然递归不会⽆限下去，不同的数据库有不同的递归限制，MySQL 8.0中默认限制的最⼤递归次数是1000。

超过最⼤低估次数会报错：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
由参数@@cte_max_recursion_depth决定。

关于CTE的限制，跟其他数据库并⽆太⼤差异，⽐如CTE内部的查询结果都要有字段名称，不允许连续对⼀个CTE多次查询等等，相信熟悉CTE的⽼司机都很清楚。

窗⼝函数和CTE的增加，简化了SQL代码的编写和逻辑的实现，并不是说没有这些新的特性，这些功能都⽆法实现，只是新特性的增加，可以⽤更优雅和可读性的⽅式来写SQL。

不过这都是在MySQL 8.0中实现的新功能，在8.0之前，还是⽼⽼实实按照较为复杂的⽅式实现吧。