SQL轻松入门(5):窗口函数

01 前言

标题中有2个字让我在初次接触窗口函数时，真真切切明白了何谓”高级”？说来也是一番辛酸史！话说，我见识了窗口函数的强大后，便磨拳擦掌的要试验一番，结果在查询中输入语句，返回的结果却是报错，What？聪明的你，猜猜为啥？

害，原因可不就是MySQL的版本不匹配呗，我的原装是5.5，而窗口函数可运用的版本是MySQL8.0。经历了卸载重装，我对窗口函数的印象可是老深的啦！闲话到此，正题开始~

窗口函数的高级不仅体现在版本要求高，还体现在功能上的简洁易懂。以Excel中vlookup作类比，窗口函数≈vlookup，group by+order by +关联子查询≈index+match，虽不完全贴切，但能助你理解即可。

本文所涉及知识点，框架如下：

02 什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

基本语法: ?窗口函数? over (partition by ?用于分组的列名? order by ?用于排序的列名?)。语法中的?窗口函数?主要由专用窗口函数（rank、dense_rank和row_number等组成）与聚合函数（sum、avg、count、max和min等）作为窗口函数组成。

从窗口函数组成上看，它是group by 和 order by的功能组合，既然我们已经学了group by和order by，为什么还要学窗口函数呢？group by分组汇总后改变了表的行数，一行只有一个类别，而partiition by则不会减少原表中的行数。

恰如窗口函数的组成，它同时具有分组和排序的功能，且不减少原表的行数。

03 窗口函数如何使用？

以截图问题为例：查看每个班级的排名情况

截图示例中使用了专有窗口函数rank，从整体执行效果来看，语句十分简洁。我们也可以理解：为什么要叫“窗口”函数？因为partition by分组后的结果被称为“窗口”，这里的窗口不是指我们家里的门窗，而是表示“范围”的意思。

04 窗口函数分类和应用

从上例，我们可以清晰看到窗口函数的厉害之处，所以会在了解窗口函数组成部分的同时通过应用的讲解来加深理解。

排序函数rank、dense_rank和row_number的区别在于并列的排名表现。

• rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。
• dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。
• row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况，和Excel中的row（）函数一样，依据顺序排列下去。

topN问题：在日常工作中十分常用，以查找每个学生成绩最高的2个科目为例：

截图中红色标注是为了说明掌握sql语句运行顺序的重要性，能看懂报错十分重要，有时候很容易忽略语句的运行顺序，谨记！

topN的万能模板：

select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking ?= N;

如果是最小的话，只需变动order by的排序方式即可。

汇总分析版块中的常用聚合函数相信我们已掌握，那么在窗口函数中，它们区别于专用窗口函数的一个点是括号里需带有对应的列名。

上图以2列突出显示，可以观察到sum（）数值不断累加，avg（）也是，由此可以说明，窗口函数中可以看到1列的数值动态变化过程。

根据上面的观察，我们可以解决业务中的累计求和问题。

“累计求和”问题的万能模板是：

select 列1,列2, sum(列名) over (order by ?用于排序的列名?) as 累计值的别名 from 表名;在开篇对比中，我有说到关联子查询，毫无疑问，接下来的问题和”每一组对比”相关，那么如何在每个组里比较？

通过上述2种方法，再一次验证了窗口函数的简洁高效，以后若是遇到每组比较的问题，要知道有2种解法，同时大脑中能反映出这2种语句，但执行可以选择窗口函数。

窗口函数除了能解决以上的应用外，还可以实现移动平均，或者移动求和等，以移动平均为例：

红色标注的就是移动平均的关键字，截图是包括本行再往上2行，也可以变成数字4，那么就是5行对比，重点可以观测到相邻数值之间的差异。

综上，窗口函数所包含的内容绝不仅是上面这些，随着我们在业务中的不断打磨，我们需要不断扩充自己的知识库，平时要多总结学习。