pyqt 只能打开子窗口一次_第七关：SQL窗口函数

一、窗口函数介绍
1.窗口函数用处
它可以直接解决组内排名。如：对班级的每个学生的各科成绩排序、top n 问题：查询各科成绩前几名的学生信息等。2.窗口函数的基本语法

‹窗口函数› over (partition by ‹对什么分组›        
    order by ‹按什么排序›)

那么‹窗口函数›栏可以放些什么函数呢？score表如下：

1.rank( )

SELECT	*, 
RANK () over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS ranking
FROM	score

查询结果如下

2.dense_rank( )

SELECT	*, 
 dense_rank () over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS ranking
FROM	score

3.row_number

SELECT	*, 
 row_number () over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS ranking
FROM	score

查询结果如下

都是组内排序也是有区别的
现在放在一起比较

SELECT	*, 
rank() over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS ranking,
dense_rank() over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS dense_ranking,
row_number() over (PARTITION BY 学号
		ORDER BY  成绩 DESC) AS num_ranking
FROM	score

查询结果如下图：

4.聚合函数（MAX,MIN,SUM,AVG,COUNT）

SELECT	*, 
 MAX(成绩) over (PARTITION BY 学号
		) AS MA_ranking,
MIN(成绩) over (PARTITION BY 学号
		) AS MI_ranking,
SUM(成绩) over (PARTITION BY 学号
		) AS S_ranking,
AVG(成绩) over (PARTITION BY 学号
		) AS A_ranking,
COUNT(成绩) over (PARTITION BY 学号
		) AS C_ranking
FROM	score 

查询结果如下：

这恰好是group by的结果，但是select……group by……只能查询出每组的第一个数据，而窗口函数功能可以查询出分组后的每组的全部数据。接下来加入排序

SELECT	*, 
 MAX(成绩) over (PARTITION BY 学号
	ORDER BY 课程号	) AS MA_ranking,
MIN(成绩) over (PARTITION BY 学号
	ORDER BY 课程号	) AS MI_ranking,
SUM(成绩) over (PARTITION BY 学号
	ORDER BY 课程号	) AS S_ranking,
AVG(成绩) over (PARTITION BY 学号
	ORDER BY 课程号	) AS A_ranking,
COUNT(成绩) over (PARTITION BY 学号
	ORDER BY 课程号	) AS C_ranking
FROM	score

可以发现 MAX，MIN，SUM，AVG，COUNT都是针对每一组自身记录、以及自身记录之上的所有数据进行计算。
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本组本行数据，每组的统计数据（MAX，MIN，SUM，AVG，COUNT）是多少。同时可以看出每一行数据，对某组整体统计数据的影响。二、窗口函数的用处窗口函数解决TOP N 问题（查询按某个分组的前N条数据）上一关有个题目为：查询各科成绩前两名的记录现在来回顾一下解决方法1.自联结
select a.`课程号`,a.`学号`,a.`成绩`

from score a

left join score b on b.`课程号`=a.`课程号` and a.成绩<b.成绩

GROUP BY a.`课程号`,a.`学号`,a.`成绩`

HAVING COUNT(*)<2

ORDER BY a.`课程号`,a.`成绩` DESC所用原理是假设a.成绩为第三名，则a left jion b 的 a.`课程号`,a.`学号`,a.`成绩`所存在列数为2，如图：

课程号0001的80分成绩就为第三名，比它高的有90分和99分，就会出现两列，因此要查询前两名的成绩应该加上条件having count（*）<2或者直接=1。

2.关联子查询
select *

from score a

where 2>

(select COUNT(*)

from score b where b.课程号=a.课程号

and b.成绩>a.成绩)

ORDER BY a.`课程号`,a.`成绩` DESC首先观察原表（score表）

关联子查询首先将第一行的数据代入子查询中，select COUNT(*)

from score b where b.课程号=1

and b.成绩>80然后此时count（*）=2,90>80,99>80；接着将第二行数据代入子查询中select COUNT(*)

from score b where b.课程号=1

and b.成绩>90此时count（*）=1，只有99>90继续进行就可以得到最终答案。因为我们要查询的是前两名的成绩，比第一名大的只有0行，比第二名大的只有1行，因此count（*）=0或者为1，count（*）<2。3.union all 分别求出每个课程号对应的前两名，在并在一起。但是前两名有并列就会不准确。4.运用今天的窗口函数SELECT 课程号,学号,成绩

FROM

(SELECT *,

rank() over (PARTITION BY 课程号

ORDER BY 成绩 DESC ) AS ranking

FROM score)AS a

WHERE ranking<3现在来解决TOP N 问题（查询按某个分组的前N条数据）唯一与上题不同的是这里窗口函数用row_number。
SELECT *
FROM (SELECT
*, row_number () over ( PARTITION BY （对什么分组）
ORDER by（按什么排序）) AS ranking
FROM 表名 ) AS a
WHERE
ranking ‹ = N;窗口函数常见题目：如何在每个组里比较？

如 查找单科成绩高于该科目平均成绩的学生名单1.窗口函数分析思路：
将avg(成绩)填写到窗口函数处，按课程号分组，可以查询出每组的平均成绩，之后平均成绩与学生成绩比较即可
具体查询如下：

SELECT * 
FROM
(SELECT *,AVG(成绩) over (PARTITION BY 课程号) as 平均成绩  from score)AS a
WHERE 成绩>平均成绩

2.关联子查询

SELECT * 
FROM score a
WHERE 成绩 >(select AVG(成绩) FROM score b WHERE b.课程号=a.课程号) 

窗口函数的移动平均

select *,        avg(成绩) over (order by 学号 rows N preceding) 
as current_avg from 班级表;
 

表示该行数据与前面N行数据的平均总结这一节为SQL的窗口函数
首先介绍了窗口函数的概念以及基本语法法（‹窗口函数› over (partition by ‹对什么分组› order by ‹按什么排序›)）；
接着介绍了窗口函数的使用注意事项（窗口函数原则上只能写在select子句中）；
最后介绍一些常见的MySQL支持的窗口函数，并在实例中运用：
①排序函数：ROW_NUMBER()、RANK()、DENSE_RANK()，比较其排序规则，在经典排名问题以及在TOP N 问题中的使用。
②聚合函数：（SUM()、AVG()、MAX()、MIN()、COUNT()），动态计算在指定的窗口内的各种聚合函数值，在每组进行比较：查找单科成绩高于该科目平均成绩的学生名单