PERCENTILE_DISC
功能描述：返回一個(gè)與輸入的分布百分比值相對(duì)應(yīng)的數(shù)據(jù)值，分布百分比的計(jì)算方法見(jiàn)函數(shù)CUME_DIST，如果沒(méi)有正好對(duì)應(yīng)的數(shù)據(jù)值，就取大于該分布值的下一個(gè)值。
注意：本函數(shù)與PERCENTILE_CONT的區(qū)別在找不到對(duì)應(yīng)的分布值時(shí)返回的替代值的計(jì)算方法不同

SAMPLE：下例中0.7的分布值在部門(mén)30中沒(méi)有對(duì)應(yīng)的Cume_Dist值，所以就取下一個(gè)分布值0.83333333所對(duì)應(yīng)的SALARY來(lái)替代

SELECT last_name, salary, department_id,
???????? PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary )
???????? OVER (PARTITION BY department_id) "Percentile_Disc",
???????? CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary)??????? "Cume_Dist"
??? FROM employees
WHERE department_id in (30, 60);

LAST_NAME?????????????????????? SALARY DEPARTMENT_ID Percentile_Disc??? Cume_Dist
------------------------- ---------- ------------- --------------- ----------
Colmenares??????????????????????? 2500????????????? 30????????????? 3100 .166666667
Himuro??????????????????????????? 2600????????????? 30????????????? 3100 .333333333
Tobias??????????????????????????? 2800????????????? 30????????????? 3100?????????? .5
Baida???????????????????????????? 2900????????????? 30????????????? 3100 .666666667
Khoo????????????????????????????? 3100????????????? 30????????????? 3100 .833333333
Raphaely???????????????????????? 11000????????????? 30????????????? 3100??????????? 1
Lorentz?????????????????????????? 4200????????????? 60????????????? 6000?????????? .2
Austin??????????????????????????? 4800????????????? 60????????????? 6000?????????? .6
Pataballa???????????????????????? 4800????????????? 60????????????? 6000?????????? .6
Ernst???????????????????????????? 6000????????????? 60????????????? 6000?????????? .8
Hunold??????????????????????????? 9000????????????? 60????????????? 6000??????????? 1


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

SELECT d.department_id , e.last_name, e.salary, RANK()
????????? OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
??? FROM employees e, departments d
WHERE e.department_id = d.department_id
???? AND d.department_id IN ('60', '90');

DEPARTMENT_ID LAST_NAME?????????????????????? SALARY??????? DRANK
------------- ------------------------- ---------- ----------
???????????? 60 Lorentz?????????????????????????? 4200??????????? 1
???????????? 60 Austin??????????????????????????? 4800??????????? 2
???????????? 60 Pataballa???????????????????????? 4800??????????? 2
???????????? 60 Ernst???????????????????????????? 6000??????????? 4
???????????? 60 Hunold??????????????????????????? 9000??????????? 5
???????????? 90 Kochhar????????????????????????? 17000??????????? 1
???????????? 90 De Haan????????????????????????? 17000??????????? 1
???????????? 90 King???????????????????????????? 24000??????????? 3


RATIO_TO_REPORT
功能描述：該函數(shù)計(jì)算expression/(sum(expression))的值，它給出相對(duì)于總數(shù)的百分比，即當(dāng)前行對(duì)sum(expression)的貢獻(xiàn)。
SAMPLE：下例計(jì)算每個(gè)員工的工資占該類(lèi)員工總工資的百分比

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
??? FROM employees
WHERE job_id = 'PU_CLERK';

LAST_NAME?????????????????????? SALARY?????????? RR
------------------------- ---------- ----------
Khoo????????????????????????????? 3100 .223021583
Baida???????????????????????????? 2900 .208633094
Tobias??????????????????????????? 2800 .201438849
Himuro??????????????????????????? 2600??? .18705036
Colmenares??????????????????????? 2500 .179856115


