oracle分析函数 汇总,Oracle高效的SQL语句之分析函数汇总

如果我们按照示例想得到每个部门薪水值最高的雇员的纪录，可以有四种方法实现：

先创建示例表

create

table

emp

as

select

*

from

scott.emp;

alter

table

emp

add

constraint

emp_pk

primary

key

(empno);

create

table

dept

as

select

*

from

scott.dept;

alter

table

dept

add

constraint

dept_pk

primary

key

(deptno);

方法1.emp中的每一行都会进行max比较，费时

select

*

from

emp emp1

where

emp1.sal

=

(

select

max

(emp2.sal)

from

emp emp2

where

emp2.deptno

=

emp1.deptno)

方法2.先子查询查找出max sal，然后与emp表相关联，如果逻辑复杂会产生较多代码

select

*

from

emp emp1,(

select

deptno,

max

(sal) maxsal

from

emp emp2

group

by

emp2.deptno) emp3

where

emp1.deptno

=

emp3.deptno

and

emp1.sal

=

emp3.maxsal

方法3.使用max分析函数

select

deptno,maxsal,empno

from

(

select

max

(sal)

over

(partition

by

deptno) maxsal,emp.

*

from

emp) emp2

where

emp2.sal

=

emp2.maxsal

方法4.使用dense_rank分析函数，如果一个部门可能存在多笔最大薪水，就不能使用row_number()分析函数

select

deptno,sal,empno

from

(

select

emp.

*

,DENSE_RANK()

over

(partition

by

deptno

order

by

sal

desc

) rownumber

from

emp) emp2

where

rownumber

=

1

结果如下：

10

5000.00

7839

20

3000.00

7788

20

3000.00

7902

30

2850.00

7698

