Oracle常用分析函數(shù)說明

Oracle分析函數(shù)Oracle從8.1.6開始提供分析函數(shù)，分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。下面例子中使用的表來自O(shè)racle自帶的HR用戶下的表，如果沒有安裝該用戶，可以在SYS用戶下運(yùn)行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql來創(chuàng)建。除本文內(nèi)容外，你還可參考：

ROLLUP與CUBE/post/419/29159

分析函數(shù)使用例子介紹：/post/419/44634本文如果未指明，缺省是在HR用戶下運(yùn)行例子。

開窗函數(shù)的的理解：

開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，舉例如下：

over（orderbysalary）按照salary排序進(jìn)行累計(jì)，orderby是個(gè)默認(rèn)的開窗函數(shù)

over（partitionbydeptno）按照部門分區(qū)

over（orderbysalaryrangebetween50precedingand150following）

每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過50，之后行幅度值不超過150

over（orderbysalaryrowsbetween50precedingand150following）

每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前50行，之后150行

over（orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing）

每行對(duì)應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：

over（orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing）主要參考資料：《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章

1).AVG

功能描述：用于計(jì)算一個(gè)組和數(shù)據(jù)窗口內(nèi)表達(dá)式的平均值。

SAMPLE：下面的例子中列c_mavg計(jì)算員工表中每個(gè)員工的平均薪水報(bào)告，該平均值由當(dāng)前員工和與之具有相同經(jīng)理的前一個(gè)和后一個(gè)三者的平均數(shù)得來；SELECTmanager_id,last_name,hire_date,salary,

AVG(salary)OVER(PARTITIONBYmanager_idORDERBYhire_date

ROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASc_mavg

FROMemployees;MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG

----------------------------------------------------------------

100Kochhar21-SEP-891700017000

100DeHaan13-JAN-931700015000

100Raphaely07-DEC-941100011966.6667

100Kaufling01-MAY-95790010633.3333

100Hartstein17-FEB-96130009633.33333

100Weiss18-JUL-96800011666.6667

100Russell01-OCT-961400011833.33332).CORR

功能描述：返回一對(duì)表達(dá)式的相關(guān)系數(shù)，它是如下的縮寫：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

從統(tǒng)計(jì)上講，相關(guān)性是變量之間關(guān)聯(lián)的強(qiáng)度，變量之間的關(guān)聯(lián)意味著在某種程度

上一個(gè)變量的值可由其它的值進(jìn)行預(yù)測。通過返回一個(gè)-1~1之間的一個(gè)數(shù),相關(guān)

系數(shù)給出了關(guān)聯(lián)的強(qiáng)度，0表示不相關(guān)。

SAMPLE：下例返回1998年月銷售收入和月單位銷售的關(guān)系的累積系數(shù)（本例在SH用戶下運(yùn)行）SELECTt.calendar_month_number,

CORR(SUM(s.amount_sold),SUM(s.quantity_sold))

OVER(ORDERBYt.calendar_month_number)asCUM_CORR

FROMsaless,timest

WHEREs.time_id=t.time_idANDcalendar_year=1998

GROUPBYt.calendar_month_number

ORDERBYt.calendar_month_number;CALENDAR_MONTH_NUMBERCUM_CORR

-------------------------------

1

21

3.994309382

4.852040875

5.846652204

6.871250628

7.910029803

8.917556399

9.920154356

10.86720251

11.844864765

12.903542662

3).COVAR_POP

功能描述：返回一對(duì)表達(dá)式的總體協(xié)方差。

SAMPLE：下例CUM_COVP返回定價(jià)和最小產(chǎn)品價(jià)格的累積總體協(xié)方差SELECTproduct_id,supplier_id,

COVAR_POP(list_price,min_price)

OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,

COVAR_SAMP(list_price,min_price)

OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVS

FROMproduct_informationp

WHEREcategory_id=29

ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS

-----------------------------------------

17751030871473.252946.5

17941030961702.777782554.16667

18251030931926.252568.33333

20041030861591.41989.25

20051030861512.51815

24161030881475.979591721.97619

.

.

4).COVAR_SAMP

功能描述：返回一對(duì)表達(dá)式的樣本協(xié)方差

SAMPLE：下例CUM_COVS返回定價(jià)和最小產(chǎn)品價(jià)格的累積樣本協(xié)方差SELECTproduct_id,supplier_id,

COVAR_POP(list_price,min_price)

OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,

COVAR_SAMP(list_price,min_price)

OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVS

FROMproduct_informationp

WHEREcategory_id=29

ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS

-----------------------------------------

17751030871473.252946.5

17941030961702.777782554.16667

18251030931926.252568.33333

20041030861591.41989.25

20051030861512.51815

24161030881475.979591721.97619

.

.

5).COUNT

功能描述：對(duì)一組內(nèi)發(fā)生的事情進(jìn)行累積計(jì)數(shù)，如果指定*或一些非空常數(shù)，count將對(duì)所有行計(jì)數(shù)，如果指定一個(gè)表達(dá)式，count返回表達(dá)式非空賦值的計(jì)數(shù)，當(dāng)有相同值出現(xiàn)時(shí)，這些相等的值都會(huì)被納入被計(jì)算的值；可以使用DISTINCT來記錄去掉一組中完全相同的數(shù)據(jù)后出現(xiàn)的行數(shù)。

