1

MySQL窗口函数优秀实践，你学会了吗？

 1 year ago
source link: https://www.51cto.com/article/721917.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

MySQL窗口函数优秀实践，你学会了吗？

作者：指北君 2022-11-03 08:16:33
Mysql 8.0新增的窗口函数极大简化了sql实现语句，实现了更加复杂的数据逻辑，可以满足更多的开发场景，从而相应减少了代码开发成本。

​大家好，我是指北君。共勉名言：

有知识的人不实践，等于一只蜜蜂不酿蜜。——萨迪

今天的内容主要以实践为主，有兴趣的小伙伴可以一同操作~

目前生产环境中MySQL一直使用的是5.7版本，不敢贸然升级版本，涉及数据结构、数据备份等内容。但看到各大平台分享的Mysql8的新版本特性，按捺不住强烈的好奇心，于是在本地搭建了Mysql服务，实际验证了一部分新功能，确实带给我新的认知。接下来就分享给大家使用心得。

  • Mysql是小编从事开发行业三年多来，接触的最多的数据存储介质，它属于关系型数据库，以开源免费、体积小、速度快、使用成本低等优点，深得大部分用户喜爱，同时也受很多公司青睐。
  • 自从8.0.11正式版本发布以来，不知不觉已经有四年多的时间，官方号称比5.7版本快两倍（读写负载、IO密集型任务负载、高竞争负载等），同时新增了窗口函数（实现类似集合函数的新型查询方式）等。下面将从窗口函数特性进行详细介绍。

​窗口函数

  • 窗口函数又名OLAP函数（Online Anallytical Processing,联机分析处理），用来实时分析处理数据；
  • 通用语法：select 窗口函数 over (partition by 分组列名, order by 排序列名)
  • 专用窗口函数：

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新特性一小部分，其它特性还在探索中，如果有疑问和想法可以私信小编，大家一起学习讨论。​


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK