對Oracle分析函數的初步理解

版本：10.1 樣本資料庫：ORCL  模式：hr

開窗函數的的理解：
開窗函數指定了分析函數工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：
over（order by salary） 按照salary排序進行累計，order by是個預設的開窗函數
over（partition by deptno）按照部門分區
over（order by salary range between 50 preceding and 150 following）
每行對應的資料視窗是之前行幅度值不超過50，之後行幅度值不超過150
over（order by salary rows between 50 preceding and 150 following）
每行對應的資料視窗是之前50行，之後150行
over（order by salary rows between unbounded preceding and unbounded following）
每行對應的資料視窗是從第一行到最後一行，等效：
over（order by salary range between unbounded preceding and unbounded following）

AVG 函數
功能描述：用於計算一個組和資料視窗內運算式的平均值。

--1 所有人員平均工資（每個行的資料視窗為所有行）
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER () AS c_mavg
FROM employees
order by manager_id,hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
100           Kochhar      1989-9-21    17000     6461.68224299065
100           De Haan      1993-1-13    17000     6461.68224299065
100           Raphaely     1994-12-7    11000     6461.68224299065
100           Kaufling     1995-5-1     7900      6461.68224299065
100           Hartstein    1996-2-17    13000     6461.68224299065
100           Weiss        1996-7-18    8000      6461.68224299065
100           Russell      1996-10-1    14000     6461.68224299065
100           Partners     1997-1-5     13500     6461.68224299065
100           Errazuriz    1997-3-10    12000     6461.68224299065
100           Fripp        1997-4-10    8200      6461.68224299065
100           Vollman      1997-10-10   6500      6461.68224299065
100           Cambrault    1999-10-15   11000     6461.68224299065
100           Mourgos      1999-11-16   5800      6461.68224299065
100           Zlotkey      2000-1-29    10500     6461.68224299065
101           Whalen       1987-9-17    4400      6461.68224299065
101           Mavris       1994-6-7     6500      6461.68224299065
101           Higgins      1994-6-7     12000     6461.68224299065
101           Baer         1994-6-7     10000     6461.68224299065
101           Greenberg    1994-8-17    12000     6461.68224299065
...

201           Fay          1997-8-17    6000      6461.68224299065
205           Gietz        1994-6-7     8300      6461.68224299065
              King         1987-6-17    24000     6461.68224299065

--2 相同 manager_id 人員平均工資（每個行的資料視窗為相同 manager_id 所有行）
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER (PARTITION BY manager_id) AS c_mavg
FROM employees
order by manager_id,hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
100           Kochhar      1989-9-21    17000     11100
100           De Haan      1993-1-13    17000     11100
100           Raphaely     1994-12-7    11000     11100
100           Kaufling     1995-5-1     7900      11100
100           Hartstein    1996-2-17    13000     11100
100           Weiss        1996-7-18    8000      11100
100           Russell      1996-10-1    14000     11100
100           Partners     1997-1-5     13500     11100
100           Errazuriz    1997-3-10    12000     11100
100           Fripp        1997-4-10    8200      11100
100           Vollman      1997-10-10   6500      11100
100           Cambrault    1999-10-15   11000     11100
100           Mourgos      1999-11-16   5800      11100
100           Zlotkey      2000-1-29    10500     11100
101           Whalen       1987-9-17    4400      8980
101           Mavris       1994-6-7     6500      8980
101           Higgins      1994-6-7     12000     8980
101           Baer         1994-6-7     10000     8980
101           Greenberg    1994-8-17    12000     8980
...

201           Fay          1997-8-17    6000      6000
205           Gietz        1994-6-7     8300      8300
              King         1987-6-17    24000     24000

--3 按僱傭日期排序，每行資料與前面所有行的平均工資，日期相同的行平均值也相同，此平均值是包含所有該相同日期及其前面所有行的平均值（每個行的資料視窗為小於或等於當前行 hire_date 所有行）
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date) AS c_mavg
FROM employees
order by hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
              King         1987-6-17    24000     24000
101           Whalen       1987-9-17    4400      14200
100           Kochhar      1989-9-21    17000     15133.3333333333
102           Hunold       1990-1-3     9000      13600
103           Ernst        1991-5-21    6000      12080
100           De Haan      1993-1-13    17000     12900
101           Mavris       1994-6-7     6500      11420
205           Gietz        1994-6-7     8300      11420
101           Higgins      1994-6-7     12000     11420
101           Baer         1994-6-7     10000     11420
108           Faviet       1994-8-16    9000      11200
101           Greenberg    1994-8-17    12000     11266.6666666667
100           Raphaely     1994-12-7    11000     11246.1538461538
100           Kaufling     1995-5-1     7900      11007.1428571429
114           Khoo         1995-5-18    3100      10480
123           Ladwig       1995-7-14    3600      10050
124           Rajs         1995-10-17   3500      9664.70588235294
121           Sarchand     1996-1-27    4200      9361.11111111111
146           King         1996-1-30   10000      9394.73684210526
123           Bell         1996-2-4     4000      9125
...