SAMPLE：下面例子中計(jì)算每個(gè)員工在按薪水排序中當(dāng)前行附近薪水在[n-50,n+150]之間的行數(shù)，n表示當(dāng)前行的薪水

例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行沒有，所以count計(jì)數(shù)值cnt3為2（包括自己當(dāng)前行）；cnt2值相當(dāng)于小于等于當(dāng)前行的SALARY值的所有行數(shù)SELECTlast_name,salary,COUNT(*)OVER()AScnt1,

COUNT(*)OVER(ORDERBYsalary)AScnt2,

COUNT(*)OVER(ORDERBYsalaryRANGEBETWEEN50PRECEDING

AND150FOLLOWING)AScnt3FROMemployees;LAST_NAMESALARYCNT1CNT2CNT3

-----------------------------------------------------------------

Olson210010713

Markle220010732

Philtanker220010732

Landry240010758

Gee240010758

Colmenares25001071110

Patel25001071110

.

.

6).CUME_DIST

功能描述：計(jì)算一行在組中的相對(duì)位置，CUME_DIST總是返回大于0、小于或等于1的數(shù)，該數(shù)表示該行在N行中的位置。例如，在一個(gè)3行的組中，返回的累計(jì)分布值為1/3、2/3、3/3

SAMPLE：下例中計(jì)算每個(gè)工種的員工按薪水排序依次累積出現(xiàn)的分布百分比SELECTjob_id,last_name,salary,CUME_DIST()

OVER(PARTITIONBYjob_idORDERBYsalary)AScume_dist

FROMemployeesWHEREjob_idLIKE'PU%';JOB_IDLAST_NAMESALARYCUME_DIST

-------------------------------------------------------

PU_CLERKColmenares2500.2

PU_CLERKHimuro2600.4

PU_CLERKTobias2800.6

PU_CLERKBaida2900.8

PU_CLERKKhoo31001

PU_MANRaphaely110001

7).DENSE_RANK

功能描述：根據(jù)ORDERBY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDERBY子句排序，然后給每一行賦一個(gè)號(hào)，從而形成一個(gè)序列，該序列從1開始，往后累加。每次ORDERBY表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號(hào)（認(rèn)為null時(shí)相等的）。密集的序列返回的時(shí)沒有間隔的數(shù)

SAMPLE：下例中計(jì)算每個(gè)員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號(hào)（注意與RANK函數(shù)的區(qū)別）SELECTd.department_id,e.last_name,e.salary,DENSE_RANK()

OVER(PARTITIONBYe.department_idORDERBYe.salary)asdrank

FROMemployeese,departmentsd

WHEREe.department_id=d.department_id

ANDd.department_idIN('60','90');DEPARTMENT_IDLAST_NAMESALARYDRANK

----------------------------------------------------------

60Lorentz42001

60Austin48002

60Pataballa48002

60Ernst60003

60Hunold90004

90Kochhar170001

90DeHaan170001

90King240002

8).FIRST

功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個(gè)值的行（可能多行，因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄

SAMPLE：下面例子中DENSE_RANK按部門分區(qū)，再按傭金commission_pct排序，F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行，然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值；LAST取出傭金最高的對(duì)應(yīng)的所有行，然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值

SELECTlast_name,department_id,salary,

MIN(salary)KEEP(DENSE_RANKFIRSTORDERBYcommission_pct)

OVER(PARTITIONBYdepartment_id)"Worst",

MAX(salary)KEEP(DENSE_RANKLASTORDERBYcommission_pct)

OVER(PARTITIONBYdepartment_id)"Best"

FROMemployees

WHEREdepartment_idin(20,80)

ORDERBYdepartment_id,salary;LAST_NAMEDEPARTMENT_IDSALARYWorstBest

--------------------------------------------------------------------

Fay206000600013000

Hartstein2013000600013000

Kumar806100610014000

Banda806200610014000

Johnson806200610014000

Ande806400610014000

Lee806800610014000

Tuvault807000610014000

Sewall807000610014000

Marvins807200610014000

Bates807300610014000

.

.

.

9).FIRST_VALUE

功能描述：返回組中數(shù)據(jù)窗口的第一個(gè)值。

SAMPLE：下面例子計(jì)算按部門分區(qū)按薪水排序的數(shù)據(jù)窗口的第一個(gè)值對(duì)應(yīng)的名字，如果薪水的第一個(gè)值有多個(gè)，則從多個(gè)對(duì)應(yīng)的名字中取缺省排序的第一個(gè)名字SELECTdepartment_id,last_name,salary,FIRST_VALUE(last_name)

OVER(PARTITIONBYdepartment_idORDERBYsalaryASC)ASlowest_sal

FROMemployees

WHEREdepartment_idin(20,30);DEPARTMENT_IDLAST_NAMESALARYLOWEST_SAL

--------------------------------------------------------------

20Fay6000Fay

20Hartstein13000Fay

30Colmenares2500Colmenares

30Himuro2600Colmenares

