Oracle開(kāi)發(fā)之分析函數(shù)簡(jiǎn)介Over用法
一、Oracle分析函數(shù)簡(jiǎn)介：
在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即Online Transaction Process)，這些系統(tǒng)的特點(diǎn)是具備實(shí)時(shí)要求，或者至少說(shuō)對(duì)響應(yīng)的時(shí)間多長(zhǎng)有一定的要求；其次這些系統(tǒng)的業(yè)務(wù)邏輯一般比較復(fù)雜，可能需要經(jīng)過(guò)多次的運(yùn)算。比如我們經(jīng)常接觸到的電子商城。
在這些系統(tǒng)之外，還有一種稱(chēng)之為OLAP的系統(tǒng)(即Online Aanalyse Process)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉(cāng)庫(kù)、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點(diǎn)是數(shù)據(jù)量大，對(duì)實(shí)時(shí)響應(yīng)的要求不高或者根本不關(guān)注這方面的要求，以查詢(xún)、統(tǒng)計(jì)操作為主。
我們來(lái)看看下面的幾個(gè)典型例子：
①查找上一年度各個(gè)銷(xiāo)售區(qū)域排名前10的員工
②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶(hù)
③查找上一年度銷(xiāo)售最差的部門(mén)所在的區(qū)域
④查找上一年度銷(xiāo)售最好和最差的產(chǎn)品
我們看看上面的幾個(gè)例子就可以感覺(jué)到這幾個(gè)查詢(xún)和我們?nèi)粘Ｓ龅降牟樵?xún)有些不同，具體有：
①需要對(duì)同樣的數(shù)據(jù)進(jìn)行不同級(jí)別的聚合操作
②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較
③需要在排序完的結(jié)果集上進(jìn)行額外的過(guò)濾操作
二、Oracle分析函數(shù)簡(jiǎn)單實(shí)例：
下面我們通過(guò)一個(gè)實(shí)際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶(hù)，來(lái)看看分析函數(shù)的應(yīng)用。
【1】測(cè)試環(huán)境：
Name Null? Type
----------------------- -------- ----------------
CUST_NBR NOT NULL NUMBER(5)
REGION_ID NOT NULL NUMBER(5)
SALESPERSON_ID NOT NULL NUMBER(5)
YEAR NOT NULL NUMBER(4)
MONTH NOT NULL NUMBER(2)
TOT_ORDERS NOT NULL NUMBER(7)
TOT_SALES NOT NULL NUMBER(11,2)
【2】測(cè)試數(shù)據(jù)：
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
15 7 12 2000 5 6 24
12 7 9 2000 6 2 50658
1 5 2 2000 3 2 44494
1 5 1 2000 9 2 74864
2 5 4 2000 3 2 35060
2 5 4 2000 4 4 6454
2 5 1 2000 10 4 35580
4 5 4 2000 12 2 39190
13 rows selected.
【3】測(cè)試語(yǔ)句：
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 37802 37802
7 6 3750 68065
10 6 64315 68065
11 7 12204 12204
三、分析函數(shù)OVER解析：
請(qǐng)注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶(hù)進(jìn)行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計(jì)每個(gè)區(qū)域每個(gè)客戶(hù)的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶(hù)所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。很顯然group by和sum是無(wú)法做到這一點(diǎn)的(因?yàn)榫奂僮鞯募?jí)別不一樣，前者是對(duì)一個(gè)客戶(hù)，后者是對(duì)一批客戶(hù))。
這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進(jìn)行分區(qū)，然后累積每個(gè)區(qū)域每個(gè)客戶(hù)的訂單總額(sum(sum(o.tot_sales)))。
現(xiàn)在我們已經(jīng)知道2001年度每個(gè)客戶(hù)及其對(duì)應(yīng)區(qū)域的訂單總額，那么下面就是篩選那些個(gè)人訂單總額占到區(qū)域訂單總額20%以上的大客戶(hù)了
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 37802 37802
10 6 64315 68065
11 7 12204 12204
SQL>
現(xiàn)在我們已經(jīng)知道這些大客戶(hù)是誰(shuí)了！哦，不過(guò)這還不夠，如果我們想要知道每個(gè)大客戶(hù)所占的訂單比例呢？看看下面的SQL語(yǔ)句，只需要一個(gè)簡(jiǎn)單的Round函數(shù)就搞定了。
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100%
10 6 64315 68065 94%
11 7 12204 12204 100%
SQL>
總結(jié)：
①Over函數(shù)指明在那些字段上做分析，其內(nèi)跟Partition by表示對(duì)數(shù)據(jù)進(jìn)行分組。注意Partition by可以有多個(gè)字段。
②Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。
相關(guān)文章
Oracle中dblink的實(shí)際應(yīng)用示例詳解
DBLink的作用是在局域網(wǎng)內(nèi)，通過(guò)一臺(tái)服務(wù)器上面的數(shù)據(jù)庫(kù)訪問(wèn)另外一臺(tái)服務(wù)器上面數(shù)據(jù)庫(kù)的功能。下面這篇文章主要給大家介紹了關(guān)于Oracle中dblink實(shí)際應(yīng)用的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友們下面來(lái)一起看看吧。2017-09-09
Oracle11g RAC開(kāi)啟關(guān)閉、設(shè)置歸檔小結(jié)
這篇文章主要介紹了Oracle11g RAC開(kāi)啟關(guān)閉、設(shè)置歸檔,很簡(jiǎn)單，但很實(shí)用，需要的朋友可以參考下2014-09-09
oracle中動(dòng)態(tài)SQL使用詳細(xì)介紹
Oracle編譯PL/SQL程序塊分為兩個(gè)種:通常靜態(tài)SQL采用前一種編譯方式，而動(dòng)態(tài)SQL采用后一種編譯方式,需要了解的朋友可以參考下2012-11-11
表空間使用情況包括：查看表空間的名稱(chēng)及大小/查看表空間物理文件的名稱(chēng)及大小/查看回滾段名稱(chēng)及大小等等感興趣的你可以參考下本文2013-03-03
Oracle是眾多的關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)之一,它可以為表格中的每一行創(chuàng)建唯一的序號(hào)列,這篇文章主要給大家介紹了關(guān)于oracle添加序號(hào)列的3種方法大全,需要的朋友可以參考下2024-03-03
本文詳細(xì)講解了Oracle中的常用函數(shù)，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
利用PL/SQL從Oracle數(shù)據(jù)庫(kù)導(dǎo)出和導(dǎo)入數(shù)據(jù)
這篇文章主要為大家詳細(xì)介紹了利用PL/SQL從Oracle數(shù)據(jù)庫(kù)導(dǎo)出數(shù)據(jù)和導(dǎo)入數(shù)據(jù)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-03-03
oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)遷移解決方案
大部分系統(tǒng)由于平臺(tái)和版本的原因，做的是邏輯遷移，少部分做的是物理遷移，接下來(lái)把心得與大家分享一下2012-12-12
oracle ORA-01114、ORA-27067錯(cuò)誤解決方法
本文章總結(jié)了關(guān)于ORA-01114、ORA-27067錯(cuò)誤解決方法，有需要學(xué)習(xí)的朋友可參考一下下哦2012-10-10
oracle閃回技術(shù)詳解(閃回表、閃回?cái)?shù)據(jù))
這篇文章主要給大家介紹了關(guān)于oracle閃回技術(shù)(閃回表、閃回?cái)?shù)據(jù))的相關(guān)資料,Oracle數(shù)據(jù)庫(kù)的閃回技術(shù)是一種高級(jí)的數(shù)據(jù)恢復(fù)技術(shù),它可以幫助用戶(hù)快速、精確地恢復(fù)誤刪除或誤修改的數(shù)據(jù),而無(wú)需進(jìn)行數(shù)據(jù)恢復(fù)或恢復(fù)備份,需要的朋友可以參考下2023-11-11