findwindowex子窗口类型有哪几种_SQL窗口函数

窗口函数

‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

• 适用范围：每组内排名—— 排名问题/TopN问题
• OLAP函数——实时分析处理
• 类型：

1. 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数(函数后面的括号不需要任何参数，保持()空着就可以)
2. 聚合函数，如sum. avg, count, max, min等(函数后面括号里面不能为空，需要指定聚合的列名)

• 重点：因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中
• 功能：同时具有分组和排序的功能；不减少原表的行数
• partition子句可是省略，省略就是不指定分组

排名问题

Rank函数——排序：每个班级内成绩排名

select *, rank() over (partition by 班级 order by 成绩 desc) as ranking from 班级表

1 按班级分组——partition by 班级

2 按班级排名——order by 成绩 desc

注意：与 group by差别——group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数

专用函数区别

在于并列值的排序情况

select *, rank() over (order by 成绩 desc) as ranking,

dense_rank() over (order by 成绩 desc) as dese_rank,

row_number() over (order by 成绩 desc) as row_num

from 班级表

TopN问题

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

select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 成绩表 where ranking ‹=2；报错——SQL运行顺序

解决方法：子查询

select * from (select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 成绩表) as a where ranking ‹= 2;

TopN 模版

select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking ‹= N;

聚合函数

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 班级表;

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

每个组里比较：每个组里大于平均值的数据

方法1: 子查询

方法2: 窗口函数

找单科成绩高于该科目平均成绩

select * from (select *, avg(成绩) over (partition by 科目) as avg_score from 成绩表) as b where 成绩 › avg_score;

移动平均

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

注：rows N preceding 之前的N行+自身行的

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

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