PERCENTILE_DISC
功能描述：返回一個與輸入的分布百分比值相對應的數據值，分布百分比的計算方法見函數CUME_DIST，如果沒有正好對應的數據值，就取大于該分布值的下一個值。
注意：本函數與PERCENTILE_CONT的區別在找不到對應的分布值時返回的替代值的計算方法不同

SAMPLE：下例中0.7的分布值在部門30中沒有對應的Cume_Dist值，所以就取下一個分布值0.83333333所對應的SALARY來替代

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
功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨 后跳躍。若兩行序數為1，則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。
SAMPLE：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與DENSE_RANK函數的區別）

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
功能描述：該函數計算expression/(sum(expression))的值，它給出相對于總數的百分比，即當前行對sum(expression)的貢獻。
SAMPLE：下例計算每個員工的工資占該類員工總工資的百分比

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
功能描述：這些線性回歸函數適合最小二乘法回歸線，有9個不同的回歸函數可使用。
??????????? REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)
??????????? REGR_INTERCEPT：返回回歸線的y截距，等于
??????????????????????????? AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
??????????? REGR_COUNT：返回用于填充回歸線的非空數字對的數目
??????????? REGR_R2：返回回歸線的決定系數，計算式為：
???????????????????? 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：計算回歸線的自變量(expr2)的平均值，去掉了空對(expr1, expr2)后，等于AVG(expr2)
??????????? REGR_AVGY：計算回歸線的應變量(expr1)的平均值，去掉了空對(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用戶下完成的）
SAMPLE 1：下例計算1998年最后三個星期中兩種產品（260和270）在周末的銷售量中已開發票數量和總數量的累積斜率和回歸線的截距

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：下例計算1998年4月每天的累積交易數量

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：下例計算1998年每月銷售量中已開發票數量和總數量的累積回歸線決定系數

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：下例計算1998年12月最后兩周產品260的銷售量中已開發票數量和總數量的累積平均值

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 分析函數參考手冊