Oracle常見分析函數(shù)實例詳解
1. 認識分析函數(shù)
1.1 什么是分析函數(shù)
分析函數(shù)是Oracle專門用于解決復雜報表統(tǒng)計需求的功能強大的函數(shù)，它可以在數(shù)據(jù)中進行分組然后計算基于組的某種統(tǒng)計值，并且每一組的每一行都可以返回一個統(tǒng)計值。
1.2 分析函數(shù)和聚合函數(shù)的不同
普通的聚合函數(shù)用group by分組，每個分組返回一個統(tǒng)計值；而分析函數(shù)采用partition by 分組，并且每組每行都可以返回一個統(tǒng)計值。
1.3 分析函數(shù)的形式
分析函數(shù)帶有一個開窗函數(shù)over(),包含三個分析子句：分組(partition by)，排序(order by), 窗口(rows)，他們的使用形式如下：
over(partition by xxx order by yyy rows between zzz) -- 例如在scott.emp表中：xxx為deptno, yyy為sal, -- zzz為unbounded preceding and unbounded following
分析函數(shù)的例子：
顯示各部門員工的工資，并附帶顯示該部分的最高工資。
SQL如下：
SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP;
結果為：
注： current row 表示當前行
unbounded preceding 表示第一行
unbounded following 表示最后一行
last_value(sal) 的結果與 order by sal 排序有關。如果排序為order by sal desc, 則最終的結果為分組排序后sal的最小值(分組排序后的最后一個值), 當deptno為10時，max_sal為1300。
2. 理解over()函數(shù)
2.1 兩個order by 的執(zhí)行機制
分析函數(shù)是在整個SQL查詢結束后(SQL語句中的order by 的執(zhí)行比較特殊)再進行的操作，也就是說SQL語句中的order by也會影響分析函數(shù)的執(zhí)行結果：
- 兩者一致：如果SQL語句中的order by 滿足分析函數(shù)分析時要求的排序，那么SQL語句中的排序將先執(zhí)行，分析函數(shù)在分析時就不必再排序。
- 兩者不一致：如果SQL語句中的order by 不滿足分析函數(shù)分析時要求的排序，那么SQL語句中的排序將最后在分析函數(shù)分析結束后執(zhí)行排序。
2.2 分析函數(shù)中的分組、排序、窗口
分析函數(shù)包含三個分析子句：分組(partition by)、排序(order by)、窗口(rows)。
窗口就是分析函數(shù)分析時要處理的數(shù)據(jù)范圍，就拿sum來說，它是sum窗口中的記錄而不是整個分組中的記錄。因此我們在想得到某個欄位的累計值時，我們需要把窗口指定到該分組中的第一行數(shù)據(jù)到當前行，如果你指定該窗口從該分組中的第一行到最后一行，那么該組中的每一個sum值都會一樣，即整個組的總和。
窗口子句中我們經常用到指定第一行，當前行，最后一行這樣的三個屬性：
- 第一行是 unbounded preceding
- 當前行是 current row
- 最后一行是 unbounded following
窗口子句不能單獨出現(xiàn)，必須有order by 子句時才能出現(xiàn)，如：
LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
以上示例指定窗口為整個分組.
而出現(xiàn)order by 子句的時候，不一定要有窗口子句，但效果會不一樣，此時窗口默認是當前組的第一行到當前行！
SQL語句為：
SELECT DEPTNO, EMPNO, ENAME, SAL, last_value(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) MAX_SAL FROM EMP;
等價于
SELECT DEPTNO, EMPNO, ENAME, SAL,last_value(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MAX_SAL FROM EMP;
結果如下圖所示：
當省略窗口子句時：
- 如果存在order by, 則默認的窗口是 unbounded preceding and current row.
- 如果同時省略order by, 則默認的窗口是 unbounded preceding and unbounded following.
如果省略分組，則把全部記錄當成一個組：
- 如果存在order by 則默認窗口是unbounded preceding and current row
- 如果這時省略order by 則窗口默認為 unbounded preceding and unbounded following
2.3 幫助理解over()的實例
例1：關注點：SQL無排序，over()排序子句省略
select deptno, empno, ename, sal, last_value(sal) over(partition by deptno) from emp;
例2：關注點：SQL無排序，over()排序子句有，窗口省略
select deptno, empno, ename, sal, last_value(sal) over(partition by deptno order by sal desc) from emp;
例3：關注點：SQL無排序，over()排序子句有，窗口也有，窗口特意強調全組數(shù)據(jù)
select deptno, empno, ename, sal, last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following) max_sal from emp;
例4：關注點：SQL有排序（正序），over() 排序子句無，先做SQL排序再進行分析函數(shù)運算
select deptno, mgr, ename, sal, hiredate, last_value(sal) over(partition by deptno) last_value from emp where deptno=30 order by deptno, mgr;
例5：關注點：SQL有排序（倒序），over() 排序子句無，先做SQL排序再進行分析函數(shù)運算
select deptno, mgr, ename, sal, hiredate, last_value(sal) over(partition by deptno) last_value from emp where deptno=30 order by deptno, mgr desc;
例6：關注點：SQL有排序（倒序），over()排序子句有，窗口子句無，此時的運算是：SQL先選數(shù)據(jù)但是不排序，而后排序子句先排序并進行分析函數(shù)處理（窗口默認為第一行到當前行），最后再進行SQL排序
select deptno, mgr, ename, sal, hiredate, min(sal) over(partition by deptno order by sal)last_value from emp where deptno=30 order by deptno, mgr desc;
select deptno, mgr, ename, sal, hiredate, min(sal) over(partition by deptno order by sal desc) last_value from emp where deptno=30 order by deptno, mgr desc;
3. 常見分析函數(shù)
3.1 演示表和數(shù)據(jù)的生成
建表語句：
create table t( BILL_MONTH VARCHAR2(12), AREA_CODE NUMBER, NET_TYPE VARCHAR(2), LOCAL_FARE NUMBER );
插入數(shù)據(jù)：
insert into t values('200405',5761,'G', 7393344.04);
insert into t values('200405',5761,'J', 5667089.85);
insert into t values('200405',5762,'G', 6315075.96);
insert into t values('200405',5762,'J', 6328716.15);
insert into t values('200405',5763,'G', 8861742.59);
insert into t values('200405',5763,'J', 7788036.32);
insert into t values('200405',5764,'G', 6028670.45);
insert into t values('200405',5764,'J', 6459121.49);
insert into t values('200405',5765,'G', 13156065.77);
insert into t values('200405',5765,'J', 11901671.70);
insert into t values('200406',5761,'G', 7614587.96);
insert into t values('200406',5761,'J', 5704343.05);
insert into t values('200406',5762,'G', 6556992.60);
insert into t values('200406',5762,'J', 6238068.05);
insert into t values('200406',5763,'G', 9130055.46);
insert into t values('200406',5763,'J', 7990460.25);
insert into t values('200406',5764,'G', 6387706.01);
insert into t values('200406',5764,'J', 6907481.66);
insert into t values('200406',5765,'G', 13562968.81);
insert into t values('200406',5765,'J', 12495492.50);
insert into t values('200407',5761,'G', 7987050.65);
insert into t values('200407',5761,'J', 5723215.28);
insert into t values('200407',5762,'G', 6833096.68);
insert into t values('200407',5762,'J', 6391201.44);
insert into t values('200407',5763,'G', 9410815.91);
insert into t values('200407',5763,'J', 8076677.41);
insert into t values('200407',5764,'G', 6456433.23);
insert into t values('200407',5764,'J', 6987660.53);
insert into t values('200407',5765,'G', 14000101.20);
insert into t values('200407',5765,'J', 12301780.20);
insert into t values('200408',5761,'G', 8085170.84);
insert into t values('200408',5761,'J', 6050611.37);
insert into t values('200408',5762,'G', 6854584.22);
insert into t values('200408',5762,'J', 6521884.50);
insert into t values('200408',5763,'G', 9468707.65);
insert into t values('200408',5763,'J', 8460049.43);
insert into t values('200408',5764,'G', 6587559.23);
insert into t values('200408',5764,'J', 7342135.86);
insert into t values('200408',5765,'G', 14450586.63);
insert into t values('200408',5765,'J', 12680052.38);
commit;3.2 first_value()與last_value():求最值對應的其他屬性
問題：取出每個月通話費最高和最低的兩個地區(qū)
思路：先進行group by bill_month, area_code使用聚合函數(shù)sum()求解出by bill_month, area_code的local_fare總和, 即sum(local_fare), 然后再運用分析函數(shù)進行求解每個月通話費用最高和最低的兩個地區(qū)。
select bill_month, area_code, sum(local_fare) local_fare, first_value(area_code) over(partition by bill_month order by sum(local_fare) desc rows between unbounded preceding and unbounded following) firstval, last_value(area_code) over(partition by bill_month order by sum(local_fare) desc rows between unbounded preceding and unbounded following) lastval from t group by bill_month, area_code;
3.3 rank()、dense_rank()與row_number() 排序問題
演示數(shù)據(jù)再Oracle自帶的scott用戶下
1.rank()值相同時排名相同，其后排名跳躍不連續(xù)
select * from ( select deptno, ename, sal, rank() over(partition by deptno order by sal desc) rw from emp ) where rw < 4;
2. dense_rank()值相同時排名相同，其后排名連續(xù)不跳躍
select * from ( select deptno, ename, sal, dense_rank() over(partition by deptno order by sal desc) rw from emp ) where rw <= 4;
3. row_number()值相同時排名不相等，其后排名連續(xù)不跳躍
select * from ( select deptno, ename, sal, row_number() over(partition by deptno order by sal desc) rw from emp ) where rw <= 4;
3.4 lag()與lead():求之前或之后的第N行
lag(arg1, arg2, arg3):
- arg1：是從其他行返回的表達式
- arg2：是希望檢索的當前行分區(qū)的偏移量。是一個正的偏移量，是一個往回檢索以前的行數(shù)目
- arg3：是在arg2表示的數(shù)目超出了分組的范圍時返回的值
而lead()與lag()相反
select bill_month, area_code, local_fare cur_local_fare,
lag(local_fare, 1, 0) over(partition by area_code order by bill_month)
last_local_fare,
lead(local_fare, 1, 0) over(partition by area_code order by bill_month)
next_local_fare
from (select bill_month, area_code, sum(local_fare) local_fare
from t group by bill_month, area_code);
3.5 rollup()與cube()：排列組合分組
group by rollup(A, B, C)：
首先會對 (A, B, C) 進行group by,
然后再對 (A, B) 進行group by,
其后再對 (A) 進行group by,
最后對全表進行匯總操作。
group by cube(A, B, C):
則首先會對 (A, B, C) 進行group by,
然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),
最后對全表進行匯總操作。
1.生成演示數(shù)據(jù)：
create table scott.tt as select * from dba_indexes;
2.普通group by 體驗
select owner, index_type, status, count(*) from tt where owner like 'SY%' group by owner, index_type, status;
3. group by rollup(A, B, C)：
首先會對 (A, B, C) 進行group by,
然后再對 (A, B) 進行group by,
其后再對 (A) 進行group by,
最后對全表進行匯總操作。
select owner, index_type, status, count(*) from tt where owner like 'SY%' group by rollup(owner, index_type, status);
4. group by cube(A, B, C):
則首先會對 (A, B, C) 進行group by,
然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),
最后對全表進行匯總操作。
select owner, index_type, status, count(*) from tt where owner like 'SY%' group by cube(owner, index_type, status);
（只截取了部分圖）
3.6 max()、min()、sum()與avg()：求移動的最值、總和與平均值
問題：計算出各個地區(qū)連續(xù)3個月的通話費用的平均數(shù)(移動平均值)
select area_code, bill_month, local_fare,
sum(local_fare) over(partition by area_code order by to_number(bill_month)
range between 1 preceding and 1 following) month3_sum,
avg(local_fare) over(partition by area_code order by to_number(bill_month)
range between 1 preceding and 1 following) month3_avg,
max(local_fare) over(partition by area_code order by to_number(bill_month)
range between 1 preceding and 1 following) month3_max,
min(local_fare) over(partition by area_code order by to_number(bill_month)
range between 1 preceding and 1 following) month3_min
from (select bill_month, area_code, sum(local_fare) local_fare from t
group by area_code, bill_month);
問題：求各地區(qū)按月份累加的通話費
select area_code, bill_month, local_fare, sum(local_fare) over(partition by area_code order by bill_month asc) last_sum_value from(select area_code, bill_month, sum(local_fare) local_fare from t group by area_code, bill_month) order by area_code, bill_month;
3.7 ratio_to_report()：求百分比
問題：求各地區(qū)花費占各月花費的比例
select bill_month, area_code, sum(local_fare) local_fare, RATIO_TO_REPORT(sum(local_fare)) OVER (partition by bill_month) AS area_pct from t group by bill_month, area_code;
總結
到此這篇關于Oracle常見分析函數(shù)的文章就介紹到這了,更多相關Oracle分析函數(shù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了oracle中關于case?when?then的使用方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
Oracle中的sequence用法在oracle中sequence就是所謂的序列號，本文詳細的介紹了ORACLE SEQUENCE用法，具有一定的參考價值，有興趣的可以了解下。2016-12-12
今天小編就為大家分享一篇關于Oracle中sql語句(+)符號代表連接的使用講解，小編覺得內容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-02-02
trunc函數(shù)用法用于截取時間或者數(shù)值,返回指定的值,下面這篇文章主要給大家介紹了關于Oracle中trunc()函數(shù)詳解的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-01-01
淺談Oracle數(shù)據(jù)庫的建模與設計...2007-03-03
Oracle數(shù)據(jù)庫安全策略分析(二）...2007-03-03
Linux環(huán)境下Oracle安裝參數(shù)設置方法詳解
這篇文章主要介紹了Linux環(huán)境下Oracle安裝參數(shù)設置方法，本文通過代碼給大家介紹的非常詳細，具有一定的參考借鑒價值 ,需要的朋友可以參考下2019-06-06
使用contains謂詞有個條件，那就是列要建立索引，本節(jié)主要介紹了oracle使用instr或like方法判斷是否包含字符串,需要的朋友可以參考下2014-08-08
Oracle中update和select 關聯(lián)操作
本文主要向大家介紹了Oracle數(shù)據(jù)庫之oracle update set select from 關聯(lián)更新，通過具體的內容向大家展現(xiàn)，本文給大家介紹的非常詳細，感興趣的朋友跟隨小編一起看看吧2022-01-01
這篇文章主要介紹了Oracle觸發(fā)器用法,結合實例形式詳細分析了Oracle觸發(fā)器的概念,功能,語法及相關使用技巧,需要的朋友可以參考下2016-03-03