30Tobias2800Colmenares

30Baida2900Colmenares

30Khoo3100Colmenares

30Raphaely11000Colmenares

10).LAG

功能描述：可以訪問結(jié)果集中的其它行而不用進(jìn)行自連接。它允許去處理游標(biāo)，就好像游標(biāo)是一個(gè)數(shù)組一樣。在給定組中可參考當(dāng)前行之前的行，這樣就可以從組中與當(dāng)前行一起選擇以前的行。Offset是一個(gè)正整數(shù)，其默認(rèn)值為1，若索引超出窗口的范圍，就返回默認(rèn)值（默認(rèn)返回的是組中第一行），其相反的函數(shù)是LEAD

SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值SELECTlast_name,hire_date,salary,

LAG(salary,1,0)OVER(ORDERBYhire_date)ASprev_sal

FROMemployees

WHEREjob_id='PU_CLERK';LAST_NAMEHIRE_DATESALARYPREV_SAL

-------------------------------------------------------

Khoo18-5月-9531000

Tobias24-7月-9728003100

Baida24-12月-9729002800

Himuro15-11月-9826002900

Colmenares10-8月-9925002600

11).LAST

功能描述：從DENSE_RANK返回的集合中取出排在最后面的一個(gè)值的行（可能多行，因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄

SAMPLE：下面例子中DENSE_RANK按部門分區(qū)，再按傭金commission_pct排序，F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行，然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值；LAST取出傭金最高的對(duì)應(yīng)的所有行，然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值

SELECTlast_name,department_id,salary,

MIN(salary)KEEP(DENSE_RANKFIRSTORDERBYcommission_pct)

OVER(PARTITIONBYdepartment_id)"Worst",

MAX(salary)KEEP(DENSE_RANKLASTORDERBYcommission_pct)

OVER(PARTITIONBYdepartment_id)"Best"

FROMemployees

WHEREdepartment_idin(20,80)

ORDERBYdepartment_id,salary;LAST_NAMEDEPARTMENT_IDSALARYWorstBest

--------------------------------------------------------------------

Fay206000600013000

Hartstein2013000600013000

Kumar806100610014000

Banda806200610014000

Johnson806200610014000

Ande806400610014000

Lee806800610014000

Tuvault807000610014000

Sewall807000610014000

Marvins807200610014000

Bates807300610014000

.

12).LAST_VALUE

功能描述：返回組中數(shù)據(jù)窗口的最后一個(gè)值。

SAMPLE：下面例子計(jì)算按部門分區(qū)按薪水排序的數(shù)據(jù)窗口的最后一個(gè)值對(duì)應(yīng)的名字，如果薪水的最后一個(gè)值有多個(gè)，則從多個(gè)對(duì)應(yīng)的名字中取缺省排序的最后一個(gè)名字

SELECTdepartment_id,last_name,salary,LAST_VALUE(last_name)

OVER(PARTITIONBYdepartment_idORDERBYsalary)AShighest_sal

FROMemployees

WHEREdepartment_idin(20,30);DEPARTMENT_IDLAST_NAMESALARYHIGHEST_SAL

------------------------------------------------------------

20Fay6000Fay

20Hartstein13000Hartstein

30Colmenares2500Colmenares

30Himuro2600Himuro

30Tobias2800Tobias

30Baida2900Baida

30Khoo3100Khoo

30Raphaely11000Raphaely

13).LEAD

功能描述：LEAD與LAG相反，LEAD可以訪問組中當(dāng)前行之后的行。Offset是一個(gè)正整數(shù)，其默認(rèn)值為1，若索引超出窗口的范圍，就返回默認(rèn)值（默認(rèn)返回的是組中第一行）

SAMPLE：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值SELECTlast_name,hire_date,

LEAD(hire_date,1)OVER(ORDERBYhire_date)AS"NextHired"

FROMemployeesWHEREdepartment_id=30;LAST_NAMEHIRE_DATENextHired

-------------------------------------------

Raphaely07-DEC-9418-MAY-95

Khoo18-MAY-9524-JUL-97

Tobias24-JUL-9724-DEC-97

Baida24-DEC-9715-NOV-98

Himuro15-NOV-9810-AUG-99

Colmenares10-AUG-99

14).MAX

功能描述：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最大值。

SAMPLE：下面例子中dept_max返回當(dāng)前行所在部門的最大薪水值SELECTdepartment_id,last_name,salary,

MAX(salary)OVER(PARTITIONBYdepartment_id)ASdept_max

FROMemployeesWHEREdepartment_idin(10,20,30);DEPARTMENT_IDLAST_NAMESALARYDEPT_MAX

----------------------------------------------------------

10Whalen44004400

20Hartstein1300013000

20Fay600013000

30Raphaely1100011000

30Khoo310011000

30Baida290011000

30Tobias280011000

30Himuro260011000

30Colmenares250011000

15).MIN

功能描述：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最小值。

SAMPLE：下面例子中dept_min返回當(dāng)前行所在部門的最小薪水值SELECTdepartment_id,last_name,salary,

MIN(salary)OVER(PARTITIONBYdepartment_id)ASdept_min

