select函数_SQL 之 窗口函数

一.什么是窗口函数

1.窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名的业务。

1）排名问题：每个部门按业绩来排名；

2）topN问题：找出每个部门排名前n的员工进行奖励。

面对这类需求，需要使用sql的高级功能窗口函数。

2.什么是窗口函数？

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

基本语法：

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

3.窗口函数的种类

1）专用窗口函数：RANK，DENSE_RANK，ROW_NUMBER

2）聚合函数： SUM，AVG， COUNT，MAX，MIN

注意事项

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

---

二.如何使用窗口函数

1.专用窗口函数

1）RANK

问：在每个课程内对学生按成绩排名。

SELECT *, RANK() OVER (PARTITION BY 课程号  ORDER BY 成绩 DESC) AS 'ranking'
FROM scor

• 每个课程内，即按课程分组：PARTITION BY 用来对表分组
• 按成绩排名：ORDER BY 子句功能是对分组后的结果进行排序，默认按照升序。

注：为什么叫窗口函数？—PARTITION BY分组后的结果称为“窗口”，表示“范围”的意思。

窗口函数功能

1）同时具有分组和排序的功能；

窗口函数同时具备了GROUP BY和ORDER BY功能。

2）不减少原表的行数。

GROUP BY分组汇总后改变了表的行数，一组只有一行，一行只有一个类型。

而PARTITION BY和RANK函数不会减少原表中的行数。

2）RANK, DENSE_RANK, ROW_NUMBER

RANK：如果有并列名次的行会占用下一名次的位置。

e.g 前三名并列：1，1，1，4

DENSE_RANK：如果有并列名次的行，不占用下一名次的位置。

e.g 前三名并列：1，1，1，2

ROW_NUMBER：不考虑并列名次的情况。

e.g 前三名并列：1，2，3，4

案例：面试经典topN问题

分组取每组最大值，最小值，每组最大的N条记录。

1.分组取每组最大值

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

我们可以使用分组GROUP BY和汇总函数得到每个组里的一个值（最大值，最小值，平均值等），但是无法得到成绩最大值所在行的数据。

SELECT 课程号, MAX(成绩) AS 最大成绩
FROM score
GROUP BY 课程号;

使用关联子查询来实现：

SELECT * 
FROM score AS a
WHERE 成绩 = (
SELECT MAX(成绩)
FROM score AS b
GROUP BY 课程号
HAVING b.课程号=a.课程号
);

2.分组取每组最小值

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

同样可以使用关联子查询来实现：

SELECT * 
FROM score AS a
WHERE 成绩 = (
SELECT MIN(成绩)
FROM score AS b
GROUP BY 课程号
HAVING b.课程号=a.课程号
);

3.每组最大的N条记录

问：查找每个学生成绩最高的2个科目。

解题思路：

1.每个学生：按学号分组；

2.成绩最高的2个科目：每组按成绩降序排名取前两个；

3.为了不受并列成绩影响，使用ROW_NUMBER窗口函数。

SELECT * 
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY 学号 ORDER BY 成绩 DESC) AS 'ranking'
FROM score) AS a
WHERE ranking <= 2;

模版：查找每个分组某列值最高的N个记录。

SELECT * 
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY 要分组的列名 ORDER BY 要排序的列名 DESC) AS 'ranking'
FROM 表名) AS a
WHERE ranking <= N;

---

2.聚合窗口函数

聚合函数作为窗口函数时，可以在每一行的数据里直观的看到：

1.截止到本行数据，统计数据（最大值，最小值等）是多少；

2.每一行数据对整体统计数据的影响。

SELECT *, SUM(成绩) OVER (ORDER BY 学号,课程号) AS current_sum,
AVG(成绩) OVER (ORDER BY 学号,课程号) AS current_avg,
COUNT(成绩) OVER (ORDER BY 学号,课程号) AS current_count,
MAX(成绩) OVER (ORDER BY 学号,课程号) AS current_max,
MIN(成绩) OVER (ORDER BY 学号,课程号) AS current_min
FROM score;

ORDER BY的列名唯一才会逐行累积：

SELECT *, SUM(成绩) OVER (ORDER BY 学号) AS current_sum,
AVG(成绩) OVER (ORDER BY 学号) AS current_avg,
COUNT(成绩) OVER (ORDER BY 学号) AS current_count,
MAX(成绩) OVER (ORDER BY 学号) AS current_max,
MIN(成绩) OVER (ORDER BY 学号) AS current_min
FROM score;

案例：如何在每个组里比较

问：查找单科成绩高于该科目平均成绩的学生名单。

解题思路

1.查找单科成绩高于该科目平均成绩，也就是在每个科目里比较，考虑分组。

能实现“分组”功能的SQL有两种，一是GROUP BY子句，另一个是窗口PARTITION BY。

2.使用窗口聚合函数（求平均值AVG），将每门课的平均成绩求出之后，然后找出大于平均成绩的数据，即分组后不能减少表的行数，所以使用PARTITION BY。

SQL语句

第一步：聚合函数AVG( )作为窗口函数，求出每一科目的平均值；

SELECT *, 
AVG(成绩) OVER (PARTITION BY 课程号) AS avg_score
FROM score;

第二步：筛选出成绩大于平均分的数据。

SELECT *, 
AVG(成绩) OVER (PARTITION BY 课程号) AS avg_score
FROM score
WHERE 成绩 > avg_score

这样书写会报错！！！忽略了SQL的运行顺序，应改为：

SELECT *
FROM (
SELECT *, 
AVG(成绩) OVER (PARTITION BY 课程号) AS avg_score
FROM score
) AS a
WHERE 成绩 > avg_score;

也可使用关联子查询解决该问题

SELECT * 
FROM score AS a
WHERE 成绩 > (
SELECT AVG(成绩)
FROM score AS b
GROUP BY 课程号
HAVING a.课程号 = b.课程号
);

---

3.窗口函数的移动平均

例如：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

使用窗口函数的移动平均，每一行得到的结果是，当前行和该行前两行（共3行）的平均值。

SELECT *, 
AVG(成绩) OVER (ORDER BY 学号 ROWS 2 PRECEDING) AS current_avg
FROM score;