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

一、窗口函数

1. 含义

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。窗口函数包含专用窗口函数和聚合函数，两者区别在于，专用窗口函数返回的是每组多行数据，不影响行数。聚合函数只返回的是每组的单行数据。

2. 基本语句

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

2. 窗口函数类型

专用窗口函数：rank，denserank, row_number等

聚合函数：max，min，sum，avg，count等

二、如何使用窗口函数

建立测试表，如下

INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0001','1','86');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0002','1','95');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0003','2','89');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0004','1','83');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0005','2','86');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0006','3','92');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0007','3','86');
INSERT INTO 班级表(学号,班级,成绩)
            VALUES('0008','1','88');

每个班级内按成绩排名，得出结果。

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

我们理解以下窗口函数的sql语句

1. partition by 用来对表的分组

每个班级内就是按照班级分组，所以partition by 班级

2. order by 对分组后进行排序，默认升序。

按成绩排名，,所以order by 成绩，加了desc是按照成绩降序排列。

分组汇总与窗口函数的区别：

group by分组汇总后会改变表的行数，一行只有一个类别。而partition by和rank 函数不会影响原表的行数。

/*group by 分组汇总改变行数*/
select 班级,count(学号)
from 班级表
group by 班级
order by 班级;

/*partition by 分组汇总总行数不变*/
select 班级,
count(学号) over (partition by 班级
                  order by 班级) as current_count
from 班级表;

三、专用窗口函数

rank ：跳跃排序,相同数据排名相同，排名存在重复值。

dense_rank ：连续排序，相同数据排排名相同。

row_number ：相同数据，在前面的排名优先，没有重复值。

select *,
rank() over (order by 成绩 desc ) as ranking,
dense_rank() over (order by 成绩 desc) as dense_ranking,
row_number() over (order by 成绩 desc) as row_num
from 班级表;

注意事项：

1. 窗口函数as后面的别名不能跟函数重名，不然会报错。
2. 记得窗口函数over后面加括号。

应用：面试经典排名问题

三、聚合窗口函数

1. 含义

聚合窗口函数和专用窗口函数用法一样，只需把聚合函数放在窗口函数的位置，但是函数后面要指定聚合的列名。

2. sql语句应用

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

针对自身记录，以及自身记录之上的数据进行计算。比如：先按学号排序，current_sum列的每行数据是前面所有行数据的总和。current_max列的每行数据是对比前面所有行数据后取最大值。

3. 作用

可以在每一行的数据里直观的看到，截至到本行数据，统计数据是多少。同时可以看出每行数据，对整体统计数据的影响。

四、经典面试题

1. 排名问题

考察的是rank、dense_rank、row_number的区别。具体如何使用请回顾第二部分的专用窗口函数。

2. topN问题

经常遇到的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？

如果找到每个类别下的用户点击最多的5个商品是什么？

其实就是：分组取每组最大值、最小值，每组最大的N条记录。

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

建立测试表score1：

• 方法1：若使用group by分组和汇总函数，可得每个组的一个值，但是无法取得成绩最大值所在行的数据。需要结合关联子查询来实现。

select *
from score1 as a
where 成绩=(select max(成绩) from score1 as b
            where a.课程号=b.课程号);

• 方法 2：运用窗口函数

select *
from (select *,
      dense_rank() over(partition by 课程号 order by 成绩 desc)as dense_ranking from score1) as a
where dense_ranking=1;

topN问题的万能模板：

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

3. 累计求和问题

案例1：按照雇员编号升序排列，查找薪水的累计薪水。其中累计薪水是前N个当前员工（结束日期=‘9999-01-01‘）薪水的累计和。

select 雇员编号,薪水,
sum（薪水) over (order by 雇员编号) as 累计薪水 
from 薪水表
where 结束时间='1/1/9999';

案例2：下表为确诊人数表，包含日期和该日期对应的新增确诊人数。

按照日期进行升序排列，查找日期、确认人数以及对应的累计确诊人数。

select 日期, 确诊人数,
sum(确诊人数) over (order by 日期) as 累计确诊人数
from 确诊人数表;

累计求和问题的万能模板：

select 列1,列2,
sum（列名) over (order by 用于排序的列名) as 累计值别名 
from 表名;

4. 如何在每个组里比较。

各科成绩表为如下：

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

select 姓名,科目,
avg(成绩) over (partition by 科目) as 平均成绩
from 各科成绩表
where 成绩>平均成绩;

！！！sql报错！！！

原因是在运行顺序中，select字句是最后被运行，该sql是先运行where后select。

正确sql语句写法：

select *
from (select *, avg(成绩) over (partition by 科目) as 平均成绩
from 各科成绩表) as a
where 成绩>平均成绩;

方法二：运用关联子查询。

select 姓名,科目,成绩
from 各科成绩表 a
where 成绩>(select avg(成绩) from 各科成绩表 as b where a.科目=b.科目);

五、窗口函数的移动平均（rows和preceding）

以班级表为测试表。

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

rows和precedin是"之前~行"的意思，得出的current_avg数值是自己记录以及前2行的平均值。譬如：学号0004学号的current_avg是自己和前2位同学的平均，即学号0002、0003、0004三位同学的平均。

业务场景：

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