﻿ 110個oracle常用函數總結(8)_電腦知識網

110個oracle常用函數總結(8)

2022-06-13   來源: Oracle

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