Oracle高級分析函數(shù)與統(tǒng)計函數(shù)結(jié)合使用

這個blog我們來聊聊Oracle高級分析函數(shù)與統(tǒng)計統(tǒng)計函數(shù)結(jié)合使用

測試數(shù)據(jù):

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函數(shù)

keep是Oracle下的另一個分析函數(shù)龄句，他的用法不同于通過over關鍵字指定的分析函數(shù)，可以用于這樣一種場合下：取同一個分組下以某個字段排序后，對指定字段取最小或最大的那個值纸厉。

keep語法：

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

最前是聚合函數(shù)，可以是min、max、avg贴妻、sum...

col1為要計算的列；

dense_rank first蝙斜，dense_rank last為keep 函數(shù)的保留屬性名惩，表示分組、排序結(jié)果集中第一個孕荠、最后一個娩鹉；

解釋：返回按照col3分組后，按照col2排序的結(jié)果集中第一個或最后一個最小值或最大值col1稚伍。

col1和col2列可重復

需求:求員工表每個員工信息及部門最高薪資弯予、最低薪資

--傳統(tǒng)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;

--排名函數(shù)只能進行排名,通過排名可以看出最大和最小薪資,同樣需要嵌套臨時表來完成此類需求

--通過keep函數(shù),無需嵌套子查詢,代碼邏輯更為簡單?

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> --傳統(tǒng)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> --排名函數(shù)只能進行排名,通過排名可以看出最大和最小薪資,同樣需要嵌套臨時表來完成此類需求

SQL> --通過keep函數(shù),無需嵌套子查詢,代碼邏輯更為簡單

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

需求:每月的最高和最低銷售額對應的區(qū)域（如有多個區(qū)域按區(qū)域列出最小的一個，如某區(qū)域某月無銷售額則不做統(tǒng)計

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 上海上海

二.求累積銷售額

需求:求每個區(qū)域每個月的銷售額以及累積銷售額

--傳統(tǒng)寫法,通過表連接 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聚合函數(shù)與分析函數(shù)配合使用,代碼更簡潔易懂

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> --傳統(tǒng)寫法,通過表連接 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聚合函數(shù)與分析函數(shù)配合使用,代碼更簡潔易懂

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

正在跳轉(zhuǎn)(iOS交流裙密碼：123)