147           Banda        2000-4-21    6200      6461.68224299065
148           Kumar        2000-4-21    6100      6461.68224299065

--4 按僱傭日期排序，每行資料與前面所有具有相同 manager_id 行的平均工資，日期相同的行平均值也相同，此平均值是包含所有該相同日期及其前面所有具有相同 manager_id 行的平均值（每個行的資料視窗為與當前行具有相同 manager_id 且小於或等於當前行 hire_date 所有行）
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) AS c_mavg
FROM employees
order by manager_id,hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
100           Kochhar      1989-9-21    17000     17000
100           De Haan      1993-1-13    17000     17000
100           Raphaely     1994-12-7    11000     15000
100           Kaufling     1995-5-1     7900      13225
100           Hartstein    1996-2-17    13000     13180
100           Weiss        1996-7-18    8000      12316.6666666667
100           Russell      1996-10-1    14000     12557.1428571429
100           Partners     1997-1-5     13500     12675
100           Errazuriz    1997-3-10    12000     12600
100           Fripp        1997-4-10    8200      12160
100           Vollman      1997-10-10   6500      11645.4545454545
100           Cambrault    1999-10-15   11000     11591.6666666667
100           Mourgos      1999-11-16   5800      11146.1538461538
100           Zlotkey      2000-1-29    10500     11100
101           Whalen       1987-9-17    4400      4400
101           Mavris       1994-6-7     6500      8225
101           Higgins      1994-6-7     12000     8225
101           Baer         1994-6-7     10000     8225
101           Greenberg    1994-8-17    12000     8980
102           Hunold       1990-1-3     9000      9000
...

201           Fay          1997-8-17    6000      6000
205           Gietz        1994-6-7     8300      8300
              King         1987-6-17    24000     24000

--5 按僱傭日期排序，每行資料與前面一行和後面一行的平均工資（每個行的資料視窗為以 hire_date 排序，當前行的前一行和後一行）
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
order by hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
              King         1987-6-17    24000     14200
101           Whalen       1987-9-17    4400      15133.3333333333
100           Kochhar      1989-9-21    17000     10133.3333333333
102           Hunold       1990-1-3     9000      10666.6666666667
103           Ernst        1991-5-21    6000      10666.6666666667
100           De Haan      1993-1-13    17000     9833.33333333333
101           Mavris       1994-6-7     6500      10600
205           Gietz        1994-6-7     8300      8933.33333333333
101           Higgins      1994-6-7     12000     10100
101           Baer         1994-6-7     10000     10333.3333333333
108           Faviet       1994-8-16    9000      10333.3333333333
101           Greenberg    1994-8-17    12000     10666.6666666667
100           Raphaely     1994-12-7    11000     10300
100           Kaufling     1995-5-1     7900      7333.33333333333
114           Khoo         1995-5-18    3100      4866.66666666667
123           Ladwig       1995-7-14    3600      3400
124           Rajs         1995-10-17   3500      3766.66666666667
121           Sarchand     1996-1-27    4200      5900
146           King         1996-1-30   10000      6066.66666666667
123           Bell         1996-2-4     4000      9000
...

147           Banda        2000-4-21    6200      6233.33333333333
148           Kumar        2000-4-21    6100      6150

--6 按僱傭日期排序，每行資料與相同 manager_id 的前面一行和後面一行的平均工資（每個行的資料視窗為以與當前行具有相同 manager_id 的資料的 hire_date 排序，當前行的前一行和後一行）
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
order by manager_id,hire_date;

MANAGER_ID    LAST_NAME    HIRE_DATE    SALARY    C_MAVG
100           Kochhar      1989-9-21    17000     17000
100           De Haan      1993-1-13    17000     15000
100           Raphaely     1994-12-7    11000     11966.6666666667
100           Kaufling     1995-5-1     7900      10633.3333333333
100           Hartstein    1996-2-17    13000     9633.33333333333
100           Weiss        1996-7-18    8000      11666.6666666667
100           Russell      1996-10-1    14000     11833.3333333333
100           Partners     1997-1-5     13500     13166.6666666667
100           Errazuriz    1997-3-10    12000     11233.3333333333
100           Fripp        1997-4-10    8200      8900
100           Vollman      1997-10-10   6500      8566.66666666667
100           Cambrault    1999-10-15   11000     7766.66666666667
100           Mourgos      1999-11-16   5800      9100
100           Zlotkey      2000-1-29    10500     8150
101           Whalen       1987-9-17    4400      5450
101           Mavris       1994-6-7     6500      7633.33333333333
101           Higgins      1994-6-7     12000     9500
101           Baer         1994-6-7     10000     11333.3333333333
101           Greenberg    1994-8-17    12000     11000
102           Hunold       1990-1-3     9000      9000
...

201           Fay          1997-8-17    6000      6000
205           Gietz        1994-6-7     8300      8300
              King         1987-6-17    24000     24000