FROMemployeesWHEREdepartment_idin(10,20,30);DEPARTMENT_IDLAST_NAMESALARYDEPT_MIN

----------------------------------------------------------

10Whalen44004400

20Hartstein130006000

20Fay60006000

30Raphaely110002500

30Khoo31002500

30Baida29002500

30Tobias28002500

30Himuro26002500

30Colmenares25002500

16).NTILE

功能描述：將一個(gè)組分為"表達(dá)式"的散列表示，例如，如果表達(dá)式=4，則給組中的每一行分配一個(gè)數(shù)（從1到4），如果組中有20行，則給前5行分配1，給下5行分配2等等。如果組的基數(shù)不能由表達(dá)式值平均分開，則對(duì)這些行進(jìn)行分配時(shí)，組中就沒有任何percentile的行數(shù)比其它percentile的行數(shù)超過一行，最低的percentile是那些擁有額外行的percentile。例如，若表達(dá)式=4，行數(shù)=21，則percentile=1的有5行，percentile=2的有5行等等。

SAMPLE：下例中把6行數(shù)據(jù)分為4份SELECTlast_name,salary,

NTILE(4)OVER(ORDERBYsalaryDESC)ASquartileFROMemployees

WHEREdepartment_id=100;LAST_NAMESALARYQUARTILE

---------------------------------------------

Greenberg120001

Faviet90001

Chen82002

Urman78002

Sciarra77003

Popp69004

17).PERCENT_RANK

功能描述：和CUME_DIST（累積分配）函數(shù)類似，對(duì)于一個(gè)組中給定的行來說，在計(jì)算那行的序號(hào)時(shí)，先減1，然后除以n-1（n為組中所有的行數(shù)）。該函數(shù)總是返回0～1（包括1）之間的數(shù)。

SAMPLE：下例中如果Khoo的salary為2900，則pr值為0.6，因?yàn)镽ANK函數(shù)對(duì)于等值的返回序列值是一樣的SELECTdepartment_id,last_name,salary,

PERCENT_RANK()

OVER(PARTITIONBYdepartment_idORDERBYsalary)ASpr

FROMemployees

WHEREdepartment_id<50

ORDERBYdepartment_id,salary;DEPARTMENT_IDLAST_NAMESALARYPR

----------------------------------------------------------

10Whalen44000

20Fay60000

20Hartstein130001

30Colmenares25000

30Himuro26000.2

30Tobias28000.4

30Baida29000.6

30Khoo31000.8

30Raphaely110001

40Mavris65000

18).PERCENTILE_CONT

功能描述：返回一個(gè)與輸入的分布百分比值相對(duì)應(yīng)的數(shù)據(jù)值，分布百分比的計(jì)算方法見函數(shù)PERCENT_RANK，如果沒有正好對(duì)應(yīng)的數(shù)據(jù)值，就通過下面算法來得到值：

RN=1+(P*(N-1))其中P是輸入的分布百分比值，N是組內(nèi)的行數(shù)

CRN=CEIL(RN)FRN=FLOOR(RN)

if(CRN=FRN=RN)then

(valueofexpressionfromrowatRN)

else

(CRN-RN)*(valueofexpressionforrowatFRN)+

(RN-FRN)*(valueofexpressionforrowatCRN)

注意：本函數(shù)與PERCENTILE_DISC的區(qū)別在找不到對(duì)應(yīng)的分布值時(shí)返回的替代值的計(jì)算方法不同SAMPLE：在下例中，對(duì)于部門60的Percentile_Cont值計(jì)算如下：

P=0.7N=5RN=1+(P*(N-1)=1+(0.7*(5-1))=3.8CRN=CEIL(3.8)=4

FRN=FLOOR(3.8)=3

（4-3.8）*4800+(3.8-3)*6000=5760SELECTlast_name,salary,department_id,

PERCENTILE_CONT(0.7)WITHINGROUP(ORDERBYsalary)

OVER(PARTITIONBYdepartment_id)"Percentile_Cont",

PERCENT_RANK()

OVER(PARTITIONBYdepartment_idORDERBYsalary)"Percent_Rank"

FROMemployeesWHEREdepartment_idIN(30,60);LAST_NAMESALARYDEPARTMENT_IDPercentile_ContPercent_Rank

---------------------------------------------------------------------------

Colmenares25003030000

Himuro26003030000.2

Tobias28003030000.4

Baida29003030000.6

Khoo31003030000.8

Raphaely110003030001

Lorentz42006057600

Austin48006057600.25

Pataballa48006057600.25

Ernst60006057600.75

Hunold90006057601

19).PERCENTILE_DISC

功能描述：返回一個(gè)與輸入的分布百分比值相對(duì)應(yīng)的數(shù)據(jù)值，分布百分比的計(jì)算方法見函數(shù)CUME_DIST，如果沒有正好對(duì)應(yīng)的數(shù)據(jù)值，就取大于該分布值的下一個(gè)值。

注意：本函數(shù)與PERCENTILE_CONT的區(qū)別在找不到對(duì)應(yīng)的分布值時(shí)返回的替代值的計(jì)算方法不同SAMPLE：下例中0.7的分布值在部門30中沒有對(duì)應(yīng)的Cume_Dist值，所以就取下一個(gè)分布值0.83333333所對(duì)應(yīng)的SALARY來替代SELECTlast_name,salary,department_id,

