sql中的窗口函数

一、简述

窗口函数常用的有排序窗口函数,聚合窗口函数,使用时一般是这种格式

函数名() over(PARTITION by 字段名 ORDER BY 字段名)

二、排序窗口函数

假设有这样一张员工表

现在需要实现这样一种查询效果，将所有员工按照部门分组进行展示,组内的人员按照salary进行升序排列.

这时就要使用排序窗口函数

1.row_number

select *,row_number() over(PARTITION by dept_name ORDER BY salary) as num1 from emp_table;

以上这个sql使用row_number实现把所有员工按部门名分组,组内按salary排序,最终会得到这样的效果

其中num1是使用row_number函数后得到的分组内的行号,这个函数的特点就是行号是递增的,即使两条记录排序字段相等,行号也递增,例如图中的张三和王五这俩人salary相等,num1分别为1和2

2.rank

rank函数也是排序窗口函数作用和row_number类似,只是在生成行号时遇到salary相同的记录时处理方式不同，

select *,rank() over(PARTITION by dept_name ORDER BY salary) as num1 from emp_table;

会得到如下的效果

关注张三和王五这两个人,他们salary一样,所以生成的行号都是1,同时下一个人李四的行号是3,把2跳过了。

3.dense_rank

dense_rank函数也是排序窗口函数作用和row_number类似,只是在生成行号时遇到salary相同的记录时处理方式不同，

select *,dense_rank() over(PARTITION by dept_name ORDER BY salary) as num1 from emp_table;

会得到如下的效果图

关注张三和王五这两个人,他们salary一样,所以生成的行号都是1,但下一个人的行号是2，产生了一个排名并列的效果,这就是和rank函数的区别。

二、聚合窗口函数

现在需要在上边按部门分组的基础上给每一行展示部门内salary的总数和平均数,这时就可以使用sum和avg这两个聚合函数

select *,row_number() over(PARTITION by dept_name ORDER BY salary) as row_num,
sum(salary) over(PARTITION by dept_name) as salary_sum ,
avg(salary) over(PARTITION by dept_name) as salary_avg
from emp_table;

上边的sql会得到这样的效果图,人员按部门分组后每一行记录都带上了本部门的总salary和平均数。

当然还有类似max,min,count这样的聚合函数可以使用。

那么窗口函数中使用的聚合函数和普通使用的聚合函数有什么区别呢?

普通使用的聚合函数最终得到的是一行记录,例如SELECT count(1) from emp_table; 这样得到的是一个全表的总记录数。

而在窗口函数中使用的聚合函数,不会改变返回记录的行数,它会把聚合函数的结果拼在每一行上。

SELECT count(1) over() as total from emp_table;

这个sql会得到这样的结果

这样就可以得到一种查询员工的详细信息同时附带员工总人数的效果

这就是普通聚合函数和窗口聚合函数的区别