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

吾爱主题阅读：152 2024-04-01 23:26:01 评论：0

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

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

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

前言

目前生产环境中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的结果。