程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 分析函數，oracle分析函數

分析函數，oracle分析函數

編輯：Oracle教程

分析函數，oracle分析函數

分析函數
基本語法
函數名稱([參數]) VOER (
PARTITION BY 子句字段,....
[ORDER BY 子句字段,..[ASC][DESC][NULLS FIRST][NULLS LAST]]
[WINDOWING 子句]);
使用PARTITION BY子句

SELECT deptno,ename,sal,job,
SUM(sal) OVER(PARTITION BY deptno) sum部門工資總和
FROM emp;

如果不加PARTITION BY

SELECT deptno,ename,sal,job,
SUM(sal) OVER() sum全公司工資總和
FROM emp;

使用PARTITION BY子句加多個分區

SELECT deptno,ename,sal,job,
SUM(sal) OVER(PARTITION BY deptno,job)sum
FROM emp;

ORDER BY 子句
查看ORDER BY子句

SELECT deptno,ename,sal,hiredate,
RANK()OVER (PARTITION BY deptno ORDER BY sal,hiredate DESC) rk
FROM emp;

直接用ORDER BY排序

SELECT deptno,ename,sal,hiredate,
SUM(sal) OVER(ORDER BY ename)sum
FROM emp;

NULLS FIRST 出現NULL值的數據放在前面
NULLS LAST 出現NULL值的數據放在後面

SELECT deptno,ename,sal,comm,
RANK() OVER(ORDER BY comm DESC NULLS LAST) RK,
SUM(sal) OVER(ORDER BY comm DESC NULLS LAST) sum
FROM emp;

WINDOWING子句
RNGGE子句
在sal上設置偏移量

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sum
FROM emp;

向上N匹配
下面是向下N匹配

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum
FROM emp;

匹配當前行數據

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
BETWEEN 0 PRECEDING AND CURRENT ROW) sum
FROM emp;

此處用了CURRENT ROW選項，表示比當前行數相同，所以只有相同的數據才會使用SUM計算總和
使用UNBOUNDED不設置邊界

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM emp;

ROWS子句
設置兩行偏移

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sum
FROM emp;

查詢行的范圍

SELECT deptno,ename,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum
FROM emp;

分析函數的范例
數據統計函數
SUM
MIN
MAX
AVG
COUNT
這些是數據統計函數
查詢編號是7369的員工姓名，職位，基本工資，部門編號，部門人數，平均工資，最高工資，最低工資，總工資

SELECT * FROM(
SELECT empno,ename,job,sal,deptno,
COUNT(empno) OVER(PARTITION BY deptno) count,
ROUND(AVG(sal) OVER(PARTITION BY deptno),2) avg,
MAX(sal) OVER(PARTITION BY deptno) max,
MIN(sal) OVER(PARTITION BY deptno) min,
SUM(sal) OVER(PARTITION BY deptno) sum
FROM emp
) temp
WHERE temp.empno=7369;

查詢每個員工的編號，姓名，基本工資，所在部門名稱，部門位置，以及此部門的平均工資，最高和最低工資
emp和dept表

SELECT e.empno,e.ename,e.sal,d.dname,d.loc,
ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),2) avg, 
MAX(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max,
MIN(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min,
SUM(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum
FROM emp e,dept d
WHERE e.deptno=d.deptno;

等級函數
記錄標記函數
RANK()和DENSE_RANK()
觀察RANK()和DENSE_RANK()函數

SELECT deptno,ename,sal,
RANK() OVER(PARTITION BY deptno ORDER BY sal) rank_result,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dense_rank_resut
FROM emp;

可以看出RANK是有重復是當前值未變，但其它已經+1會跳號，下個不同的就+2了和DENSE_RANK()每次只+1，重復也不會增加
行標記函數
ROW_NUMBER()自動成功行記錄號，並且不管內容是否重復都可以連接編號

SELECT deptno,ename,sal,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) row_result_deptno,
ROW_NUMBER() OVER(ORDER BY sal) row_result_sal
FROM emp;
使用KEEP語句保留

滿足條件的數據

SELECT deptno,
MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) max_sal,
MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal) min_sal
FROM emp
GROUP BY deptno;

取出首行和尾行的記錄

FIRST_VALUE() LAST_VALUE()
SELECT deptno,empno,ename,sal,
FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_result,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_result

FROM emp
WHERE deptno=10;

比較相鄰記錄
LAG()取得之前所列數據行的第N行記錄顯示，如果沒有則使用默認值，不設置默認值返回NULL
LEAD()取重之後所列數據的第N行記錄顯示，如果沒有則使用默認值，不設置默認值返回NULL

SELECT deptno,empno,ename,sal,
LAG(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal) lag_result,
LEAD(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal)lead_result
FROM emp
WHERE deptno=20;

