oracle 9i分析函式參考手冊 (二)

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