数据窗口retrieve查询结果生成新表_SQL系列之窗口函数及经典使用场景，如topN排名问题...

日常生活中，经常会遇到需要在每组内排名的问题，比如每个部门按业绩排名，找出每个部门排名前N的员工等，面对这类需求，就需要使用SQL的高级功能--窗口函数。

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

窗口函数的基本语法：

<窗口函数> over (PARTITION BY <用于分组的列名> 
                 ORDER BY <用于排序的列名>)

窗口函数是对WHERE或者GROUP BY子句处理后的即通过进行操作，所以窗口函数原则上只能写在SELECT子句中。

窗口函数脑图

题1：表class中记录了每个学生所在班级和对应的成绩，现在需要在每个班级内按成绩排名，如果两个学生分数相同，那么排名并列;

在Navicat里创建的表class

解题思路：

1. 涉及到排名问题，可以使用窗口函数；
2. 脑图中已有三种专用窗口函数的区别，根据题目要求的排名规则，使用dense_rank函数；

对“每个班级内按成绩排名”进行解读：

• 每个班级内：按班级分组，(PARTITION BY 班级)对班级分组；
• 按成绩排名：ORDER BY子句对分组后的结果排序，默认升序(ASC)，本例中(ORDER BY 成绩 DESC)按成绩列排序，DESC表降序；

问题：GROUP BY子句有分组功能，ORDER BY子句有排序功能，为什么还要用窗口函数呢？

答：因为GROUP BY分组汇总后表的行数改变，一行只有一个类别，而PARTITION BY和dense_rank函数不会减少原表中的行数。

比如统计每个班级的人数：

题2：现有“成绩表”记录了每个学生各科成绩，查找每个学生成绩最高的2个科目；

方法1：关联子查询；

按课程号分组取成绩最大值所在行的数据。

Navicat中创建的成绩表

使用分组（GROUP BY）和汇总函数得到每个组里成绩最大值，但是无法得到成绩最大值所在行的数据。

使用关联子查询来实现：

方法2：窗口函数；

解题思路：

1. 当问题中出现“每个”的时候，就要想到分组了。问题中查看每个学生最高成绩，即为按学生（学号）来分组；
2. 将表按照学号分组后，成绩降序排列，排在最前面的2个就是“成绩最高的两个科目”；
3. 分组后排序，不能减少原表的行数，使用窗口函数；
4. 使用哪个专用窗口函数？为了不受并列的影响，使用row_number函数；

解题步骤：

步骤1：按学号分组（PARTITION BY 学号），成绩降序排序（ORDER BY 成绩 DESC）；

步骤2：进一步得到每个学生成绩最好的2个科目，即提取出“ranking”值小于等于2的数据，只需在步骤1的SQL语句中加入条件筛选WHERE就可以了；

原因：搞错了SQL的运行顺序，SQL运行顺序和书写顺序不一样！！！

SQL运行顺序：

1 先运行子查询；

2 每个查询语句中的运行顺序：1 -> 2 -> 3;

题中运行到“WHERE ranking<=2”时，SELECT子句还没有被执行，"ranking"列还没有出现，所以报错，怎么解决这个问题呢？

使用子查询，把第一步得到的查询结果作为一个新表。

举一反三：

将WHERE子句中的条件修改为N，可以变为topN问题的万能模板，下次遇到直接嵌套就好。

-- topN问题SQL万能模板
SELECT *
FROM(
SELECT *,
        row_number() over (PARTITION BY <要分组的列名> 
        ORDER BY <要排序的列名> DESC) AS ranking
FROM 表名) AS a
WHERE ranking<=N;

题3：累积求和问题；

表salary中记录了员工发放的薪水，包含雇员编号、薪水、起始日期和结束日期，其中当前员工指结束日期="9999-01-01"的员工。

业务问题：按照雇员编号升序排列，查找前N个当前员工薪水的累计和。

简化了salary表，只设置了6行

解题步骤：

步骤1：先筛选出当前员工（结束日期="9999-01-01"）的薪水；

步骤2：累计薪水（第一行的累计薪水为雇员编号01的薪水，第二行为雇员编号01和02薪水之和，以此类推）；

对于“累计”问题，要想到用聚合函数作为窗口函数。

比如累计求和，用SUM()；

SUM(列名) over (ORDER BY <用于排序的列名>)

累计平均，用AVG()；

AVG(列名) over (ORDER BY <用于排序的列名>)

所以，可得出“累计求和”问题的万能模板是：

SELECT 列1, 列2,
SUM(列名) over (ORDER BY <用于排序的列名>) AS 累计值的别名
FROM 表名;

举例说明窗口函数的移动平均。

想要计算当前行与前n行（共n+1）行的平均时，只要调整rows ... preceding中间的数字即可，通过preceding关键字调整作用范围，在以下场景中非常适用：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。