oracle常見(jiàn)分析函數(shù)實(shí)例詳解

1. 認(rèn)識(shí)分析函數(shù)

1.1 什么是分析函數(shù)

分析函數(shù)是oracle專門用于解決復(fù)雜報(bào)表統(tǒng)計(jì)需求的功能強(qiáng)大的函數(shù)，它可以在數(shù)據(jù)中進(jìn)行分組然后計(jì)算基于組的某種統(tǒng)計(jì)值，并且每一組的每一行都可以返回一個(gè)統(tǒng)計(jì)值。

1.2 分析函數(shù)和聚合函數(shù)的不同

普通的聚合函數(shù)用group by分組，每個(gè)分組返回一個(gè)統(tǒng)計(jì)值；而分析函數(shù)采用partition by 分組，并且每組每行都可以返回一個(gè)統(tǒng)計(jì)值。

1.3 分析函數(shù)的形式

分析函數(shù)帶有一個(gè)開窗函數(shù)over(),包含三個(gè)分析子句：分組(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;

結(jié)果為：

注： current row 表示當(dāng)前行

unbounded preceding 表示第一行

unbounded following 表示最后一行

last_value(sal) 的結(jié)果與 order by sal 排序有關(guān)。如果排序?yàn)閛rder by sal desc, 則最終的結(jié)果為分組排序后sal的最小值(分組排序后的最后一個(gè)值), 當(dāng)deptno為10時(shí)，max_sal為1300。

2. 理解over()函數(shù)

2.1 兩個(gè)order by 的執(zhí)行機(jī)制

分析函數(shù)是在整個(gè)sql查詢結(jié)束后(sql語(yǔ)句中的order by 的執(zhí)行比較特殊)再進(jìn)行的操作，也就是說(shuō)sql語(yǔ)句中的order by也會(huì)影響分析函數(shù)的執(zhí)行結(jié)果：

• 兩者一致：如果sql語(yǔ)句中的order by 滿足分析函數(shù)分析時(shí)要求的排序，那么sql語(yǔ)句中的排序?qū)⑾葓?zhí)行，分析函數(shù)在分析時(shí)就不必再排序。
• 兩者不一致：如果sql語(yǔ)句中的order by 不滿足分析函數(shù)分析時(shí)要求的排序，那么sql語(yǔ)句中的排序?qū)⒆詈笤诜治龊瘮?shù)分析結(jié)束后執(zhí)行排序。

2.2 分析函數(shù)中的分組、排序、窗口

分析函數(shù)包含三個(gè)分析子句：分組(partition by)、排序(order by)、窗口(rows)。

窗口就是分析函數(shù)分析時(shí)要處理的數(shù)據(jù)范圍，就拿sum來(lái)說(shuō)，它是sum窗口中的記錄而不是整個(gè)分組中的記錄。因此我們?cè)谙氲玫侥硞€(gè)欄位的累計(jì)值時(shí)，我們需要把窗口指定到該分組中的第一行數(shù)據(jù)到當(dāng)前行，如果你指定該窗口從該分組中的第一行到最后一行，那么該組中的每一個(gè)sum值都會(huì)一樣，即整個(gè)組的總和。

窗口子句中我們經(jīng)常用到指定第一行，當(dāng)前行，最后一行這樣的三個(gè)屬性：

• 第一行是 unbounded preceding
• 當(dāng)前行是 current row
• 最后一行是 unbounded following

窗口子句不能單獨(dú)出現(xiàn)，必須有order by 子句時(shí)才能出現(xiàn)，如：

last_value(sal) over (partition by deptno order by sal rows between unbounded preceding and unbounded following )

以上示例指定窗口為整個(gè)分組.

而出現(xiàn)order by 子句的時(shí)候，不一定要有窗口子句，但效果會(huì)不一樣，此時(shí)窗口默認(rèn)是當(dāng)前組的第一行到當(dāng)前行！

sql語(yǔ)句為：

select deptno, empno, ename, sal,
last_value(sal) over(partition by deptno order by sal) max_sal from emp;

等價(jià)于

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;

結(jié)果如下圖所示：

當(dāng)省略窗口子句時(shí)：

• 如果存在order by, 則默認(rèn)的窗口是 unbounded preceding and current row.
• 如果同時(shí)省略order by, 則默認(rèn)的窗口是 unbounded preceding and unbounded following.

如果省略分組，則把全部記錄當(dāng)成一個(gè)組：

• 如果存在order by 則默認(rèn)窗口是unbounded preceding and current row
• 如果這時(shí)省略order by 則窗口默認(rèn)為 unbounded preceding and unbounded following

