备注:测试数据库版本为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
需求:每月的最高和最低销售额对应的区域（如有多个区域按区域列出最小的一个，如某区域某月无销售额则不做统计
SELECT t1.month,
MIN(area) keep(dense_rank FIRST ORDER BY amount DESC) max_area,
MIN(area) keep (dense_rank FIRST ORDER BY amount) min_area
FROM testa t1
GROUP BY t1.month;
SQL> SELECT t1.month,
2 MIN(area) keep(dense_rank FIRST ORDER BY amount DESC) max_area,
3 MIN(area) keep (dense_rank FIRST ORDER BY amount) min_area
4 FROM testa t1
5 GROUP BY t1.month;
MONTH MAX_AREA MIN_AREA
-------------------- -------------------- --------------------
1 北京 广州
2 北京 广州
3 上海 上海
4 上海 上海
二.求累积销售额
需求:求每个区域每个月的销售额以及累积销售额
--传统写法,通过表连接 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
网友评论