Oracle学习日记——使用数字（oracle数据库number）

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没有数据返回。

验证过程：

建立空表：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.生成累计和

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

首先，按照进入公司的先后顺序进行查看：

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

用listagg()来查看每个值具体是由哪些值相加

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.计算累计差

创建项目费用表

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

MySQL学习系列之十——联结

MySQL学习系列之九——子查询

MySQL学习系列之八——分组数据

MySQL学习系列之七——用聚集函数汇总数据

MySQL案例使用表建表以及插入数据语句