PERCENTILE_DISC(0.7)WITHINGROUP(ORDERBYsalary)

OVER(PARTITIONBYdepartment_id)"Percentile_Disc",

CUME_DIST()OVER(PARTITIONBYdepartment_idORDERBYsalary)"Cume_Dist"

FROMemployees

WHEREdepartment_idin(30,60);LAST_NAMESALARYDEPARTMENT_IDPercentile_DiscCume_Dist

-------------------------------------------------------------------------

Colmenares2500303100.166666667

Himuro2600303100.333333333

Tobias2800303100.5

Baida2900303100.666666667

Khoo3100303100.833333333

Raphaely110003031001

Lorentz4200606000.2

Austin4800606000.6

Pataballa4800606000.6

Ernst6000606000.8

Hunold90006060001

20).RANK

功能描述：根據(jù)ORDERBY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDERBY子句排序，然后給每一行賦一個(gè)號(hào)，從而形成一個(gè)序列，該序列從1開始，往后累加。每次ORDERBY表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號(hào)（認(rèn)為null時(shí)相等的）。然而，如果兩行的確得到同樣的排序，則序數(shù)將隨后跳躍。若兩行序數(shù)為1，則沒有序數(shù)2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。

SAMPLE：下例中計(jì)算每個(gè)員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號(hào)（注意與DENSE_RANK函數(shù)的區(qū)別）SELECTd.department_id,e.last_name,e.salary,RANK()

OVER(PARTITIONBYe.department_idORDERBYe.salary)asdrank

FROMemployeese,departmentsd

WHEREe.department_id=d.department_id

ANDd.department_idIN('60','90');DEPARTMENT_IDLAST_NAMESALARYDRANK

----------------------------------------------------------

60Lorentz42001

60Austin48002

60Pataballa48002

60Ernst60004

60Hunold90005

90Kochhar170001

90DeHaan170001

90King240003

21).RATIO_TO_REPORT

功能描述：該函數(shù)計(jì)算expression/(sum(expression))的值，它給出相對(duì)于總數(shù)的百分比，即當(dāng)前行對(duì)sum(expression)的貢獻(xiàn)。

SAMPLE：下例計(jì)算每個(gè)員工的工資占該類員工總工資的百分比SELECTlast_name,salary,RATIO_TO_REPORT(salary)OVER()ASrr

FROMemployees

WHEREjob_id='PU_CLERK';LAST_NAMESALARYRR

---------------------------------------------

Khoo3100.223021583

Baida2900.208633094

Tobias2800.201438849

Himuro2600.18705036

Colmenares2500.179856115

22).REGR_(LinearRegression)Functions

功能描述：這些線性回歸函數(shù)適合最小二乘法回歸線，有9個(gè)不同的回歸函數(shù)可使用。

REGR_SLOPE：返回斜率，等于COVAR_POP(expr1,expr2)/VAR_POP(expr2)

REGR_INTERCEPT：返回回歸線的y截距，等于

AVG(expr1)-REGR_SLOPE(expr1,expr2)*AVG(expr2)

REGR_COUNT：返回用于填充回歸線的非空數(shù)字對(duì)的數(shù)目

REGR_R2：返回回歸線的決定系數(shù)，計(jì)算式為：

IfVAR_POP(expr2)=0thenreturnNULL

IfVAR_POP(expr1)=0andVAR_POP(expr2)!=0thenreturn1

IfVAR_POP(expr1)>0andVAR_POP(expr2!=0then

returnPOWER(CORR(expr1,expr),2)

REGR_AVGX：計(jì)算回歸線的自變量(expr2)的平均值，去掉了空對(duì)(expr1,expr2)后，等于AVG(expr2)

REGR_AVGY：計(jì)算回歸線的應(yīng)變量(expr1)的平均值，去掉了空對(duì)(expr1,expr2)后，等于AVG(expr1)

REGR_SXX：返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr2)

REGR_SYY：返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr1)

REGR_SXY:返回值等于REGR_COUNT(expr1,expr2)*COVAR_POP(expr1,expr2)（下面的例子都是在SH用戶下完成的）

SAMPLE1：下例計(jì)算1998年最后三個(gè)星期中兩種產(chǎn)品（260和270）在周末的銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積斜率和回歸線的截距SELECTt.fiscal_month_number"Month",t.day_number_in_month"Day",

REGR_SLOPE(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)ASCUM_SLOPE,

REGR_INTERCEPT(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)ASCUM_ICPT

FROMsaless,timest

WHEREs.time_id=t.time_id

ANDd_idIN(270,260)

ANDt.fiscal_year=1998

ANDt.fiscal_week_numberIN(50,51,52)

ANDt.day_number_in_weekIN(6,7)

ORDERBYt.fiscal_month_desc,t.day_number_in_month;MonthDayCUM_SLOPECUM_ICPT

----------------------------------------

1212-681872

1212-681872

1213-20.2448981254.36735

1213-20.2448981254.36735

1219-18.8260871287

122062.4561404125.28655

