Oracle分析函数(一) - 迅易阁资讯

Oracle分析函数(一)

2024-12-29 06:31

SQL> select deptno,sum(sal) sum_sal fromemp group by deptno;
而这样使用就是分析函数。
SQL> select distinct deptno,sum(sal)over(partition by deptno) sum_sal from emp;
它们得出的结果是相同的，都是：
1 10 8750
2 20 10875
3 30 9400
请注意，这里我们用到了***distinct*** 关键字，如果不用distinct，第2个查询将返回14行数据，即emp表的每行记录都将返回一行sum_sal，因为不用distinct的含义是：针对每个雇员计算他/她所在的部门的薪金总数。
SQL> select deptno,sum(sal) over(partition by deptno) sum_sal from emp
1 10 8750
2 10 8750
3 10 8750
4 20 10875
5 20 10875
6 20 10875
7 20 10875
8 20 10875
9 30 9400
10 30 9400
11 30 9400
12 30 9400
13 30 9400
14 30 9400

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。
下面通过几个实例来介绍部分分析函数的用途。

问题1：求出每个部门工资最高的前3名。
利用我们传统的聚合函数max可以方便地取出工资最高的一个员工，但是取出多个就无能为力了，同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个部门的分组。而采用rank聚合函数，可以方便地实现我们的要求。

完整的语句如下:
select * from (
select deptno,sal,ename, rank()over(partition by deptno order by sal desc) pm from emp)
where pm<=3

结果为：
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1
5 20 FORD 3000.00 1
6 20 JONES 2975.00 3
7 30 BLAKE 2850.00 1
8 30 ALLEN 1600.00 2
9 30 TURNER 1500.00 3

我们在开窗函数中使用deptno(部门编号)作为分组标志,并按照sal(工资)倒序排列。
注意:RANK()函数有3组,分别是rank, dense_rank, row_number,它们的区别是：
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4；而dense_rank则不会跳过这个排名,结果是1,2,2,3；而row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4

dense_rank()
完整的语句如下：
select * from (selectdeptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) pmfrom emp) t where pm<=3
//按部门编号根据工资由高到低排序
结果：
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1
5 20 FORD 3000.00 1
6 20 JONES 2975.00 2
7 20 ADAMS 1100.00 3
8 30 BLAKE 2850.00 1
9 30 ALLEN 1600.00 2
10 30 TURNER 1500.00 3

Rank()
完整的语句如下：
select * from (selectdeptno,ename,sal,rank() over(partition by deptno order by sal desc) pm fromemp) t where pm<=3
//按部门编号根据工资由高到低排序
结果：
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1
5 20 FORD 3000.00 1
6 20 JONES 2975.00 3
7 30 BLAKE 2850.00 1
8 30 ALLEN 1600.00 2
9 30 TURNER 1500.00 3

Row_number()
完整的语句如下：
select * from (selectdeptno,ename,sal,row_number() over(partition by deptno order by sal desc) pmfrom emp) t where pm<=3
//按部门编号根据工资由高到底排序
结果：
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1
5 20 FORD 3000.00 2
6 20 JONES 2975.00 3
7 30 BLAKE 2850.00 1
8 30 ALLEN 1600.00 2
9 30 TURNER 1500.00 3

nvl:
NVL(eExpression1, eExpression2)
例子：NVL(T1.IS_INVESTMENT, 0) IS_INVESTMENT,
参数
eExpression1, eExpression2 两者参数类型要一致

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.

说明

重点：在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。
select nvl(a.name,‘空得’) as name from student a join school b on a.ID=b.ID
注意：两个参数得类型要匹配

decode:
作用的相当于数据表展示的时候可以把某一字段值替换成你想表达的值
https://blog.csdn.net/sdut406/article/details/82795585
decode含义
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
这个是decode的表达式，具体的含义解释为：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　…
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

decode的用法
这里主要说的就是decode的用法，在很多时候这个函数还是很有用的。

需求：查询出的数据，1表示男生，2表示女生

select t.id,
t.name,
t.age,
decode(t.sex, ‘1’, ‘男生’, ‘2’, ‘女生’, ‘其他’) as sex
from STUDENT2 t

2.decode比较大小
说明：sign(value)函数会根据value的值为0，正数，负数，分别返回0，1,-1
数据：

需求：年龄在20以上的显示20以上，20以下的显示20以下，20的显示正好20

3.decode分段
数据暂无
需求：工资大于5000为高薪，工资介于3000到5000为中等，工资小于3000为底薪

select name,
sal,
decode(sign(sal - 5000),
1,
‘高薪’,
0,
‘高薪’,
-1,
decode(sign(sal - 3000), 1, ‘中等’, 0, ‘中等’, -1, ‘低薪’)) as salname
from person
结果暂无

select t.id,
t.name,
t.age,
decode(t.sex,NULL,‘暂无数据’,t.sex) as sex
from STUDENT2 t