Oracle笔记--分析函数及窗口子句

《Oracle 查询优化改写技巧与案例》  

oracle的分析函数   分析函数用法及窗口子句 range/rows差别

create table TEST001
(
  empno  NUMBER(4),
  ename  VARCHAR2(50),
  sal    NUMBER(8,4),
  deptno NUMBER(4) default 1
)
INSERT INTO TEST001 values(3,'James',1000);
INSERT INTO TEST001 values(3,'Allen',800);
INSERT INTO TEST001 values(2,'Blake',600);
INSERT INTO TEST001 values(2,'Ward',300);
INSERT INTO TEST001 values(1,'Turner',1200);

KEEP函数：每行记录中标注该员工所在部门最高工资员工、最低工资员工

SELECT DEPTNO,ENAME,SAL,
       MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY SAL) OVER (PARTITION BY DEPTNO) MIN_SAL,
       MIN(ENAME) KEEP(DENSE_RANK LAST ORDER BY SAL) OVER (PARTITION BY DEPTNO) MAX_SAL
FROM TEST001

显示最高工资员工、最低工资员工

SELECT DEPTNO,MIN(SAL) MIN_SAL,
       MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY SAL)  MIN_SAL_ENAME,
       MAX(SAL) MAX_SAL,
       MIN(ENAME) KEEP(DENSE_RANK LAST ORDER BY SAL)  MAX_SAL_ENAME
FROM TEST001
GROUP BY DEPTNO

 

SELECT 
      ENAME,SAL,
      FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL),
      LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL)
FROM TEST001

注意：FIRST_VALUE、LAST_VALUE  类似累加模式

员工工资在部门占的比例，在公司占的比例（RATIO_TO_REPORT函数 ）

SELECT 
    ENAME,SAL,
    ROUND(SAL/SUM(SAL) OVER(PARTITION BY DEPTNO)*100,2) PST1,
    ROUND(SAL/SUM(SAL) OVER()*100,2) PST2,
    ROUND(RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO)*100,2) PST1_R,
    ROUND(RATIO_TO_REPORT(SAL) OVER()*100,2) PST2_R
FROM TEST001

1.各部门总额

select empno,sum(sal) as 部门总金额 from test001 group by empno;

2.各部门总额所占比例

select empno, empno_total,total,round(empno_total/total*100,2)||'%' as pst 

from( select  empno, empno_total, sum(empno_total) over() as total 

       from (select empno,sum(sal) empno_total from test001 group by empno))

 3.个人占总额比

select empno,ename,sal,round(sal/total,2) pst from       

     (select empno,ename,sal,sum(sal) over() as total from test001 order by 1)

 4.依次累加sum() over(order by)

select empno,ename,sal,sum(sal) over(order by empno) as total from test001 order by 1

 5.分区累加sum() over(partition by)

select empno,ename,sal,sum(sal) over(partition by empno ) as total from test001 order by 1

 与select empno,sum(sal) from test001 group by empno类似，但不能获取分组字段外值

6.区内累加sum(xx) over(partition by xx order by xx)

select empno,ename,sal,sum(sal) over(partition by empno order by ename ) as total from test001 order by 1

详见

--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
--unbounded：不受控制的，无限的
--preceding：在...之前
--following：在...之后
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING