专题一:窗口函数

一.窗口函数有什么用？

在业务需求时候会需要对组内进行排序，或者输出对一个部门薪水排名前几的人员情况

二.什么是窗口函数？

专业话语：窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

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

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

三.窗口函数要如何使用

不说一堆大白话，直接用例子来举例说明吧！！！

1.以rank为例

--1.创建学生表
drop table department ;
create table department(
staff_id int ,-- 员工编号
 dept_id int ,--部门编号
 salary float--员工薪水
);
--2.插入员工信息
INSERT into department values (01,1,6000),(02,1,7500),(03,2,6500),(05,1,7600),(05,2,8100),(06,3,7700),(07,3,9800),(08,1,11000),(09,2,6000),(10,1,6000),(11,2,8100);
--3.查看student
SELECT * from department ;
-- 窗口函数
/*
 * select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表
 */
 -- rank窗口函数
select *,RANK() over (PARTITION by dept_id  ORDER by salary  desc) as ranking 
 from department ;
 -- 对dept_id进行分组
SELECT d.dept_id,COUNT(*) 
FROM  department  d
group by d.dept_id  
ORDER by d.dept_id  desc ;

 

查询结果如图所示：partition by dept_id 和group by dept_id都是对部门进行分组但是不同的是partition by进行分组是对整张表而言，而group by 是对dept_id部门进行分组后的结构进行排序

question：窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法如下：

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

2.其他专业窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢?

它们的区别我举个例子，你们一下就能看懂：

select *,
   rank() over (order by salary  desc) as ranking,
   dense_rank() over (order by salary desc) as dese_rank,
   row_number() over (order by salary desc) as row_num
from department  ;

 

结果如上图所示：

rank：3、3、5如果salary相同情况下会占用下一个名次。

Dese_rank:3、3、4如果salary相同情况不会占用下一个名次

Row_num:3、4、5如果salary相同情况依然会把其中一个作为下一个名次使用

3聚合函数作为窗口函数使用

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

我们来看一下窗口函数是聚合函数时，会出来什么结果：

select *,
   sum(d.salary) over (order by d.staff_id) as current_sum,
   avg(d.salary) over (order by d.staff_id) as current_avg,
   count(d.salary) over (order by d.staff_id) as current_count,
   max(d.salary) over (order by d.staff_id) as current_max,
   min(d.salary) over (order by d.staff_id) as current_min
from department d;

 

如图所示：

sum（）函数：是对自身，及其以上的salary进行求和，比如1号员工current_sum=6000，2号员工current_sum=6000+7500，3号员工current_sum=6000+7500+6500..............

avg()函数：也是对自身，及其以上的salary进行求评价薪水，比如1号员工current_avg=6000，2号员工current_avg=（6000+7500）/2，3号员工current_avg=（6000+7500+6500）/3...........

max()函数:也是自身，及其以上的salary进行找最大薪水，比如1号员工current_max=6000,2号从6000和7500中找出最大为7500，3号员工从6000，7500，6500中找到最大值7500...........

min（）函数:原理同上max()函数

4.这样使用窗口函数有什么用呢？

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

注意事项：partition子句可是省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：

select *,
  rank() over (order by salary desc) as ranking
from departition