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

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

测试数据:

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

正在跳转(iOS交流裙密码：123)