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 Baida Tobias Himuro Colmenares
REGR_ (Linear Regression) Functions功能描述這些線性回歸函數適合最小二乘法回歸線有個不同的回歸函數可使用
REGR_SLOPE返回斜率等於COVAR_POP(expr expr) / VAR_POP(expr) REGR_INTERCEPT返回回歸線的y截距等於AVG(expr) REGR_SLOPE(expr expr) * AVG(expr)
REGR_COUNT返回用於填充回歸線的非空數字對的數目
REGR_R返回回歸線的決定系數計算式為
If VAR_POP(expr) = then return NULL
If VAR_POP(expr) = and VAR_POP(expr) != then return If VAR_POP(expr) > and VAR_POP(expr != then return POWER(CORR(exprexpr)) REGR_AVGX計算回歸線的自變量(expr)的平均值去掉了空對(expr expr)後等於AVG(expr) REGR_AVGY計算回歸線的應變量(expr)的平均值去掉了空對(expr expr)後等於AVG(expr) REGR_SXX 返回值等於REGR_COUNT(expr expr) * VAR_POP(expr) REGR_SYY 返回值等於REGR_COUNT(expr expr) * VAR_POP(expr) REGR_SXY: 返回值等於REGR_COUNT(expr expr) * COVAR_POP(expr expr)
（下面的例子都是在SH用戶下完成的）
SAMPLE 下例計算年最後三個星期中兩種產品（和）在周末的銷售量中已開發票數量和總數量的累積斜率和回歸線的截距
SELECT tfiscal_month_number Month tday_number_in_month Day REGR_SLOPE(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_SLOPE REGR_INTERCEPT(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_ICPT FROM sales s times t WHERE stime_id = ttime_id AND sprod_id IN ( ) AND tfiscal_year= AND tfiscal_week_number IN ( ) AND tday_number_in_week IN () ORDER BY tfiscal_month_desc tday_number_in_month;
Month Day CUM_SLOPE CUM_ICPT
SAMPLE 下例計算年月每天的累積交易數量
SELECT UNIQUE tday_number_in_month REGR_COUNT(samount_sold squantity_sold) OVER (PARTITION BY tfiscal_month_number ORDER BY tday_number_in_month)
Regr_Count
FROM sales s times t WHERE stime_id = ttime_id AND tfiscal_year = AND tfiscal_month_number = ;
DAY_NUMBER_IN_MONTH Regr_Count
SAMPLE 下例計算年每月銷售量中已開發票數量和總數量的累積回歸線決定系數
SELECT tfiscal_month_number REGR_R(SUM(samount_sold) SUM(squantity_sold)) OVER (ORDER BY tfiscal_month_number) Regr_R FROM sales s times t WHERE stime_id = ttime_id AND tfiscal_year = GROUP BY tfiscal_month_number ORDER BY tfiscal_month_number;
FISCAL_MONTH_NUMBER Regr_R
SAMPLE 下例計算年月最後兩周產品的銷售量中已開發票數量和總數量的累積平均值
SELECT tday_number_in_month REGR_AVGY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) Regr_AvgY REGR_AVGX(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month)
Regr_AvgX
FROM sales s times t WHERE stime_id = ttime_id AND sprod_id = AND tfiscal_month_desc = AND tfiscal_week_number IN ( ) ORDER BY tday_number_in_month;
DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX
SAMPLE 下例計算產品和在年月周末銷售量中已開發票數量和總數量的累積REGR_SXY REGR_SXX and REGR_SYY統計值
SELECT tday_number_in_month REGR_SXY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxy REGR_SYY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_syy REGR_SXX(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxx FROM sales s times t WHERE stime_id = ttime_id AND prod_id IN ( ) AND tfiscal_month_desc = AND tday_number_in_week IN () ORDER BY tday_number_in_month;
DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx
ROW_NUMBER功能描述返回有序組中一行的偏移量從而可用於按特定標准排序的行號
SAMPLE下例返回每個員工再在每個部門中按員工號排序後的順序號
SELECT department_id last_name employee_id ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < ;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
Whalen
Hartstein
Fay
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Mavris
STDDEV功能描述計算當前行關於組的標准偏離（Standard Deviation）
SAMPLE下例返回部門按雇傭日期排序的薪水值的累積標准偏離
SELECT last_name hire_datesalary
STDDEV(salary) OVER (ORDER BY hire_date) StdDev
FROM employees
WHERE department_id = ;
LAST_NAME HIRE_DATE SALARY StdDev
Raphaely 月
Khoo 月 Tobias 月 Baida 月 Himuro 月 Colmenares 月
STDDEV_POP功能描述該函數計算總體標准偏離並返回總體變量的平方根其返回值與VAR_POP函數的平方根相同（Standard Deviation－Population）
SAMPLE下例返回部門的薪水值的總體標准偏差
SELECT department_id last_name salary
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in ();
DEPARTMENT_ID LAST_NAME SALARY POP_STD
Hartstein
Fay
Raphaely Khoo Baida Colmenares Himuro Tobias Hunold Ernst Austin Pataballa Lorentz
STDDEV_SAMP功能描述 該函數計算累積樣本標准偏離並返回總體變量的平方根其返回值與VAR_POP函數的平方根相同（Standard Deviation－Sample）
SAMPLE下例返回部門的薪水值的樣本標准偏差
SELECT department_id last_name hire_date salary
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in ();
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
Hartstein 月
Fay 月
Raphaely 月
Khoo 月 Tobias 月 Baida 月 Himuro 月 Colmenares 月
Hunold 月
Ernst 月 Austin 月 Pataballa 月 Lorentz 月
SUM功能描述該函數計算組中表達式的累積和
SAMPLE下例計算同一經理下員工的薪水累積值
SELECT manager_id last_name salary
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in ();
MANAGER_ID LAST_NAME SALARY L_CSUM
Whalen
Mavris
Baer
Greenberg
Higgins
Lorentz
Austin
Pataballa
Ernst
Popp
Sciarra
Urman
Chen
Faviet
VAR_POP功能描述（Variance Population）該函數返回非空集合的總體變量（忽略null）VAR_POP進行如下計算
(SUM(expr) SUM(expr) / COUNT(expr)) / COUNT(expr)
SAMPLE下例計算年每月銷售的累積總體和樣本變量（本例在SH用戶下運行）
SELECT tcalendar_month_desc VAR_POP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Pop VAR_SAMP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Samp FROM sales s times t WHERE stime_id = ttime_id AND tcalendar_year = GROUP BY tcalendar_month_desc;
CALENDAR Var_Pop Var_Samp
E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+
VAR_SAMP功能描述（Variance Sample）該函數返回非空集合的樣本變量（忽略null）VAR_POP進行如下計算
(SUM(expr*expr)SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr))
SAMPLE下例計算年每月銷售的累積總體和樣本變量
SELECT tcalendar_month_desc VAR_POP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Pop VAR_SAMP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Samp FROM sales s times t WHERE stime_id = ttime_id AND tcalendar_year = GROUP BY tcalendar_month_desc;
CALENDAR Var_Pop Var_Samp
E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+
VARIANCE功能描述該函數返回表達式的變量Oracle計算該變量如下如果表達式中行數為則返回如果表達式中行數大於則返回VAR_SAMP
SAMPLE下例返回部門按雇傭日期排序的薪水值的累積變化
SELECT last_name salary VARIANCE(salary)
OVER (ORDER BY hire_date) Variance
FROM employees
WHERE department_id = ;
LAST_NAME SALARY Variance
Raphaely
Khoo
Tobias Baida
Himuro
Colmenares
From:http://tw.wingwit.com/Article/program/Oracle/201311/17896.html
