时间戳

首页 > 技术文章 > 《Oracle查询优化改写技巧与案例》学习笔记-------使用数字篇

《Oracle查询优化改写技巧与案例》学习笔记-------使用数字篇

fenyangyang 2018-11-08 16:46 原文

 

一个系列的读书笔记，读的书是有教无类和落落两位老师编写的《Oracle查询优化改写技巧与案例》。

用这个系列的读书笔记来督促自己学习Oracle，同时，对于其中一些内容，希望大家看到以后，可以留下自己的想法。以此交流。

这篇随笔主要记录的是在Oracle查询过程中对数字的使用具体分为九个部分

1.常用聚集函数

select deptno,
min(sal) as 最小值,
max(sal) as 最大值,
sum(sal) as 工资合计,
count(sal) as 计数,
avg(sal) as 错误平均值,
avg(coalesce(sal,0)) as 正确平均值 
from emp group by deptno

正确平均值和错误平均值的意义：
聚集函数会忽略空值，对sum不会造成影响，但是对avg，connt会造成影响，所以根据需求决定是否需要把空值转换为0。

注意：当表中没有数据时，不加group by会返回一条数据，加了group by没有数据返回。
验证过程：创建表emp2

create table emp2 as select * from emp where 1 = 2

 

select count(*) from emp2 group by deptno

没有group by ：0

 


有group by：空值。

 

所以在实际使用过程中要注意group by 的位置。

2.生成累计和

案例：公司为了查看用人成本，需要对员工的工资进行累加，以便查看员工人数和工资支出之间对应的关系

以员工编码(empno)进行排序，累加查看，查看部门编码为 2的工资支出

select empno,empname,sal,sum(sal) over(order by empno) from emp where deptno = '2' order by empno

详细看一下每一条数据的具体组成

select
empno,
empname,
sal,
sum(sal) over(order by empno),
(select listagg(sal,'+') within group(order by empno)
from emp b
where b.deptno = '2'
and b.empno <= a.empno) 计算公式
from emp a
where deptno = '2' order by empno

效果如下：

3.计算累计差

案例：创建一个虚拟的流水账，比如有30000的预算，然后有各种活动支出，每次支出完需要统计余额。

创建测试用表：

create table detail as
select 1000 as 编号,'预交费用' as 项目,30000 as 金额 from dual;

在测试用表插入数据：

insert into detail 
select empno as 编号,'支出' || rownum as 项目,sal+1000 as 金额
from emp where deptno = '2'

detail表中为消费流水账的内容。

方案：

（1）一般流水账的编号都是按顺序生成的，我们根据编号排序并生成序号。

select rownum as seq,a.* from (select 编号,项目,金额 from detail order by 编号 desc) a

(2)观察查询结果  seq = 1 的为收入，后面的为支出，可以用case when把后面的数据变为负数

with x as
(select rownum as seq,a.* from (select 编号,项目,金额 from detail order by 编号 desc) a )
select  编号,项目,金额,(case when seq = 1 then 金额 else -金额 end) as 转换后的值 from x;

（3）把转换后的结果进行相加，可以得到差值

with x as
(select rownum as seq,a.* from (select 编号,项目,金额 from detail order by 编号 desc) a )
select  编号,项目,金额,sum(case when seq = 1 then 金额 else -金额 end) over(order by seq) as 余额 from x;

4.更改累计和的值

创建测试视图

create or replace view v(id,amt,trx)
as
select 1,100,'PR' FROM DUAL UNION ALL
select 2,300,'PR' FROM DUAL UNION ALL
select 3,150,'PY' FROM DUAL UNION ALL
select 4,50,'PY' FROM DUAL UNION ALL
select 5,200,'PY' FROM DUAL UNION ALL
select 6,100,'PR' FROM DUAL UNION ALL
select 7,300,'PY' FROM DUAL UNION ALL
select 8,400,'PR' FROM DUAL ;

创建的视图内容为一个存取款列表：

       id是唯一列。

       amt表示每次事务处理设计的金额。

       trx列定义了事务处理的类型，取款是"PY"，存款是"PR"。

（1）把取款值变为负数

select id,
       case when trx = 'PY' THEN '取款' else '存款' end 存取类型,
         amt 金额,
         case when trx = 'PY' THEN AMT ELSE -amt end 变更后的值
         from v order by id

（2）把变更后的值进行相加

select id,
case when trx = 'PY' then '取款' else '存款' end 存取类型,
  amt 金额,
  sum(case when trx = 'PY' THEN AMT ELSE -AMT END) over(order by id) 余额
  from v
  order by id;

5.返回各部门工资排名前三位的员工

select deptno,
       empno,
       sal,
       row_number() over(partition by deptno order by sal desc) as row_number,
       rank() over(partition by deptno order by sal desc) as rank,
       dense_rank() over(partition by deptno order by sal desc) as dense_rank
       from emp
       where deptno in (2,3)
       order by 1,3 desc;