REGR_ (Linear Regression) 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ì)算式為：
???????????????????? If VAR_POP(expr2)??? = 0 then return NULL
???????????????????? If VAR_POP(expr1)??? = 0 and VAR_POP(expr2) != 0 then return 1
???????????????????? If VAR_POP(expr1)??? > 0 and VAR_POP(expr2??? != 0 then
??????????????????????? return POWER(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用戶(hù)下完成的）
SAMPLE 1：下例計(jì)算1998年最后三個(gè)星期中兩種產(chǎn)品（260和270）在周末的銷(xiāo)售量中已開(kāi)發(fā)票數(shù)量和總數(shù)量的累積斜率和回歸線的截距

SELECT t.fiscal_month_number "Month", t.day_number_in_month "Day",
???????? REGR_SLOPE(s.amount_sold, s.quantity_sold)
?????????? OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE,
???????? REGR_INTERCEPT(s.amount_sold, s.quantity_sold)
?????????? OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_ICPT
??? FROM sales s, times t
WHERE s.time_id = t.time_id
???? AND s.prod_id IN (270, 260)
???? AND t.fiscal_year=1998
???? AND t.fiscal_week_number IN (50, 51, 52)
???? AND t.day_number_in_week IN (6,7)
???? ORDER BY t.fiscal_month_desc, t.day_number_in_month;

?????? Month????????? Day??? CUM_SLOPE???? CUM_ICPT
---------- ---------- ---------- ----------
????????? 12?????????? 12????????? -68???????? 1872
????????? 12?????????? 12????????? -68???????? 1872
????????? 12?????????? 13 -20.244898 1254.36735
????????? 12?????????? 13 -20.244898 1254.36735
????????? 12?????????? 19 -18.826087???????? 1287
????????? 12?????????? 20 62.4561404??? 125.28655
????????? 12?????????? 20 62.4561404??? 125.28655
????????? 12?????????? 20 62.4561404??? 125.28655
????????? 12?????????? 20 62.4561404??? 125.28655
????????? 12?????????? 26 67.2658228 58.9712313
????????? 12?????????? 26 67.2658228 58.9712313
????????? 12?????????? 27 37.5245541 284.958221
????????? 12?????????? 27 37.5245541 284.958221
????????? 12?????????? 27 37.5245541 284.958221

SAMPLE 2：下例計(jì)算1998年4月每天的累積交易數(shù)量

SELECT UNIQUE t.day_number_in_month,
???????? REGR_COUNT(s.amount_sold, s.quantity_sold)
????????? OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month)
????? "Regr_Count"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 1998 AND t.fiscal_month_number = 4;

DAY_NUMBER_IN_MONTH Regr_Count
------------------- ----------
??????????????????? 1????????? 825
??????????????????? 2???????? 1650
??????????????????? 3???????? 2475
??????????????????? 4???????? 3300
.
.
.
?????????????????? 26??????? 21450
?????????????????? 30??????? 22200

SAMPLE 3：下例計(jì)算1998年每月銷(xiāo)售量中已開(kāi)發(fā)票數(shù)量和總數(shù)量的累積回歸線決定系數(shù)

SELECT t.fiscal_month_number,
???????? REGR_R2(SUM(s.amount_sold), SUM(s.quantity_sold))
??????????? OVER (ORDER BY t.fiscal_month_number) "Regr_R2"
???? FROM sales s, times t
???? WHERE s.time_id = t.time_id
???? AND t.fiscal_year = 1998
???? GROUP BY t.fiscal_month_number
???? ORDER BY t.fiscal_month_number;

FISCAL_MONTH_NUMBER????? Regr_R2
------------------- ----------
??????????????????? 1
??????????????????? 2??????????? 1
??????????????????? 3 .927372984
??????????????????? 4 .807019972
??????????????????? 5 .932745567
??????????????????? 6??? .94682861
??????????????????? 7 .965342011
??????????????????? 8 .955768075
??????????????????? 9 .959542618
?????????????????? 10 .938618575
?????????????????? 11 .880931415
?????????????????? 12 .882769189

SAMPLE 4：下例計(jì)算1998年12月最后兩周產(chǎn)品260的銷(xiāo)售量中已開(kāi)發(fā)票數(shù)量和總數(shù)量的累積平均值

SELECT t.day_number_in_month,
???? REGR_AVGY(s.amount_sold, s.quantity_sold)
??????? OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
??????? "Regr_AvgY",
???? REGR_AVGX(s.amount_sold, s.quantity_sold)
??????? OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
??????? "Regr_AvgX"
???? FROM sales s, times t
???? WHERE s.time_id = t.time_id
??????? AND s.prod_id = 260
??????? AND t.fiscal_month_desc = '1998-12'
??????? AND t.fiscal_week_number IN (51, 52)
???? ORDER BY t.day_number_in_month;

DAY_NUMBER_IN_MONTH??? Regr_AvgY??? Regr_AvgX
------------------- ---------- ----------
?????????????????? 14????????? 882???????? 24.5
?????????????????? 14????????? 882???????? 24.5
?????????????????? 15????????? 801??????? 22.25
?????????????????? 15????????? 801??????? 22.25
?????????????????? 16??????? 777.6???????? 21.6
?????????????????? 18 642.857143 17.8571429
?????????????????? 18 642.857143 17.8571429
?????????????????? 20??????? 589.5?????? 16.375
?????????????????? 21????????? 544 15.1111111
?????????????????? 22 592.363636 16.4545455
?????????????????? 22 592.363636 16.4545455
?????????????????? 24 553.846154 15.3846154
?????????????????? 24 553.846154 15.3846154
?????????????????? 26????????? 522???????? 14.5
?????????????????? 27??????? 578.4 16.0666667

Oracle 9i 分析函數(shù)參考手冊(cè)