大家好,我是指北君。共勉名言:
有知识的人不实践,等于一只蜜蜂不酿蜜。——萨迪
今天的内容主要以实践为主,有兴趣的小伙伴可以一同操作~
目前生产环境中MySQL一直使用的是5.7版本,不敢贸然升级版本,涉及数据结构、数据备份等内容。但看到各大平台分享的Mysql8的新版本特性,按捺不住强烈的好奇心,于是在本地搭建了Mysql服务,实际验证了一部分新功能,确实带给我新的认知。接下来就分享给大家使用心得。
rank函数:如按班级名称分类,按序号正序,用rank函数实现,相同序号会出现并列ranking值
SELECT *, RANK() over (partition by `NAME` ORDER BY NUM) as ranking FROM f0627 结果 name num ranking A 1 1 A 2 2 A 3 3 A 4 4 A 6 5 B 2 1 B 2 1 B 8 3 sql说明:rank为排序函数,通过partition by按照班级名称分组(此处不会类似group by将数据去重),然后按照序号正序,最后得到的rangking字段就是分类后的排序结果。
row_number函数:同样如按班级名称分类,按序号正序,会忽略相同序号,顺序生成ranking值
SELECT *, ROW_NUMBER() over (partition by `NAME` ORDER BY NUM) as ranking FROM f0627 结果 name num ranking A 1 1 A 2 2 A 3 3 A 4 4 A 6 5 B 2 1 B 2 2 B 8 3 sql说明:rank为排序函数,通过partition by按照班级名称分组(此处不会类似group by将数据去重),然后按照序号正序,最后得到的rangking字段就是分类后的排序结果,观察ranking结果忽略了并列情况。聚合窗口函数:
sum()函数:如按班级名称分类,按序号正序,累加序号,将分类后第一行至当前行的累加结果汇总至‘求和’字段
SELECT *, SUM(NUM) over (partition by `NAME` ORDER BY NUM) as '求和' FROM f0627 结果 name num 求和 A 1 1 A 2 3 A 3 6 A 4 10 A 6 16 B 2 4 B 2 4 B 8 12 sql说明:sum()为求和函数,通过partition by按照班级名称分组(此处不会类似group by将数据去重),然后按照序号正序,将每种分类第一行至当前行的序号累加结果汇总至‘求和’字
avg()函数:在上面sum函数基础上,增加avg函数计算平均值
SELECT *, SUM(NUM) over (partition by `NAME` ORDER BY NUM) as '求和', AVG(NUM) over (partition by `NAME` ORDER BY NUM) as '平均' FROM f0627 结果 name num 求和 平均 A 1 1 1.0000 A 2 3 1.5000 A 3 6 2.0000 A 4 10 2.5000 A 6 16 3.2000 B 2 4 2.0000 B 2 4 2.0000 B 8 12 4.0000 sql说明:avg()为平均值函数,通过partition by按照班级名称分组(此处不会类似group by将数据去重),然后按照序号正序,将每种分类第一行至当前行的序号累加结果求平均值至‘平均’字段CTE表达式(Common Table Expressions,通用表表达式):结合窗口函数使得复杂的嵌入查询更加清晰,提高了可读性
求平均值案例
WITH cte as (SELECT *, SUM(NUM) over (partition by `NAME` ORDER BY NUM) as suming, AVG(NUM) over (partition by `NAME` ORDER BY NUM) as avging FROM f0627) SELECT * FROM cte where avging > 2 结果 name num suming avging A 4 10 2.5000 A 6 16 3.2000 B 8 12 4.0000 sql说明:with cte as (sql) 将sql结果可以定义为cte的派生表,可以直接查询派生表过滤平均值大于2的结果。
Mysql 8.0新增的窗口函数极大简化了sql实现语句,实现了更加复杂的数据逻辑,可以满足更多的开发场景,从而相应减少了代码开发成本。
当窗口函数结合cte使用时,可以将嵌套查询分层,使得语句可读性更高,当然性能也是有保证的。
以上就是分享的全部内容,仅是mysql8新特性一小部分,其它特性还在探索中,如果有疑问和想法可以私信小编,大家一起学习讨论。