partition by:会把主查询返回的子句分组进行分析。观察查询结果，对子句进行部门分组以后，部门为2的生成序列以后，部门为3的部门生成序列时，会重新进行分组。
当工资有重复项时，观察row_number,rank,dense_rank的区别
row_number:仍然会生成序号1、2、3
rank:相同的工资会生成相同的序号，而且其后的序号与row_number相同，即1，1，3，3，5
dense_rank:相同的工资会生成相同的序号，其后的序号会递增，即1，1，2，3，3，4

 6.计算出现次数最多的值：
案例要求：查看部门中哪个工资等级的员工最多
(1)计算不同工资出现的次数

select sal,count(*) as 出现次数 from emp where deptno = 2 group by sal order by sal

(2)按次数排序生成序号

select sal,dense_rank() over(order by 出现次数 desc) as 次数排序
from (select sal,count(*) as 出现次数 from emp where deptno = 2 group by sal)x;

(3)根据序号过滤得到需要的结果

select sal
from (select sal,dense_rank() over(order by 出现次数 desc)as 次数排序 from (select sal,count(*) as 出现次数 from emp where deptno = 2 group by sal)x)y
where 次数排序 = 1

(4)利用partition by子句分别查询各部门哪个工资等级的员工最多

select deptno,sal
from (select deptno, sal,dense_rank() over(partition by deptno order by 出现次数 desc)as 次数排序 
from (select sal,deptno,count(*) as 出现次数 from emp group by sal,deptno)x)y
where 次数排序 = 1

7.返回最值所在行数据

方案1：
标量查询：先取出最大值，再和最大值进行关联，思路简单，sql复杂

select 
a.empname as 工资最高的人,a.deptno,a.sal,a.max_sal
from (
select max(sal) over(partition by deptno) as max_sal,empno,sal,empname,deptno from emp) a
where sal = a.max_sal

方案2：
分析函数：在Oracle里有分析函数可以直接满足这个需求，而且还可以方便的同时取最大值和最小值

select 
deptno,
empno,
max(empname) keep(dense_rank first order by sal) over(partition by deptno) as 工资最低的人,
max(empname) keep(dense_rank last order by sal) over(partition by deptno) as 工资最高的人,
empname,
sal
from emp
order by 1,6 desc

first、last语句也可以放在group里与其他聚合函数一样使用，这是要去掉后面的over(partition by xxx)

select 
deptno,
min(sal) as min_sal,
max(empname) keep(dense_rank first order by sal)  as 工资最低的人,
max(sal) as max_sal,
max(empname) keep(dense_rank last order by sal)  as 工资最高的人
from emp
group by deptno

在第一个分析函数的语句中，不论是first,还是last，都用聚合函数MAX，分析一下MAX的作用

select
deptno,
empno,
max(sal) over(partition by deptno) as 最高工资,
empname,
sal
from emp
where deptno = 3
order by 1,5 desc



根据表中的数据，工资最高的有两个人，加上first和last语句

select deptno,
empno,
empname,
sal,
to_char(wmsys.wm_concat(empname) keep(dense_rank last order by sal) over(partition by deptno)) as 工资最高的人,
min(empname) keep(dense_rank last order by sal) over(partition by deptno) as 工资最高的人min,
max(empname) keep(dense_rank last order by sal) over(partition by deptno) as 工资最高的人max
from emp
where deptno = 3
order by 1,4 desc

编码为3的部门工资最高的有两个，通过查询结果，可以看到keep()得到的结果包含两个人名字，所以通过min和max可以取到不同的值。
8.first_value
用first_value 和 last_value 来替换 first 和 last

select
deptno,
empno,
first_value(empname) over(partition by deptno order by sal desc) as 工资最高的人,
empname,
sal
from emp where deptno = 3
order by 1,5 desc

上面sql的结果没有问题

9.求总和的百分比
需求：计算各部门的工资合计，及该合计工资占总公司的比例
(1)分组汇总

select deptno,sum(sal) 工资合计 from emp group by deptno

(2)通过分析函数获取总合计

select deptno,工资合计,sum(工资合计) over() as 总合计
from (select deptno,sum(sal) 工资合计 from emp group by deptno) x;

(3)通过前两步的结果计算

select
deptno,
工资合计,
round((工资合计/总合计)*100,2) as 工资比例
from (
select deptno,工资合计,sum(工资合计) over() as 总合计
from (select deptno,sum(sal) 工资合计 from emp group by deptno) x) y;

也可以用专门的比例函数“ratio_to_report”

select deptno,
round(ratio_to_report(工资合计)over() * 100,2) as 工资比例
from(select deptno,sum(sal) 工资合计 from emp group by deptno)
order by 1

同其他分析函数一样，可以使用partition by 分组计算，如查询各员工占本部门的工资比例：

select deptno,
empname,
sal,
round(ratio_to_report(sal) over(partition by deptno)*100,2) as 工资比例
from emp
order by 1,2

 

推荐阅读