122062.4561404125.28655

122062.4561404125.28655

122062.4561404125.28655

122667.265822858.9712313

122667.265822858.9712313

122737.5245541284.958221

122737.5245541284.958221

122737.5245541284.958221SAMPLE2：下例計(jì)算1998年4月每天的累積交易數(shù)量SELECTUNIQUEt.day_number_in_month,

REGR_COUNT(s.amount_sold,s.quantity_sold)

OVER(PARTITIONBYt.fiscal_month_numberORDERBYt.day_number_in_month)

"Regr_Count"

FROMsaless,timest

WHEREs.time_id=t.time_id

ANDt.fiscal_year=1998ANDt.fiscal_month_number=4;DAY_NUMBER_IN_MONTHRegr_Count

-----------------------------

1825

21650

32475

43300

.

.

.

2621450

3022200SAMPLE3：下例計(jì)算1998年每月銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積回歸線決定系數(shù)SELECTt.fiscal_month_number,

REGR_R2(SUM(s.amount_sold),SUM(s.quantity_sold))

OVER(ORDERBYt.fiscal_month_number)"Regr_R2"

FROMsaless,timest

WHEREs.time_id=t.time_id

ANDt.fiscal_year=1998

GROUPBYt.fiscal_month_number

ORDERBYt.fiscal_month_number;FISCAL_MONTH_NUMBERRegr_R2

-----------------------------

1

21

3.927372984

4.807019972

5.932745567

6.94682861

7.965342011

8.955768075

9.959542618

10.938618575

11.880931415

12.882769189SAMPLE4：下例計(jì)算1998年12月最后兩周產(chǎn)品260的銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積平均值SELECTt.day_number_in_month,

REGR_AVGY(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)

"Regr_AvgY",

REGR_AVGX(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)

"Regr_AvgX"

FROMsaless,timest

WHEREs.time_id=t.time_id

ANDd_id=260

ANDt.fiscal_month_desc='1998-12'

ANDt.fiscal_week_numberIN(51,52)

ORDERBYt.day_number_in_month;DAY_NUMBER_IN_MONTHRegr_AvgYRegr_AvgX

---------------------------------------

1488224.5

1488224.5

1580122.25

1580122.25

16777.621.6

18642.85714317.8571429

18642.85714317.8571429

20589.516.375

2154415.1111111

22592.36363616.4545455

22592.36363616.4545455

24553.84615415.3846154

24553.84615415.3846154

2652214.5

27578.416.0666667SAMPLE5：下例計(jì)算產(chǎn)品260和270在1998年2月周末銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積REGR_SXY,REGR_SXX,andREGR_SYY統(tǒng)計(jì)值SELECTt.day_number_in_month,

REGR_SXY(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_sxy",

REGR_SYY(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_syy",

REGR_SXX(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_sxx"

FROMsaless,timest

WHEREs.time_id=t.time_id

ANDprod_idIN(270,260)

ANDt.fiscal_month_desc='1998-02'

ANDt.day_number_in_weekIN(6,7)

ORDERBYt.day_number_in_month;DAY_NUMBER_IN_MONTHRegr_sxyRegr_syyRegr_sxx

-------------------------------------------------

118870.42116198.4258.4

118870.42116198.4258.4

118870.42116198.4258.4

118870.42116198.4258.4

718870.42116198.4258.4

818870.42116198.4258.4

1418870.42116198.4258.4

1518870.42116198.4258.4

2118870.42116198.4258.4

2218870.42116198.4258.4

23).ROW_NUMBER

功能描述：返回有序組中一行的偏移量，從而可用于按特定標(biāo)準(zhǔn)排序的行號(hào)。

SAMPLE：下例返回每個(gè)員工再在每個(gè)部門中按員工號(hào)排序后的順序號(hào)SELECTdepartment_id,last_name,employee_id,ROW_NUMBER()

OVER(PARTITIONBYdepartment_idORDERBYemployee_id)ASemp_id

FROMemployees

WHEREdepartment_id<50;DEPARTMENT_IDLAST_NAMEEMPLOYEE_IDEMP_ID

-----------------------------------------------------------

10Whalen2001

20Hartstein2011

20Fay2022

30Raphaely1141

30Khoo1152

30Baida1163

30Tobias1174

30Himuro1185

30Colmenares1196

40Mavris2031

24).STDDEV

功能描述：計(jì)算當(dāng)前行關(guān)于組的標(biāo)準(zhǔn)偏離。（StandardDeviation）

SAMPLE：下例返回部門30按雇傭日期排序的薪水值的累積標(biāo)準(zhǔn)偏離SELECTlast_name,hire_date,salary,

STDDEV(salary)OVER(ORDERBYhire_date)"StdDev"

FROMemployees

WHEREdepartment_id=30;LAST_NAMEHIRE_DATESALARYStdDev

-------------------------------------------------------

Raphaely07-12月-94110000

Khoo18-5月-9531005586.14357

Tobias24-7月-9728004650.0896

Baida24-12月-9729004035.26125

Himuro15-11月-9826003649.2465

Colmenares10-8月-9925003362.58829

25).STDDEV_POP

