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

小毛头的BLOG

[Oracle]高效的SQL语句之分析函数(一)--sum()  

实际应用中我们可以通过sum()统计出组中的总计或者是累加值，具体示例如下：

1.创建演示表

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);

2. sum()语句如下：

select  deptno,
       ename,
       sal,
　　 -- 按照部门薪水累加（order by改变了分析函数的作用，只工作在当前行和前一行，而不是所有行）
        sum (sal)  over  (partition  by  deptno  order   by  sal) CumDeptTot,  
        sum (sal)  over  (partition  by  deptno) SalByDept,   -- 统计一个部门的薪水
        sum (sal)  over  ( order   by  deptno,sal) CumTot,   -- 所有雇员的薪水一行一行的累加
        sum (sal)  over  () TotSal   -- 统计总薪水
   from  emp
  order   by  deptno, sal

3. 结果如下：

10    MILLER    1300.00    1300    8750    1300    29025
10    CLARK    2450.00    3750    8750    3750    29025
10    KING    5000.00    8750    8750    8750    29025
20    SMITH    800.00    800    10875    9550    29025
20    ADAMS    1100.00    1900    10875    10650    29025
20    JONES    2975.00    4875    10875    13625    29025
20    SCOTT    3000.00    10875    10875    19625    29025
20    FORD    3000.00    10875    10875    19625    29025
30    JAMES    950.00    950    9400    20575    29025
30    WARD    1250.00    3450    9400    23075    29025
30    MARTIN    1250.00    3450    9400    23075    29025
30    TURNER    1500.00    4950    9400    24575    29025
30    ALLEN    1600.00    6550    9400    26175    29025
30    BLAKE    2850.00    9400    9400    29025    29025

[Oracle]高效的SQL语句之分析函数(二)--max()

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

先创建示例表

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