2.3 幫助理解over()的實(shí)例

例1：關(guān)注點(diǎn)：sql無(wú)排序，over()排序子句省略

select deptno, empno, ename, sal, last_value(sal) over(partition by deptno) from emp;

例2：關(guān)注點(diǎn)：sql無(wú)排序，over()排序子句有，窗口省略

select deptno, empno, ename, sal, last_value(sal) over(partition by deptno 
order by sal desc) from emp;

例3：關(guān)注點(diǎn)：sql無(wú)排序，over()排序子句有，窗口也有，窗口特意強(qiáng)調(diào)全組數(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：關(guān)注點(diǎn)：sql有排序（正序），over() 排序子句無(wú)，先做sql排序再進(jìn)行分析函數(shù)運(yùn)算

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：關(guān)注點(diǎn)：sql有排序（倒序），over() 排序子句無(wú)，先做sql排序再進(jìn)行分析函數(shù)運(yùn)算

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：關(guān)注點(diǎn)：sql有排序（倒序），over()排序子句有，窗口子句無(wú)，此時(shí)的運(yùn)算是：sql先選數(shù)據(jù)但是不排序，而后排序子句先排序并進(jìn)行分析函數(shù)處理（窗口默認(rèn)為第一行到當(dāng)前行），最后再進(jìn)行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. 常見(jiàn)分析函數(shù)

3.1 演示表和數(shù)據(jù)的生成

建表語(yǔ)句：

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():求最值對(duì)應(yīng)的其他屬性

問(wèn)題：取出每個(gè)月通話費(fèi)最高和最低的兩個(gè)地區(qū)

思路：先進(jìn)行g(shù)roup by bill_month, area_code使用聚合函數(shù)sum()求解出by bill_month, area_code的local_fare總和, 即sum(local_fare),然后再運(yùn)用分析函數(shù)進(jìn)行求解每個(gè)月通話費(fèi)用最高和最低的兩個(gè)地區(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() 排序問(wèn)題

演示數(shù)據(jù)再oracle自帶的scott用戶下

1.rank()值相同時(shí)排名相同，其后排名跳躍不連續(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()值相同時(shí)排名相同，其后排名連續(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()值相同時(shí)排名不相等，其后排名連續(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：是從其他行返回的表達(dá)式
• arg2：是希望檢索的當(dāng)前行分區(qū)的偏移量。是一個(gè)正的偏移量，是一個(gè)往回檢索以前的行數(shù)目
• arg3：是在arg2表示的數(shù)目超出了分組的范圍時(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)：

首先會(huì)對(duì) (a, b, c) 進(jìn)行g(shù)roup by,

然后再對(duì) (a, b) 進(jìn)行g(shù)roup by,

其后再對(duì) (a) 進(jìn)行g(shù)roup by,

最后對(duì)全表進(jìn)行匯總操作。

group by cube(a, b, c):

則首先會(huì)對(duì) (a, b, c) 進(jìn)行g(shù)roup by,

然后依次是 (a, b), (a, c), (a), (b, c), (b), (c),

最后對(duì)全表進(jìn)行匯總操作。

1.生成演示數(shù)據(jù)：

create table scott.tt as select * from dba_indexes;

2.普通group by 體驗(yàn)

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)：

首先會(huì)對(duì) (a, b, c) 進(jìn)行g(shù)roup by,

然后再對(duì) (a, b) 進(jìn)行g(shù)roup by,

其后再對(duì) (a) 進(jìn)行g(shù)roup by,

最后對(duì)全表進(jìn)行匯總操作。

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):

則首先會(huì)對(duì) (a, b, c) 進(jìn)行g(shù)roup by,

然后依次是 (a, b), (a, c), (a), (b, c), (b), (c),

最后對(duì)全表進(jìn)行匯總操作。

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()：求移動(dòng)的最值、總和與平均值

問(wèn)題：計(jì)算出各個(gè)地區(qū)連續(xù)3個(gè)月的通話費(fèi)用的平均數(shù)(移動(dòng)平均值)

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);

問(wèn)題：求各地區(qū)按月份累加的通話費(fèi)

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()：求百分比

問(wèn)題：求各地區(qū)花費(fèi)占各月花費(fèi)的比例

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;

總結(jié)

關(guān)于oracle常見(jiàn)分析函數(shù)的文章就介紹至此，更多相關(guān)oracle分析函數(shù)內(nèi)容請(qǐng)搜索碩編程以前的文章，希望以后支持碩編程！