功能描述：該函數(shù)計(jì)算總體標(biāo)準(zhǔn)偏離，并返回總體變量的平方根，其返回值與VAR_POP函數(shù)的平方根相同。（StandardDeviation－Population）

SAMPLE：下例返回部門20、30、60的薪水值的總體標(biāo)準(zhǔn)偏差SELECTdepartment_id,last_name,salary,

STDDEV_POP(salary)OVER(PARTITIONBYdepartment_id)ASpop_std

FROMemployees

WHEREdepartment_idin(20,30,60);DEPARTMENT_IDLAST_NAMESALARYPOP_STD

----------------------------------------------------------

20Hartstein130003500

20Fay60003500

30Raphaely110003069.6091

30Khoo31003069.6091

30Baida29003069.6091

30Colmenares25003069.6091

30Himuro26003069.6091

30Tobias28003069.6091

60Hunold90001722.32401

60Ernst60001722.32401

60Austin48001722.32401

60Pataballa48001722.32401

60Lorentz42001722.32401

26).STDDEV_SAMP

功能描述：該函數(shù)計(jì)算累積樣本標(biāo)準(zhǔn)偏離，并返回總體變量的平方根，其返回值與VAR_POP函數(shù)的平方根相同。（StandardDeviation－Sample）

SAMPLE：下例返回部門20、30、60的薪水值的樣本標(biāo)準(zhǔn)偏差SELECTdepartment_id,last_name,hire_date,salary,

STDDEV_SAMP(salary)OVER

(PARTITIONBYdepartment_idORDERBYhire_date

ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScum_sdev

FROMemployees

WHEREdepartment_idin(20,30,60);DEPARTMENT_IDLAST_NAMEHIRE_DATESALARYCUM_SDEV

--------------------------------------------------------------------

20Hartstein17-2月-9613000

20Fay17-8月-9760004949.74747

30Raphaely07-12月-9411000

30Khoo18-5月-9531005586.14357

30Tobias24-7月-9728004650.0896

30Baida24-12月-9729004035.26125

30Himuro15-11月-9826003649.2465

30Colmenares10-8月-9925003362.58829

60Hunold03-1月-909000

60Ernst21-5月-9160002121.32034

60Austin25-6月-9748002163.33077

60Pataballa05-2月-9848001982.42276

60Lorentz07-2月-9942001925.61678

27).SUM

功能描述：該函數(shù)計(jì)算組中表達(dá)式的累積和。

SAMPLE：下例計(jì)算同一經(jīng)理下員工的薪水累積值SELECTmanager_id,last_name,salary,

SUM(salary)OVER(PARTITIONBYmanager_idORDERBYsalary

RANGEUNBOUNDEDPRECEDING)l_csum

FROMemployees

WHEREmanager_idin(101,103,108);MANAGER_IDLAST_NAMESALARYL_CSUM

-------------------------------------------------------

101Whalen44004400

101Mavris650010900

101Baer1000020900

101Greenberg1200044900

101Higgins1200044900

103Lorentz42004200

103Austin480013800

103Pataballa480013800

103Ernst600019800

108Popp69006900

108Sciarra770014600

108Urman780022400

108Chen820030600

108Faviet900039600

28).VAR_POP

功能描述：（VariancePopulation）該函數(shù)返回非空集合的總體變量（忽略null），VAR_POP進(jìn)行如下計(jì)算：

(SUM(expr2)-SUM(expr)2/COUNT(expr))/COUNT(expr)

SAMPLE：下例計(jì)算1998年每月銷售的累積總體和樣本變量（本例在SH用戶下運(yùn)行）SELECTt.calendar_month_desc,

VAR_POP(SUM(s.amount_sold))

OVER(ORDERBYt.calendar_month_desc)"Var_Pop",

VAR_SAMP(SUM(s.amount_sold))

OVER(ORDERBYt.calendar_month_desc)"Var_Samp"

FROMsaless,timest

WHEREs.time_id=t.time_idANDt.calendar_year=1998

GROUPBYt.calendar_month_desc;CALENDARVar_PopVar_Samp

----------------------------

1998-010

1998-026.1321E+111.2264E+12

1998-034.7058E+117.0587E+11

1998-044.6929E+116.2572E+11

1998-051.5524E+121.9405E+12

1998-062.3711E+122.8453E+12

1998-073.7464E+124.3708E+12

1998-083.7852E+124.3260E+12

1998-093.5753E+124.0222E+12

1998-103.4343E+123.8159E+12

1998-113.4245E+123.7669E+12

1998-124.8937E+125.3386E+12

29).VAR_SAMP

功能描述：（VarianceSample）該函數(shù)返回非空集合的樣本變量（忽略null），VAR_POP進(jìn)行如下計(jì)算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

SAMPLE：下例計(jì)算1998年每月銷售的累積總體和樣本變量SELECTt.calendar_month_desc,

VAR_POP(SUM(s.amount_sold))

OVER(ORDERBYt.calendar_month_desc)"Var_Pop",

VAR_SAMP(SUM(s.amount_sold))

OVER(ORDERBYt.calendar_month_desc)"Var_Samp"

FROMsaless,timest

WHEREs.time_id=t.time_idANDt.calendar_year=1998

