您的位置: 首页  >  文章  >  Oracle分析函数(一)

Oracle分析函数(一)

分类: 文章 • 2023-11-16 22:24:04 
   分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行
   许多分析函数同时也是聚合函数，比如sum()函数，这样使用就是聚合函数。

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.翻译值
数据截图：
Oracle分析函数(一)

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

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

结果：
Oracle分析函数(一)

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

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

select t.id,
t.name,
t.age,
decode(sign(t.age - 20),
1,
‘20以上’,
-1,
‘20以下’,
0,
‘正好20’,
‘未知’) as sex
from STUDENT2 t
结果：
Oracle分析函数(一)

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
结果暂无

4.搜索字符串
数据：
Oracle分析函数(一)
需求：找到含有三的姓名

select t.id,
decode(instr(t.name, ‘三’), 0, ‘姓名不含有三’, ‘姓名含有三’) as name,
t.age,
t.sex
from STUDENT2 t
结果：
Oracle分析函数(一)
5.判断是否为空
数据：
Oracle分析函数(一)
需求：性别为空显示“暂无数据”，不为空原样输出

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

结果：
Oracle分析函数(一)

总结：decode在书写sql的时候还是挺有用的，常用的应该是1和5了吧（我猜的，因为我就是经常用这两种）
原文：https://blog.csdn.net/sdut406/article/details/82795585

相关推荐