oracle 9i分析函数参考手册 (二)

上一篇 / 下一篇 2008-06-16 17:17:27 / 个人分类：pl/sql

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 < 50;

DEPARTMENT_ID LAST_NAME                EMPLOYEE_ID    EMP_ID
------------- ------------------------- ----------- ----------
         10 Whalen                         200       1
         20 Hartstein                      201       1
         20 Fay                            202       2
         30 Raphaely                         114       1
         30 Khoo                            115       2
         30 Baida                            116       3
         30 Tobias                         117       4
         30 Himuro                         118       5
         30 Colmenares                      119       6
         40 Mavris                         203       1

STDDEV
功能描述：计算当前行关于组的标准偏离。（Standard Deviation）
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积标准偏离

SELECT last_name, hire_date,salary,
      STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
  FROM employees
WHERE department_id = 30;

LAST_NAME                HIRE_DATE    SALARY    StdDev
------------------------- ---------- ---------- ----------
Raphaely                07-12月-94    11000       0
Khoo                   18-5月 -95    3100 5586.14357
Tobias                   24-7月 -97    2800  4650.0896
Baida                   24-12月-97    2900 4035.26125
Himuro                   15-11月-98    2600  3649.2465
Colmenares             10-8月 -99    2500 3362.58829

STDDEV_POP
功能描述：该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Population）
SAMPLE：下例返回部门20、30、60的薪水值的总体标准偏差

SELECT department_id, last_name, salary,
   STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
  FROM employees
WHERE department_id in (20,30,60);

DEPARTMENT_ID LAST_NAME                   SALARY POP_STD
------------- ------------------------- ---------- ----------
         20 Hartstein                   13000    3500
         20 Fay                            6000    3500
         30 Raphaely                      11000  3069.6091
         30 Khoo                         3100  3069.6091
         30 Baida                         2900  3069.6091
         30 Colmenares                   2500  3069.6091
         30 Himuro                         2600  3069.6091
         30 Tobias                         2800  3069.6091
         60 Hunold                         9000 1722.32401
         60 Ernst                         6000 1722.32401
         60 Austin                         4800 1722.32401
         60 Pataballa                      4800 1722.32401
         60 Lorentz                      4200 1722.32401

STDDEV_SAMP
功能描述：该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Sample）
SAMPLE：下例返回部门20、30、60的薪水值的样本标准偏差

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 (20,30,60);

DEPARTMENT_ID LAST_NAME                HIRE_DATE    SALARY CUM_SDEV
------------- ------------------------- ---------- ---------- ----------
         20 Hartstein                17-2月 -96    13000
         20 Fay                      17-8月 -97    6000 4949.74747
         30 Raphaely                07-12月-94    11000
         30 Khoo                   18-5月 -95    3100 5586.14357
         30 Tobias                   24-7月 -97    2800  4650.0896
         30 Baida                   24-12月-97    2900 4035.26125
         30 Himuro                   15-11月-98    2600  3649.2465
         30 Colmenares             10-8月 -99    2500 3362.58829
         60 Hunold                   03-1月 -90    9000
         60 Ernst                   21-5月 -91    6000 2121.32034
         60 Austin                   25-6月 -97    4800 2163.33077
         60 Pataballa                05-2月 -98    4800 1982.42276
         60 Lorentz                07-2月 -99    4200 1925.61678

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 (101,103,108);

MANAGER_ID LAST_NAME                   SALARY    L_CSUM
---------- ------------------------- ---------- ----------
   101 Whalen                         4400    4400
   101 Mavris                         6500    10900
   101 Baer                         10000    20900
   101 Greenberg                   12000    44900
   101 Higgins                      12000    44900
   103 Lorentz                      4200    4200
   103 Austin                         4800    13800
   103 Pataballa                      4800    13800
   103 Ernst                         6000    19800
   108 Popp                         6900    6900
   108 Sciarra                      7700    14600
   108 Urman                         7800    22400
   108 Chen                         8200    30600
   108 Faviet                         9000    39600


VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：
      (SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量（本例在SH用户下运行）

SELECT t.calendar_month_desc,
   VAR_POP(SUM(s.amount_sold))
      OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
   VAR_SAMP(SUM(s.amount_sold))
      OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
  FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;

CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01          0
1998-02  6.1321E+11 1.2264E+12
1998-03  4.7058E+11 7.0587E+11
1998-04  4.6929E+11 6.2572E+11
1998-05  1.5524E+12 1.9405E+12
1998-06  2.3711E+12 2.8453E+12
1998-07  3.7464E+12 4.3708E+12
1998-08  3.7852E+12 4.3260E+12
1998-09  3.5753E+12 4.0222E+12
1998-10  3.4343E+12 3.8159E+12
1998-11  3.4245E+12 3.7669E+12
1998-12  4.8937E+12 5.3386E+12

VAR_SAMP
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算：
      (SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量

SELECT t.calendar_month_desc,
      VAR_POP(SUM(s.amount_sold))
      OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
      VAR_SAMP(SUM(s.amount_sold))
      OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
  FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;

CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01          0
1998-02  6.1321E+11 1.2264E+12
1998-03  4.7058E+11 7.0587E+11
1998-04  4.6929E+11 6.2572E+11
1998-05  1.5524E+12 1.9405E+12
1998-06  2.3711E+12 2.8453E+12
1998-07  3.7464E+12 4.3708E+12
1998-08  3.7852E+12 4.3260E+12
1998-09  3.5753E+12 4.0222E+12
1998-10  3.4343E+12 3.8159E+12
1998-11  3.4245E+12 3.7669E+12
1998-12  4.8937E+12 5.3386E+12

VARIANCE
功能描述：该函数返回表达式的变量，Oracle计算该变量如下：
      如果表达式中行数为1，则返回0
      如果表达式中行数大于1，则返回VAR_SAMP
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积变化

SELECT last_name, salary, VARIANCE(salary)
      OVER (ORDER BY hire_date) "Variance"
  FROM employees
WHERE department_id = 30;

LAST_NAME                   SALARY Variance
------------------------- ---------- ----------
Raphaely                      11000       0
Khoo                         3100 31205000
Tobias                         2800 21623333.3
Baida                         2900 16283333.3
Himuro                         2600 13317000
Colmenares                   2500 11307000