GROUPBYt.calendar_month_desc;CALENDARVar_PopVar_Samp

----------------------------

1998-010

1998-026.1321E+111.2264E+12

1998-034.7058E+117.0587E+11

1998-044.6929E+116.2572E+11

1998-051.5524E+121.9405E+12

1998-062.3711E+122.8453E+12

1998-073.7464E+124.3708E+12

1998-083.7852E+124.3260E+12

1998-093.5753E+124.0222E+12

1998-103.4343E+123.8159E+12

1998-113.4245E+123.7669E+12

1998-124.8937E+125.3386E+12

30).VARIANCE

功能描述：該函數(shù)返回表達(dá)式的變量，Oracle計(jì)算該變量如下：

如果表達(dá)式中行數(shù)為1，則返回0

如果表達(dá)式中行數(shù)大于1，則返回VAR_SAMP

SAMPLE：下例返回部門30按雇傭日期排序的薪水值的累積變化SELECTlast_name,salary,VARIANCE(salary)

OVER(ORDERBYhire_date)"Variance"

FROMemployees

WHEREdepartment_id=30;LAST_NAMESALARYVariance

---------------------------------------------

Raphaely110000

Khoo310031205000

Tobias280021623333.3

Baida290016283333.3

Himuro260013317000

Colmenares250011307000=====================================

連續(xù)求和問題：

selectname,sum(cnt)over(orderbyrownum)fromt1;

Oracle分析函數(shù)——數(shù)據(jù)分布函數(shù)及HYPERLINK報(bào)表函數(shù)CUME_DIST功能描述：計(jì)算一行在組中的相對(duì)位置，CUME_DIST總是返回大于0、小于或等于1的數(shù)，該數(shù)表示該行在N行中的位置。例如，在一個(gè)3行的組中，返回的累計(jì)分布值為1/3、2/3、3/3SAMPLE：下例中計(jì)算每個(gè)部門的員工按薪水排序依次累積出現(xiàn)的分布百分比SELECTdepartment_id,first_name||''||last_nameemployee_name,salary,CUME_DIST()OVER(PARTITIONBYdepartment_idORDERBYsalary)AScume_distFROMemployeesNTILE功能描述：將一個(gè)組分為"表達(dá)式"的散列表示，例如，如果表達(dá)式=4，則給組中的每一行分配一個(gè)數(shù)（從1到4），如果組中有20行，則給前5行分配1，給下5行分配2等等。如果組的基數(shù)不能由表達(dá)式值平均分開，則對(duì)這些行進(jìn)行分配時(shí)，組中就沒有任何percentile的行數(shù)比其它percentile的行數(shù)超過一行，最低的percentile是那些擁有額外行的percentile。例如，若表達(dá)式=4，行數(shù)=21，則percentile=1的有5行，percentile=2的有5行等等。SAMPLE：下例中把6行數(shù)據(jù)分為4份SELECTdepartment_id,first_name||''||last_nameemployee_name,salary,NTILE(4)OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)ASquartileFROMemployeesPERCENT_RANK功能描述：和CUME_DIST（累積分配）函數(shù)類似，對(duì)于一個(gè)組中給定的行來說，在計(jì)算那行的序號(hào)時(shí)，先減1，然后除以n-1（n為組中所有的行數(shù)）。該函數(shù)總是返回0～1（包括1）之間的數(shù)。SAMPLE：下例中如果Khoo的salary為2900，則pr值為0.6，因?yàn)镽ANK函數(shù)對(duì)于等值的返回序列值是一樣的SELECT

department_id,first_name||''||last_nameemployee_name,salary,PERCENT_RANK()OVER(PARTITIONBYdepartment_idORDERBYsalary)ASprFROMemployeesORDERBYdepartment_id,salary;PERCENTILE_DISC功能描述：返回一個(gè)與輸入的分布百分比值相對(duì)應(yīng)的數(shù)據(jù)值，分布百分比的計(jì)算方法見函數(shù)CUME_DIST，如果沒有正好對(duì)應(yīng)的數(shù)據(jù)值，就取大于該分布值的下一個(gè)值。注意：本函數(shù)與PERCENTILE_CONT的區(qū)別在找不到對(duì)應(yīng)的分布值時(shí)返回的替代值的計(jì)算方法不同SAMPLE：下例中0.7的分布值在部門30中沒有對(duì)應(yīng)的Cume_Dist值，所以就取下一個(gè)分布值0.83333333所對(duì)應(yīng)的SALARY來替代SELECTdepartment_id,first_name||''||last_nameemployee_name,salary,PERCENTILE_DISC(0.7)WITHINGROUP(ORDERBYsalary)OVER(PARTITIONBYdepartment_id)"Percentile_Disc",CUME_DIST()OVER(PARTITIONBYdepartment_idORDERBYsalary)"Cume_Dist"FROMemployees<!--[if!vml]--><!--[endif]-->PERCENTILE_CONT功能描述：返回一個(gè)與輸入的分布百分比值相對(duì)應(yīng)的數(shù)據(jù)值，分布百分比的計(jì)算方法見函數(shù)PERCENT_RANK，如果沒有正好對(duì)應(yīng)