一篇文章看懂Oracle开窗函数

聚合类开窗函数

聚合类开窗函数类似分组函数group by中的sum()、avg()、count()、max()、min()等等。但是开窗函数不会像分组聚合函数一样按照分组返回结果，而是有多少行记录就返回多少个结果，结果输出的形式是单独一列进行输出。

举个例子：

这是普通的聚合函数

SELECT AVG(salary) FROM employees

这是最简单的聚合开窗函数

SELECT AVG(salary) over() FROM employees

从上面的例子可以看出，如果使用普通的聚合函数，在select后面加入其他列名，会提示“ORA-00937 不是单组分组函数”。

但是使用了开窗函数就可以为每行记录都匹配一个结果。

 

再举一个例子：

要求：查询姓名，部门ID，工资，部门的平均工资

如果用group by去实现的话，非常麻烦

首先要在子查询中求出每个部门的平均工资，然后再去和主查询进行关联，才能输出结果

SELECT last_name, emp.department_id, salary, emp.dept_avg_sal

FROM   employees e,

       (SELECT department_id, AVG(salary) dept_avg_sal

        FROM   employees

        GROUP  BY department_id) emp

WHERE  e.department_id = emp.department_id

ORDER  BY department_id;

如果使用开窗函数就非常容易实现了

只要在开窗函数中对部门ID进行分区就可以实现每一行查询结果都显示部门的平均工资

SELECT last_name,

       department_id,

       salary,

       AVG(salary) over(PARTITION BY department_id) dept_avg_sal --相同部门ID的记录，在一个窗口中（窗口的概念后面会讲）

FROM   employees

接下来我们通过几个例子来了解下开窗函数的组件作用

例1：查询每个雇员与自己薪水上下相差50元-150元之间的雇员数。

SELECT last_name,

       salary,

       COUNT(*) over(ORDER BY salary RANGE BETWEEN 50 preceding AND 150 following) mov_count

FROM   employees;

Order by salary代表我们按照工资去进行排序，默认是是升序的，range between 50 preceding and 150 following代表取值的范围，也就是说我们要count的数量范围是比当前记录低50元和高150元区间内，这个50和150就是窗口的大小。

如果order by后面跟desc进行降序排列呢？那这个范围区间就改改过来变成range between 150 preceding and 50 following才行。

 

例2：查询50号部门中，每个员工比自己早入职的5个人和后入职的5个人的工资平均值，看看自己和他们之间的差距。

SELECT last_name,

       department_id,

       hire_date,

       salary,

       AVG(salary) over(PARTITION BY department_id ORDER BY hire_date rows BETWEEN 5 preceding AND 5 following) avg_sal

FROM   employees

WHERE  department_id = 50;

首先我们在开窗函数中使用了partition by对部门id进行分组，然后使用入职日期进行排序，这样就可以根据当前行的记录取这条记录的员工比他早入职的5个人和晚入职的5个人了。

在这个例子中我们使用了rows between这个子句，它的range between的区别是它的取值范围是前5条和后5条记录，而不是一个取值范围。5 preceding和5 following代表窗口的大小是5行记录。

 

例3：无边界窗口的演示

SELECT last_name,

       hire_date,

       salary,

       AVG(salary) over(ORDER BY hire_date rows BETWEEN unbounded preceding AND unbounded following) c_mavg

FROM   employees;

在例3中，我们对上一个例子做了一些修改，没有使用partition进行分区，直接按照入职日期进行排序，由于使用了unbounded这个子句则对于每一条记录而言，preceding和following是没有边界值的。所以这个例子中聚合开窗函数求的是全员的平均工资 avg(salary)。

 

 

接下来我们通过一个例子演示一下聚合类开窗函数的应用

应用例子：查询大于自己部门平均工资的员工信息，显示department_id,last_name,hire_date,salary,部门平均工资.

WITH dept_sal AS --建立一个临时表dept_sal

 (SELECT department_id,

         last_name,

         hire_date,

         salary,

         AVG(salary) over(PARTITION BY department_id ORDER BY salary rows BETWEEN unbounded preceding AND unbounded following) avg_sal --求出分组中工资的平均值 用一列显示

  FROM   employees)

SELECT department_id, last_name, hire_date, salary, avg_sal

FROM   dept_sal

WHERE  salary > avg_sal --用临时表中的salary列比对avg_sal列

 

---

排序类开窗函数

RANK--序列集排序，相等值的行排名相同

--如果排名中，有相同的值，那么排名并列存在

--有几个并列存在的人，下一个排名就会顺延几个整数

SELECT e.department_id,

       d.department_name,

       e.last_name,

       e.salary,

       rank() over(PARTITION BY e.department_id ORDER BY e.salary) drank

FROM   employees e, departments d

WHERE  e.department_id = d.department_id

       AND d.department_id IN (30, 40, 50);

DENSE_RANK--稠密排名

计算序组中的行的排名，排名从1开始的连续整数。

--按照department_id进行分组，salary进行排序

--关联员工表和部门表，关联条件department_id

--筛选出30 40 50三个部门的排名情况，以d_rank列输出排名情况

--遇到重复值全部并列进行排名

SELECT e.department_id,

       d.department_name,

       e.last_name,

       e.salary,

       dense_rank() over(PARTITION BY e.department_id ORDER BY e.salary) d_rank

FROM   employees e, departments d

WHERE  e.department_id = d.department_id

       AND d.department_id IN (30, 40, 50);

ROW_NUMBER

--给你已定的排序组中，从1开始为每一行分配一个唯一的数字。

--不会有并列

SELECT e.department_id,

       d.department_name,

       e.last_name,

       e.salary,

       row_number() over(PARTITION BY e.department_id ORDER BY e.salary) drank

FROM   employees e, departments d

WHERE  e.department_id = d.department_id

       AND d.department_id IN (30, 40, 50);

 

rank,dense_rank,row_number的比较：

SELECT employee_id,

       last_name,

       department_id,

       salary,

       rank() over(PARTITION BY department_id ORDER BY salary) "Rank",

       dense_rank() over(PARTITION BY department_id ORDER BY salary) "D_rank",

       row_number() over(PARTITION BY department_id ORDER BY salary) "Row_number"

FROM   employees

WHERE  department_id = 50;

 

ntile() 平均分组函数

--将30号部门的人，按照工资高低分为三个档

--将30号部门的人，按照工资高低分为三个档

SELECT last_name,

       department_id,

       salary,

       ntile(3) over(PARTITION BY department_id ORDER BY salary) level_sal

FROM   employees

WHERE  department_id = 30;