sql累计求和时间太长_（七）SQL知识点--窗口函数

目录

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;

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 后的列，当要排序的列里出现几行值都相等的情况时，这几行对应的聚合窗口函数结果是一样的，如下例子：

增加分区，排序依据是‘成绩'时：

增加分区，排序依据是‘学号'时：

3.4 移动窗口--PRECEDING

语法：

<窗口函数> OVER (PARTITION BY<分组列名> ORDER BY<排序列名> ROWS<行数>PRECEDING)

rows<n>preceding ,表示向前n行计算聚合函数，也就是计算自身行及其前n行的聚合值，共n+1行。假设自身所在行数是x，当x<(n+1)时，聚合函数就累计x个行的值。

例：求每位学生与其前2位学号的学生的总成绩、平均成绩

4.练习题

4.1 topN类问题

数据库表如下：

4.2 累计求和问题

数据库表如下：

'薪水'是指该雇员在起始日期到结束日期这段时间内的薪水。

当前员工是指'结束日期' = '9999-01-01'的员工（该公司业务：没有离职的员工，用这个值表示）。

4.3 每个组里比较问题

数据表如下：