報表函數
CUME_DIST()計算在分區中的相對位置
NTILE(數字）將一個分區分為“表達式”的散列表示
RATIO_TO_TEPORT(表達式）該函數計算expression/(sum(expression))的值，它給出的相對於總數據的百分比
驗證CUME_DIST()函數
假設分區有5行測0.2,0.4,0.6,0.8,1.0進行劃分

SELECT deptno,ename,sal, 
CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal) cume
FROM emp
WHERE deptno IN(10,20);

NTILE函數
對一個數據區有序結果進行劃分，並為每個小組分配唯一的組編號

SELECT deptno,sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) sum,
NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) ntile_a,
NTILE(6) OVER (PARTITION BY deptno ORDER BY sal) ntile_b
FROM emp;

RATIO_TO_REPORT函數可以將需要統計的數據按照整體數據的百分比進行顯示

SELECT deptno,SUM(sal),
ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5) rate,
ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5)*100 || '%'precent
FROM emp
GROUP BY deptno;

行列轉換

查詢每個部門中各個職位的總工資

SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job;

第2種實現
用DECODE顯示

SELECT deptno, 
SUM(DECODE(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
SUM(DECODE(job,'MANAGER',sal,0)) MANAGER_JOB,
SUM(DECODE(job,'ANALYST',sal,0)) ANALYST_JOB, 
SUM(DECODE(job,'CLERK',sal,0)) CLERK_JOB, 
SUM(DECODE(job,'SALESMAN',sal,0)) SALESMAN_JOB
FROM emp
GROUP BY deptno;

不使用DECODE來實現

SELECT temp.dno,SUM(PRESIDENT_JOB),SUM(MANAGER_JOB),SUM(ANALYST_JOB),SUM(CLERK_JOB),SUM(SALESMANJOB)
FROM(
SELECT deptno dno, 
(SELECT SUM(sal) FROM emp WHERE job='PRESIDENT'AND empno=e.empno) PRESIDENT_JOB,
(SELECT SUM(sal) FROM emp WHERE job='MANAGER'AND empno=e.empno) MANAGER_JOB,
(SELECT SUM(sal) FROM emp WHERE job='ANALYST'AND empno=e.empno) ANALYST_JOB,
(SELECT SUM(sal) FROM emp WHERE job='CLERK'AND empno=e.empno) CLERK_JOB,
(SELECT SUM(sal) FROM emp WHERE job='SALESMAN'AND empno=e.empno) SALESMANJOB
FROM emp e) temp
GROUP BY temp.dno
ORDER BY temp.dno DESC;

PIVOT函數和UNPIVOT函數

SELECT * FROM (SELECT deptno,job,sal FROM emp)
PIVOT(
SUM(sal)
FOR job IN(
'PRESIDENT' AS PRESIDENT_JOB,
'MANAGER' AS MANAGER_JOB,
'ANALYST' AS ANALYST_JOB,
'CLERK' AS CLERK_JOB,
'SALESMAN' AS SALESMANJOB
)
)ORDER BY deptno;

使用XML與ANY
如果在PIVOT中增加了XML顯示，可以利用ANY設置所要操作的所有數據

SELECT * FROM (SELECT deptno,job,sal FROM emp)
PIVOT XML(
SUM(sal)
FOR job IN(ANY)
)ORDER BY deptno;

查詢更多統計信息

SELECT * FROM 
(
SELECT deptno,job,sal,
SUM(sal) OVER (PARTITION BY deptno) sumsal,
MAX(sal) OVER (PARTITION BY deptno) maxsal,
MIN(sal) OVER (PARTITION BY deptno) minsal
FROM emp
)
PIVOT(
SUM(sal)
FOR job IN(
'PRESIDENT' AS PRESIDENT_JOB,
'MANAGER' AS MANAGER_JOB,
'ANALYST' AS ANALYST_JOB,
'CLERK' AS CLERK_JOB,
'SALESMAN' AS SALESMANJOB
)
)ORDER BY deptno;

設置多個統計函數，查詢每個部門不同職位的總工資，以及每個部門不同職位的最高工資

SELECT * FROM 
(
SELECT deptno,job,sal FROM emp
)
PIVOT(
SUM(sal) AS sum, MAX(sal) AS max
FOR job IN(
'PRESIDENT' AS PRESIDENT_JOB,
'MANAGER' AS MANAGER_JOB,
'ANALYST' AS ANALYST_JOB,
'CLERK' AS CLERK_JOB,
'SALESMAN' AS SALESMANJOB
)
)ORDER BY deptno;
使用UNPIVOT
WITH temp AS(
SELECT * FROM 
( SELECT deptno,job,sal FROM emp)
PIVOT(
SUM(sal) 
FOR job IN(
'PRESIDENT' AS PRESIDENT_JOB,
'MANAGER' AS MANAGER_JOB,
'ANALYST' AS ANALYST_JOB,
'CLERK' AS CLERK_JOB,
'SALESMAN' AS SALESMANJOB
)
)ORDER BY deptno)
SELECT * FROM temp
UNPIVOT INCLUDE NULLS(
sal_sum FOR job IN(
PRESIDENT_JOB AS 'PRESIDENT',
MANAGER_JOB AS 'MANAGER' ,
ANALYST_JOB AS 'ANALYST' ,
CLERK_JOB AS 'CLERK' ,
SALESMANJOB AS 'SALESMAN'
)
)ORDER BY deptno;