熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 16:08:56  來源: 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.