[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()

有些时候我们希望得到指定数据中的前n列，示例如下：

得到每个部门薪水最高的三个雇员：

先创建示例表

create

table

emp

as

select

*

from

scott.emp;

alter

table

emp

add

constraint

emp_pk

primary

key

(empno);

create

table

dept

as

select

*

from

scott.dept;

alter

table

dept

add

constraint

dept_pk

primary

key

(deptno);

先看一下row_number() /rank()/dense_rank()三个函数之间的区别

select

emp.deptno,emp.sal,emp.empno,row_number()

over

(partition

by

deptno

order

by

sal

desc

) row_number,

--

1,2,3

rank()

over

(partition

by

deptno

order

by

sal

desc

) rank,

--

1,1,3

dense_rank()

over

(partition

by

deptno

order

by

sal

desc

) dense_rank

from

emp

--

1,1,2

结果如下:

10

5000.00

7839

1

1

1

10

2450.00

7782

2

2

2

10

1300.00

7934

3

3

3

20

3000.00

7788

1

1

1

20

3000.00

7902

2

1

1

20

2975.00

7566

3

3

2

20

1100.00

7876

4

4

3

20

800.00

7369

5

5

4

30

2850.00

7698

1

1

1

30

1600.00

7499

2

2

2

取每个部门的薪水前三位雇员：

select

t.deptno,t.rank,t.sal

from

 (

select

emp.

*

,row_number()

over

(partition

by

deptno

order

by

sal

desc

) row_number,

--

1,2,3

rank()

over

(partition

by

deptno

order

by

sal

desc

) rank,

--

1,1,3

dense_rank()

over

(partition

by

deptno

order

by

sal

desc

) dense_rank

from

emp

--

1,1,2

) t

where

t.rank

<=

3

结果如下：

10

1

5000.00

10

2

2450.00

10

3

1300.00

20

1

3000.00

20

1

3000.00

20

3

2975.00

30

1

2850.00

30

2

1600.00

30

3

1500.00

如果想输出成deptno  sal1   sal2   sal3这种类型的格式

步骤一(decode)：

select

t.deptno,decode(row_number,

1

,sal) sal1,decode(row_number,

2

,sal) sal2,decode(row_number,

3

,sal) sal3

from

 (

select

emp.

*

,row_number()

over

(partition

by

deptno

order

by

sal

desc

) row_number,

--

1,2,3

rank()

over

(partition

by

deptno

order

by

sal

desc

) rank,

--

1,1,3

dense_rank()

over

(partition

by

deptno

order

by

sal

desc

) dense_rank

from

emp

--

1,1,2

) t

where

t.rank

<=

3

结果如下：

10

5000

10

2450

10

1300

20

3000

20

3000

20

2975

30

2850

30

1600

30

1500

步骤二(使用聚合函数去除null,得到最终结果)：

select

t.deptno,

max

(decode(row_number,

1

,sal)) sal1,

max

(decode(row_number,

2

,sal)) sal2,

max

(decode(row_number,

3

,sal)) sal3

from

 (

select

emp.

*

,row_number()

over

(partition

by

deptno

order

by

sal

desc

) row_number,

--

1,2,3

rank()

over

(partition

by

deptno

order

by

sal

desc

) rank,

--

1,1,3

dense_rank()

over

(partition

by

deptno

order

by

sal

desc

) dense_rank

from

emp

--

1,1,2

) t

where

t.rank

<=

3

group

by

t.deptno

结果如下：

10

5000

2450

1300

20

3000

3000

2975

30

2850

1600

1500

[Oracle]高效的SQL语句之分析函数(四)--lag()/lead()

有时候报表上面需要显示该笔操作的上一步骤或者下一步骤的详细信息，这个时候可以按照下面的做法：

先创建示例表：

--

Create table

create

table

LEAD_TABLE

(

  CASEID

VARCHAR2

(

10

),

  STEPID

VARCHAR2

(

10

),

  ACTIONDATE DATE

)

tablespace COLM_DATA

  pctfree

10

  initrans

1

  maxtrans

255

  storage

  (

    initial 64K

    minextents

1

    maxextents unlimited

  );

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step1

'

,to_date(

'

20070101

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step2

'

,to_date(

'

20070102

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step3

'

,to_date(

'

20070103

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step4

'

,to_date(

'

20070104

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step5

'

,to_date(

'

20070105

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step4

'

,to_date(

'

20070106

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step6

'

,to_date(

'

20070101

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case1

'

,

'

Step1

'

,to_date(

'

20070201

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case2

'

,

'

Step2

'

,to_date(

'

20070202

'

,

'

yyyy-mm-dd

'

));

insert

into

LEAD_TABLE

values

(

'

Case2

'

,

'

Step3

'

,to_date(

'

20070203

'

,

'

yyyy-mm-dd

'

));

commit

;

每一条记录都能连接到上/下一行的内容

select

caseid,stepid,actiondate,lead(stepid)

over

(partition

by

caseid

order

by

actiondate) nextstepid,

lead(actiondate)

over

(partition

by

caseid

order

by

actiondate) nextactiondate,

lag(stepid)

over

(partition

by

caseid

order

by

actiondate) prestepid,

lag(actiondate)

over

(partition

by

caseid

order

by

actiondate) preactiondate

from

lead_table

结果如下：

Case1    Step1

2007

-

1

-

1

Step2

2007

-

1

-

2

Case1    Step2

2007

-

1

-

2

Step3

2007

-

1

-

3

Step1

2007

-

1

-

1

Case1    Step3

2007

-

1

-

3

Step4

2007

-

1

-

4

Step2

2007

-

1

-

2

Case1    Step4

2007

-

1

-

4

Step5

2007

-

1

-

5

Step3

2007

-

1

-

3

Case1    Step5

2007

-

1

-

5

Step4

2007

-

1

-

6

Step4

2007

-

1

-

4

Case1    Step4

2007

-

1

-

6

Step6

2007

-

1

-

7

Step5

2007

-

1

-

5

Case1    Step6

2007

-

1

-

7

Step4

2007

-

1

-

6

Case2    Step1

2007

-

2

-

1

Step2

2007

-

2

-

2

Case2    Step2

2007

-

2

-

2

Step3

2007

-

2

-

3

Step1

2007

-

2

-

1

Case2    Step3

2007

-

2

-

3

Step2

2007

-

2

-

2

还可以进一步统计一下两者的相差天数

select

caseid,stepid,actiondate,nextactiondate,nextactiondate

-

actiondate datebetween

from

(

select

caseid,stepid,actiondate,lead(stepid)

over

(partition

by

caseid

order

by

actiondate) nextstepid,

lead(actiondate)

over

(partition

by

caseid

order

by

actiondate) nextactiondate,

lag(stepid)

over

(partition

by

caseid

order

by

actiondate) prestepid,

lag(actiondate)

over

(partition

by

caseid

order

by

actiondate) preactiondate

from

lead_table)

结果如下：

Case1    Step1

2007

-

1

-

1

2007

-

1

-

2

1

Case1    Step2

2007

-

1

-

2

2007

-

1

-

3

1

Case1    Step3

2007

-

1

-

3

2007

-

1

-

4

1

Case1    Step4

2007

-

1

-

4

2007

-

1

-

5

1

Case1    Step5

2007

-

1

-

5

2007

-

1

-

6

1

Case1    Step4

2007

-

1

-

6

2007

-

1

-

7

1

Case1    Step6

2007

-

1

-

7

Case2    Step1

2007

-

2

-

1

2007

-

2

-

2

1

Case2    Step2

2007

-

2

-

2

2007

-

2

-

3

1

Case2    Step3

2007

-

2

-

3