Oracle分析(窗口)函数_oracle 开窗函数能分组

Oracle分析(窗口)函数

参考：https://blog.51cto.com/u_16147578/6396047

分析(窗口)函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数。

它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

1、分析函数语法

function_name(<argument>,<argument>...)
over(<partition_Clause>
    <order by_Clause>
    <windowing_Clause>);
1
2
3
4

• function_name()：函数名称
• argument：参数
• over( )：开窗函数
• partition_Clause：分区子句，数据记录集分组，group by…
• order by_Clause：排序子句，数据记录集排序，order by…
• windowing_Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying

注：使用开窗子句时一定要有排序子句！！！

2、常见分析函数的使用说明

数据案例使用Oracle的scott用户下自带的emp表和dept表

（1）、聚合、累计类型分析函数使用

--(1)count(...) over(...) ;求各部门员工数
--join方式
select 
distinct e.deptno deptno,
d.dname dname,
count(*)over(partition by e.deptno,d.dname) totar  
from scott.emp e join scott.dept d on e.deptno=d.deptno;
--如果不需要部门名称
select distinct deptno,
count(1) over(PARTITION by deptno) total
from emp;
1
2
3
4
5
6
7
8
9
10
11

--(2)sum(...) over(...);求各部门员工递加的工资总和
select 
ename,
deptno,
sum(sal) over(partition by deptno order by ename) sum_sal 
from scott.emp;
1
2
3
4
5
6

--(3)avg(...) over(...);求各部门的平均工资
select 
distinct deptno,
avg(sal)over(partition by deptno) avg_sal 
from scott.emp;
1
2
3
4
5

--（4）min(...)/max(...)over(...);求各职位的最低和最高薪资
select
distinct job,
min(sal)over(partition by job) min_sal,
max(sal)over(partition by job) max_sal 
from scott.emp;
1
2
3
4
5
6

（2）、排名类型分析函数使用

--(1)、整体排名：rank()/row_number()/dense_rank() over(...);按照薪资降序整体排名 
select emp.* , 
rank()over(order by sal desc) rank, --占空排名，跳跃排名，如 1-2-2-4-5
row_number()over(order by sal desc) row_number,--顺序递增（减）排名，如 1-2-3-4-5
dense_rank()over(order by sal desc) dense_rank --不占空排序，如1-2-2-3-4
from scott.emp;
1
2
3
4
5
6

--(2)、组内排名：rank()/row_number()/dense_rank() over(...);按照各部门内部薪资降序排名 
select emp.* , 
rank()over(partition by deptno order by sal desc) rank, --占空排名，跳跃排名，如 1-2-2-4-5
row_number()over(partition by deptno order by sal desc) row_number,--顺序递增（减）排名，如 1-2-3-4-5
dense_rank()over(partition by deptno order by sal desc) dense_rank --不占空排序，如1-2-2-3-4
from scott.emp;
1
2
3
4
5
6

（3）lag(col,n)、lead(col,n)、ntile(n) 、first_value（）、last_value（） 分析函数的使用

• lag(列名,往前的行数n,[行数为null时的默认值，不指定为null]) --往前第n行
• lead(列名,往后的行数n,[行数为null时的默认值，不指定为null]) --往后第n行
• first_value取分组内排序后，截止到当前行，第一个值。
• last_value取分组内排序后，截止到当前行，最后一个值。
• ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号

--(2)、first_value()、last_value()的使用
select ename,first_value(salary) over() from scott.emp;
select ename,first_value(salary) over(order by sal desc) from scott.emp;
select ename,first_value(salary) over(partition by job) from scott.emp;                                                           
select ename,first_value(salary) over(partition by job order by sal desc) from scott.emp;
 
select ename,last_value(ename) over() from scott.emp;
select ename,last_value(ename) over(order by sal desc) from scott.emp;
select job,ename,last_value(ename) over(partition by job) from scott.emp;
select job,ename,sal,last_value(ename) over(partition by job order by sal desc) from scott.emp;
1
2
3
4
5
6
7
8
9
10

--(3)、ntile(n)使用
-- 按照薪资降序整体分成3组
select ename,sal,ntile(3) over(order by sal desc) from scott.emp;
-- 给个部门按照薪资降序分为2组
select deptno,ename,sal,ntile(2) over(partition by deptno order by sal desc) from scott.emp;
1
2
3
4
5