sql over函数_SQL 窗函数

最近做题遇到了要用到窗函数的题目，所以去查了下窗函数是什么，写写自己的看法和理解

为什么我们需要窗函数？

大家在查询数据的时候经常会碰到一个情况，就是我既要对数据进行分组，又要对每个组进行某种聚合运算，同时还要对每行输出对应的聚合运算的结果，这用 group by 很难实现，举个例子：

我有一组数据，包含以下字段：城市，姓名，年龄，性别，需求是找出每个城市的人数，按照如下格式输出：城市，姓名，年龄，性别，人数。 很显然这题用 group by 是很难写的吧？（我不知道能不能写），再深入的，找出同龄人以及城市人数，按照如下格式输出：城市，姓名，年龄，性别，人数，同龄人人数。这貌似就更难写了吧？

所以为了解决 SQL 语句的局限性，SQL加入了窗函数的概念。

什么是窗函数？

窗函数本质还是聚合运算，只不过它更具灵活性，它对数据每一行都会返回符合条件的数据，这样就避免了非常复杂的查询语句。

窗函数的结构

窗函数基本上是一个聚合函数加上 OVER 关键字组成，比如：

COUNT(*)OVER()

SUM(*)OVER()

等等。

比如，我有一组数据：课程号，学生号，学生分数

请问，按照总分进行排序，输出名次，按照如下格式输出：学生号，总分，排名。

这题当然可以用变量去做，但是实际上用窗函数可以很快解决，因为用变量实际上是做了递归了吧？（这个不太懂，我自己猜的）。

select *,total,rank()over(order by total desc) as 排名
from (select sc.sid ,sum(score) as total from sc group by sc.sid order by total desc)

OVER函数里面还有很多参数可选，但是貌似掌握了 order by ，partition by 就差不多了吧，前者是字面意思了，后者就是按照某个字段进行聚合运算，比如上面的问题，找出同龄人

就可以用 COUNT(*)OVER(partition by age)

需要注意的是，虽然功能上 PARTITION BY 有点像 GROUP BY，但是前者是独立于结果集的，什么意思呢？GROUP BY 分出来的东西会输出，影响其他的列，而前者就像创造了一个副本，这样的话，不同的 partition by 是互不影响的，比如上面那道题，我可以同时写

COUNT(*)OVER(partition by age)

COUNT(*)OVER(partition by city)

这两个副本是互不影响的，你用 GROUP BY 肯定是做不到这样的。

应用场景

知道窗函数，知道怎么用其实是不够的，因为一到具体场景，你可能就不知道去用了，我们也必须要知道有哪些场景可以用。我不是专业的 SQL 人员，也不懂什么 SQL 优化之类的，只是用来做些简单的操作。以我的经验来看，一旦需要输出一列原数据集中没有的数据，且用 GROUP BY 你做不出来的时候，你就可以试试窗函数。

最后谈谈做了这么多 SQL 练习的感想吧，SQL 不比的写程序，写程序是有需求，然后按照需求抽象出数学模型，最后写成算法结构，然后再一步步的填就行了。而写 SQL 更多的是需要跟原本数据打交道，没人教你怎么写，比如没人教你怎么拆分需求，大多数人一上来就是 select from，管他那么多，先写个 select 再说，实际上这是很不好的习惯，写 SQL 也是解构需求的过程，举个例子，大家都可能写过三表甚至四表链接查询，一上来大家可能就直接一把梭子写完了，写完了再调，实际上可以先输出下三表链接的结果，再按照结果来决定下一步如何查询，再比如我需要找到学这个老师的某门课的考第一的学生的性别，这可能就好几个表去链接，但是我可以先输出下老师的这门课是啥，再输出这门课的成绩单，再找最高的，再找最高对应的学生，有了思路其实就好写很多了，而且 sql 没调试工具，基本就是对着莫名其妙的错误发呆，不是专业 sql 的人员根本不知道哪里错了，毕竟我们只是把 sql 当工具而已。