Oracle复杂数据处理

一. 聚合函数

1.求最大值 max()

2.求最小值 min()

3.求平均值 avg()

4.求和 sum()

5.统计记录数 count()

二. 常用技巧函数

1.多值判断 decode()

decode(表达式,比较值1,结果值1,比较值2,结果值2,...,默认值)

eg. select decode(job,'CLERK','1','SALESMAN','2','3') from emp;

2.空值处理 nvl()

nvl(表达式,新值/表达式)

3.空值处理 nvl2()

nvl(表达式,不为空设值,为空设值)

4.结果集的行号 rownum()

(1) 每捕获一条记录,rownum()函数的返回值+1

(2) rownum()始终从1开始,当rownum()>n[n>1]时,返回结果永远为空.

eg. select * from (select ename,rownum rn from emp) where rn>3;

select a.*,rownum from (select * from emp order by sal) a where rownum<4;

5.强制转换数据类型 cast()

cast(原数据 as 新的数据类型)

三. 运算

1.运算:+,-,*,/

2.逻辑运算:>,<,>=,<=,=,<>,!=,not,and,or

四.特殊判式

1.范围测试 between

2.集合成员测试 in

3.模糊匹配 like

单个字符'_',任意字符'%'

原意字符:escape

eg. select 1 from dual where '10%' like '%\%' escape '\';

4.空值判断 is null

5.存在性判断 exists

eg. select * from dept where exists(

select 1 from emp a,dept b where a.deptno=b.deptno);

6.集合判断 all,some,any

some和any判式可以返回相同的结果,两者使用的差别在使用场景上,通常说,当使用"="等肯定形式判断时,用some;用">"等判式时使用any.

eg. select ename from emp where sal> all(

select sal from emp where job='SALESMAN');

五. 分析函数和窗口函数

1.排名函数rank(),dense_ranl(),row_number()

(1)rank()

rank结果具有重复性[并列第二]和跳跃性[例如有2个第二名,则下一位是第四名]

eg. select ename,rank() over(order by sal) emp_rank from emp;

(2)dense_rank()

dense_rank()结果有重复性[并列第二],不具有跳跃性.

eg. select ename,dense_rank() over(order by sal) emp_rank from emp;

(3)row_number()

row_number不具有重复性,不会出现并列情况

eg. select ename,row_number() over(order by sal) emp_rank from emp;

2.分区窗口 partition by 列名

eg. select ename,job,avg(sal) over(partition by job order by empno) avg from emp;

3.窗口子句

(1)rows --以相对位置作为获取记录的标准

over(order by 列名 rows between 位移量 preceding and 位移量 following)

使用rows的前提是窗口利用order by排序,然后则可以通过rows按照位置进行向上or向下追溯

eg. select ename,sum(sal) over(order by sal rows between 1 preceding and 1 following) sum from emp;

(2)range --以相对列值作为筛选记录的标准

over(order by 列名 range between 差值 preceding and 差值 following)

使用range追溯的范围是当前列值的差值

eg. select ename,deptno,count(1) over(order by deptno range between 10 preceding and 10 following) count from emp order by deptno;

(3)current row和unbounded

current row和unbounded可以代替数值,其中current row代表到当前记录[current row后不用接preceding和following],unbounded代表不受限制的窗口范围[代表第一条数据,或者最后一条数据的位置].

eg. select ename,count(1) over(order by ename rows between unbounded preceding and current row) count from emp order by ename;

六. 常用分析函数

1.first_value() --返回第一条记录的信息

eg. 取出emp表中每职位工资第一人的姓名

select distinct job,first_value(ename) over(partition by job order by sal desc) ename from emp;

2.last_value() --返回最后一条记录的信息

eg. last_value和rows一起用

select ename,last_value(sal) over (order by sal rows between unbounded preceding and current row) sal from emp;

3.lag() --按照当前记录为坐标,按照特定位移向上搜索

lag(列名or列的表达式,位移,默认值)

eg. select ename,lag(ename,1,null) over(order by ename) lag from emp;

4.lead() --同lag,向下位移

ITPub博客

oracle复杂函数处理

最新文章