Oracle高级分析函数与统计函数结合使用

备注:测试数据库版本为Oracle 11g R2

这个blog我们来聊聊Oracle高级分析函数与统计统计函数结合使用

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

测试数据:

DROP TABLE testa;
 
 
CREATE  TABLE testa (area   VARCHAR2 (20), month VARCHAR2   (20),
amount NUMBER);


insert into testa values ('上海', '1', 199); 
insert into testa values ('上海', '2', 199); 
insert into testa values ('上海', '3', 155); 
insert into testa values ('上海', '3', 155); 
insert into testa values ('上海', '4', 125); 
insert into testa values ('广州', '1', 75); 
insert into testa values ('广州', '2', 67); 
insert into testa values ('北京', '1', 235);
insert into testa values ('北京', '2', 330); 
Commit;

一.keep函数

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

keep语法：

min | max(col1) keep (dense_rank first | lastorder by col2) over (partion by col3);

最前是聚合函数，可以是min、max、avg、sum…
col1为要计算的列；
dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；

解释：返回按照col3分组后，按照col2排序的结果集中第一个或最后一个最小值或最大值col1。
col1和col2列可重复

需求:求员工表每个员工信息及部门最高薪资、最低薪资

--传统sql写法,需要嵌套一层临时表
with tmp1 as
(
select e.deptno,max(e.sal) max_sal,min(e.sal) min_sal
from emp e
group by e.deptno
)
select e2.deptno,
       e2.ename,
       e2.sal,
       max_sal,
       min_sal
from emp e2
left join tmp1 
on e2.deptno = tmp1.deptno
ORDER BY e2.deptno, e2.sal, e2.ename;

--排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求
   

--通过keep函数,无需嵌套子查询,代码逻辑更为简单   
SELECT Deptno,
       Ename,
       Sal,
       MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,
       MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_sal
  FROM Emp
 ORDER BY deptno, sal, ename;

SQL> --传统sql写法,需要嵌套一层临时表
SQL> with tmp1 as
  2  (
  3  select e.deptno,max(e.sal) max_sal,min(e.sal) min_sal
  4  from emp e
  5  group by e.deptno
  6  )
  7  select e2.deptno,
  8         e2.ename,
  9         e2.sal,
 10         max_sal,
 11         min_sal
 12  from emp e2
 13  left join tmp1
 14  on e2.deptno = tmp1.deptno
 15  ORDER BY e2.deptno, e2.sal, e2.ename;
DEPTNO ENAME            SAL    MAX_SAL    MIN_SAL
------ ---------- --------- ---------- ----------
    10 MILLER       1300.00       5000       1300
    10 CLARK        2450.00       5000       1300
    10 KING         5000.00       5000       1300
    20 SMITH         800.00       3000        800
    20 ADAMS        1100.00       3000        800
    20 JONES        2975.00       3000        800
    20 FORD         3000.00       3000        800
    20 SCOTT        3000.00       3000        800
    30 JAMES         950.00       2850        950
    30 MARTIN       1250.00       2850        950
    30 WARD         1250.00       2850        950
    30 TURNER       1500.00       2850        950
    30 ALLEN        1600.00       2850        950
    30 BLAKE        2850.00       2850        950
14 rows selected
SQL> --排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求
SQL> --通过keep函数,无需嵌套子查询,代码逻辑更为简单
SQL> SELECT Deptno,
  2         Ename,
  3         Sal,
  4         MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,
  5         MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_sal
  6    FROM Emp
  7   ORDER BY deptno, sal, ename;
DEPTNO ENAME            SAL    MAX_SAL    MIN_SAL
------ ---------- --------- ---------- ----------
    10 MILLER       1300.00       1300       5000
    10 CLARK        2450.00       1300       5000
    10 KING         5000.00       1300       5000
    20 SMITH         800.00        800       3000
    20 ADAMS        1100.00        800       3000
    20 JONES        2975.00        800       3000
    20 FORD         3000.00        800       3000
    20 SCOTT        3000.00        800       3000
    30 JAMES         950.00        950       2850
    30 MARTIN       1250.00        950       2850
    30 WARD         1250.00        950       2850
    30 TURNER       1500.00        950       2850
    30 ALLEN        1600.00        950       2850
    30 BLAKE        2850.00        950       2850
14 rows selected

二.求累积销售额

需求:求每个区域每个月的销售额以及累积销售额

--传统写法,通过表连接 t1.month >= t2.month 及group语句解决
select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amount
from testa t1
left join testa t2
on t1.area = t2.area
and t1.month >= t2.month
group by t1.area,t1.month,t1.amount
order by t1.area,t1.month;

--通过sum聚合函数与分析函数配合使用,代码更简洁易懂
select t1.area,
       t1.month,
       t1.amount,
       sum(t1.amount) over(partition  by t1.area order by month) cum_amount
from testa t1
order by t1.area,t1.month;

SQL> --传统写法,通过表连接 t1.month >= t2.month 及group语句解决
SQL> select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amount
  2  from testa t1
  3  left join testa t2
  4  on t1.area = t2.area
  5  and t1.month >= t2.month
  6  group by t1.area,t1.month,t1.amount
  7  order by t1.area,t1.month;
AREA                 MONTH                    AMOUNT CUM_AMOUNT
-------------------- -------------------- ---------- ----------
北京                 1                           235        235
北京                 2                           330        565
广州                 1                            75         75
广州                 2                            67        142
上海                 1                           199        199
上海                 2                           199        398
上海                 3                           155       1416
上海                 4                           125        833
8 rows selected
SQL> --通过sum聚合函数与分析函数配合使用,代码更简洁易懂
SQL> select t1.area,
  2         t1.month,
  3         t1.amount,
  4         sum(t1.amount) over(partition  by t1.area order by month) cum_amount
  5  from testa t1
  6  order by t1.area,t1.month;
AREA                 MONTH                    AMOUNT CUM_AMOUNT
-------------------- -------------------- ---------- ----------
北京                 1                           235        235
北京                 2                           330        565
广州                 1                            75         75
广州                 2                            67        142
上海                 1                           199        199
上海                 2                           199        398
上海                 3                           155        708
上海                 3                           155        708
上海                 4                           125        833
9 rows selected