![992853facea442796a8a1e00a0344b57.png](https://i-blog.csdnimg.cn/blog_migrate/7e35f8f77e672f9d5ca86bba1cde5f08.jpeg)
![03bb7b7d85852531af2c91d61e7a6994.png](https://i-blog.csdnimg.cn/blog_migrate/e97912ccc4594c71afb1b7ddb776911f.png)
1.窗口函数
1.1定义
窗口函数,即OLAP函数(Online Anallytical Prcessing,联机分析处理),可以对数据库数据进行实时分析处理。具备分组、排序,同时又不减少原表行数的功能。
(由于分组后的结果称为“窗口”,表示“范围”的意思,so,OLAP函数被称为窗口函数、开窗函数)
1.2 语法结构
<窗口函数> OVER (PARTITION BY<分组列名> ORDER BY<排序列名>)
<窗口函数>位置可以是以下2种函数:
- 专用窗口函数,RANK()、DENSE_RANK()、ROW_NUMBER()等。
- 聚合函数,SUM(列)、AVG(列)、COUNT(列)、MAX(列)、MIN(列)。
1.3 注意事项
窗口函数是对WHERE或GROUP BY子句处理后的结果进行操作,所以窗口函数原则上只能写在SELECT子句中。
1.4 使用场景
- 排名类问题,在每个组内排名,如每个部门按业绩做排名。
- topN类问题,如找出每个业务部业绩前N的业务组。
# topN问题 sql模板
SELECT *
FROM (SELECT *,
ROW_NUMBER()OVER(PARTITION BY <分组列名> ORDER BY<排序列名>DESC) AS ranking
FROM <表名>)AS a
WHERE ranking<=N;
- 在每个组里比较的问题,如查找每个组里大于平均值的数据。
- 累计求和的问题,如查找当前员工薪水的累计之和。
2.专用窗口函数
RANK()、DENSE_RANK()、ROW_NUMBER()的功能都是进行排序,且函数括号中不需要参数,仅保持()空即可,但不同的是:
2.1 功能区别
- rank()函数是跳跃排序,数值按照要求升序/降序后,相同重复的值做并列排序,非重复值会跳过重复值的位数再依次进行排序。
- dense_rank()函数是连续排序,重复值做并列排序,所有值是依次排序。
- row_number()函数是没有重复值的排序。
SELECT 成绩,
RANK()OVER(ORDER BY 成绩 DESC) AS 'rank()排名',
DENSE_RANK()OVER(ORDER BY 成绩 DESC) AS 'dense_rank()排名',
ROW_NUMBER()OVER(ORDER BY 成绩 DESC) AS 'row_number()排名'
FROM score;
![9b0b8a3f8e0cda4a6f0cb96d60796d70.png](https://i-blog.csdnimg.cn/blog_migrate/7c13fbf7438020f93ec82e44074b776f.png)
3.聚合类窗口函数
3.1 语法结构
<SUM(列)|AVG(列)|COUNT(列)|MAX(列)|MIN(列)> OVER (PARTITION BY<分组列名> ORDER BY<排序列名>)
3.2 语法特点
聚合窗口函数后的括号()里不能为空,需要指定聚合的列名。
3.3 功能介绍
对比goup by 聚合函数返回分组后的单行聚合结果,sum、avg、count、max、min窗口函数返回的是每行的聚合结果,实现的是累计功能,每行数据对自身行记录、以及位于自身行记录以上行的数据进行聚合计算。
partition by可以省略,这时累计的依据是order by 后的列,当要排序的列里出现几行值都相等的情况时,这几行对应的聚合窗口函数结果是一样的,如下例子:
![835ddc459ba847e1ee69b4172817dcc8.png](https://i-blog.csdnimg.cn/blog_migrate/18dd07593cc02f435cc5d2d4030e515c.jpeg)
增加分区,排序依据是‘成绩'时:
![1727edc7f4d0674236b230f19edd70a6.png](https://i-blog.csdnimg.cn/blog_migrate/d234c1ab7b5b45f1730664af8f9a7ca6.jpeg)
增加分区,排序依据是‘学号'时:
![313343454446f2b60ca82e81825ed9f6.png](https://i-blog.csdnimg.cn/blog_migrate/5edfa7fc2b0f18b56dc2a27c12d3e042.jpeg)
3.4 移动窗口--PRECEDING
语法:
<窗口函数> OVER (PARTITION BY<分组列名> ORDER BY<排序列名> ROWS<行数>PRECEDING)
rows<n>preceding ,表示向前n行计算聚合函数,也就是计算自身行及其前n行的聚合值,共n+1行。假设自身所在行数是x,当x<(n+1)时,聚合函数就累计x个行的值。
例:求每位学生与其前2位学号的学生的总成绩、平均成绩
![ef0726372fb1ff24c5d08acd8892ed57.png](https://i-blog.csdnimg.cn/blog_migrate/9c70c8343474902f51fd9f67cac341da.jpeg)
4.练习题
4.1 topN类问题
数据库表如下:
![ab1f4f6077eb66315d1e343af0cd0857.png](https://i-blog.csdnimg.cn/blog_migrate/8bf1754908d33cce16cbc205a17e09f1.jpeg)
![31e2d90d870e86fa0224b9db8f1bf861.png](https://i-blog.csdnimg.cn/blog_migrate/97747cb56006a8f282835a53ebd648ef.jpeg)
4.2 累计求和问题
数据库表如下:
![cfdc0376173cb530d39ce3af3ee25525.png](https://i-blog.csdnimg.cn/blog_migrate/ac57ab28cc817c53251425b56715105a.jpeg)
'薪水'是指该雇员在起始日期到结束日期这段时间内的薪水。
当前员工是指'结束日期' = '9999-01-01'的员工(该公司业务:没有离职的员工,用这个值表示)。
![2dbf07b736c6f515f16af36695d79ec7.png](https://i-blog.csdnimg.cn/blog_migrate/d62b87c09ca2cfa43417587b8264811e.jpeg)
4.3 每个组里比较问题
数据表如下:
![1c45196415b1baed7aa97ae4f75a1ef8.png](https://i-blog.csdnimg.cn/blog_migrate/befb35c215807c732d101314469a4458.png)
![20965db50494279e6b2aa2832e2a67f2.png](https://i-blog.csdnimg.cn/blog_migrate/490208d67385a8c783094e22c3401856.jpeg)
![781ed0df7ade9fb9faacc3ffcb81ee42.png](https://i-blog.csdnimg.cn/blog_migrate/234b86c880e7edc6ea26c313fca81d91.png)