Oracle数据库的分析函数、层次化查询-- 查询原理：当select后面要查询的结果集为功能函数时，没有进行分组，就默

1、Oracle分析函数

分析函数格式：功能函数() over()

1、over()分析函数 -- 查询每个部门的员工信息，以及其占所有员工工资的百分比 -- 查询原理：当select后面要查询的结果集为功能函数时，没有进行分组，就默认是一个结果。但是要查询多个结果，只能复制功能函数结果， -- over()函数作用：复制功能函数结果， -- 使用over()分析函数进行复制

select e.*,round(sal/sum(sal) over() * 100,2)|| '%' per_sal
from emp e

2、partition by 子句：分区（分组）子句（只能在over() 函数中使用） -- group by: 会影响查询结果集的行数 -- partition by : 不会影响查询结果集的行数 -- 作用，在指定的分区内进行功能函数结果的复制 -- 查询每个部门的员工信息，以及其占本部门员工工资的百分比
```
select e.*,round(sal/sum(sal) over(partition by deptno)*100,2) || '%' per_sal
from emp e;
```
3、 order by 子句：排序子句 -- 附加功能：影响开窗

-- 按照工资进行排序
```
select e.*, sum(sal) over(order by sal)
from emp e;
```

开窗子句： -- 作用：在分区内进行小范围的划分（开窗），来执行功能函数并复制

1-行偏移开窗，只能向上偏移（必须依赖于order by 子句） rows N preceding -- 作用：在一个分区内，从当前行开始向上偏移几行的小范围划定，再执行功能函数并复制结果 -- N 的范围： 0 和正整数 -- 查询当前行往上偏移两行人员的工资总和

select e.*,sum(sal) over(order by sal rows 2 preceding)
from emp e;

-- 例子：资产损益表(算出收入和支出的结余工资) /* 名目收入支出结余上期结余 150000 150000 进货 80000 70000 销售 90000 160000 发工资 60000 100000 */

建表语句

drop table tab;

create table tab(
       prj_date date,
       prj_name varchar2(20),
       in_money number(10,2),
       out_money number(10,2)
);

insert into tab values(to_date('2020-7-1','yyyy-mm-dd'),'上期结余', 150000, null);
insert into tab values(to_date('2020-7-2','yyyy-mm-dd'),'进货', null, 80000);
insert into tab values(to_date('2020-7-3','yyyy-mm-dd'),'销售', 90000, null);
insert into tab values(to_date('2020-7-3 15:16:0','yyyy-mm-dd hh24:mi:ss'),'发工资', null, 60000);
commit;

损益表：使用分析函数

-- 损益表： 使用分析函数
select t.prj_name 名目,t.prj_date 日期,t.in_money 支出,t.out_money 结余,
sum(nvl(in_money,0)) over(order by prj_date rows (select count(1) from tab) preceding) - 
sum(nvl(out_money,0)) over(order by prj_date rows (select count(1) from tab) preceding) 结余
from tab t;

损益表：不使用分析函数

select t.*, (
       select sum(in_money)
       from (
select t.*,rownum in_rn
from(
select t.prj_date , t.prj_name , nvl(t.in_money,0) in_money , nvl(t.out_money,0) out_money
from tab t
order by prj_date) t) t_in
      where in_rn between 1 and rn
) - 
(
       select sum(out_money)
       from (
select t.*,rownum in_rn
from(
select t.prj_date , t.prj_name , nvl(t.in_money,0) in_money , nvl(t.out_money,0) out_money
from tab t
order by prj_date) t) t_in
      where in_rn between 1 and rn
) 结余
from(
select t.*,rownum rn
from(
select t.prj_date , t.prj_name , t.in_money , t.out_money
from tab t
order by prj_date) t) t;

2-数值偏移开窗 range N preceding -- 从当前行向上计算小于当前行工资500以内的工资和 -- 从当前数据行向上或者向下查询比当前工资500以内的工资和
```
select e.*,sum(sal) over(order by sal range 500 preceding)
from emp e;
```

-- 求出工资比当前员工高出500以内的人员个数

select e.*,count(1) over(order by sal desc range 500 preceding)-1
from emp e

-- 求出工资比当前员工高出500以上的人员人数

select e.*,
       count(1) over(order by sal desc range (select max(sal) from emp) preceding)-
       count(1) over(order by sal desc range 500 preceding) 
from emp e;

分析函数中常用的功能函数：

1- 分组函数：sum(); avg(); count(); max(); min()

2- 分级函数 rank() -- 注意：需要order by 子句，会产生跳号

select e.*,rank() over(order by sal)
from emp e;

-- 求出每个部门工资最高的前三个人的信息

select t.* 
from 
( select e.*,rank() over(partition by deptno order by sal desc) rk from emp e) t 
where rk between 1 and 3

-分级函数 DENSE_RANK: 分级函数 -- 注意：不会跳号
```
select e.*, dense_rank() over(order by sal)
from emp e
```

3 FIRST_VALUE 、 LAST_VALUE ：取分组中第一个和最后一个值 -- 注意：如果有order by排序，则last_value 不会取指定分区的最后一个，会收到默认数值偏移开窗的影响
```
select e.*,
first_value(sal) over(partition by deptno order by sal),
last_value(sal) over(partition by deptno)
from emp e
```

4 LAG(col,N) 向上偏移N行， LEAD(col,N) 向下偏移N行 -- 作用：不使用自连接的情况下，跨行取数据 -- 注意：必须指定 order by 子句 select e.*, lag(sal,2) over(order by empno), lead(sal,3) over(order by empno) from emp e
5、NTILE: 分区片 -- 对所有结果进行分页(对数据分4页，再进行选择第几页)
```
select * from (
select e.*,
ntile(4) over(order by sal) page
from emp e )
where page = 4;
```

6、ROW_NUMBER( ) ：添加行序号 -- 注意：需要order by子句

使用ROW_NUMBER()函数进行分页

select * from (
select e.*,
row_number() over(order by sal ,deptno desc) rum
from emp e) 
where rum between 1 and 5

层次化查询

-- 只有层次关系的表才能使用层次化查询语法：

SELECT [LEVEL] column,……
	FROM tableName
	WHERE clause
	START WITH start_condition
	CONNECT BY PRIOR  prior_condition

查询员工和管理者的层次关系

select e.*,level
from emp e
start with empno = 7369
connect by prior mgr = empno

查询员工和管理者的层次关系树状显示 lpad代表左填充

select lpad(ename,level*3+6,' ')
from emp e
start with empno = 7839
connect by prior empno = mgr;

层次化查询的意义：

1- 查询子树 -- 查询 7566 JONES 领导的团队

select lpad(ename,level*3+6,' ')
from emp e
start with empno = 7566
connect by prior empno = mgr;

2- 反向遍历 -- 查询 7788 SCOTT 其领导

select lpad(ename,level*3+6,' ')
from emp e
start with empno = 7788
connect by prior mgr = empno;

好了，我们今天就分享Oracle的分析函数和层次化查询，下期我们继续分享Oracle的视图、索引、存储过程和触发器，期待的小伙伴点赞👍+关注👉我叭。

Oracle数据库的分析函数、层次化查询

1、Oracle分析函数

分析函数格式： 功能函数() over()

层次化查询

分析函数格式：功能函数() over()