A preliminary understanding of Oracle analysis functions

Version: 10.1 Sample Database: ORCL mode: hr

Understanding of window function:
The Window Function specifies the size of the data window used by the analysis function. The size of the data window may change with the change of rows. For example:
Order by salary is a default window function.
Over (partition by deptno) by Department partition
Over (order by salary range between 50 preceding and 150 following)
The data window corresponding to each row is that the range value of the previous row cannot exceed 50, and the range value of the subsequent row cannot exceed 150.
Over (order by salary rows between 50 preceding and 150 following)
The data window corresponding to each row is the first 50 rows, and the last 150 rows
Over (order by salary rows between unbounded preceding and unbounded following)
The data window corresponding to each row is from the first row to the last row, which is equivalent:
Over (order by salary range between unbounded preceding and unbounded following)

AVG Functions
Function Description: used to calculate the average value of expressions in a group and data window.

-- 1 average salary of all personnel (the data window of each row is all rows)
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 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 11000 6461.68224299065
100 Mourgos 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 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 average salaries of people with the same manager_id (the data window of each row is all rows with the same 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 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 11000 11100
100 mourgos 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 12000 8980
...

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

-- 3. sort by employment date. The average salary of each row of data is the same as that of all previous rows, and the average value of rows with the same date is the same, this average value is the average value of all the same date and all the rows before it (the data window of each row is smaller than or equal to all the rows of hire_date of the current row)
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 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 12000 11266.6666666667
100 Raphaely 1994-12-7 11000 11246.1538461538
100 Kaufling 1995-5-1 7900 11007.1428571429
114 Khoo 3100 10480
123 Ladwig 3600 10050
124 Rajs 1995-10-17 3500 9664.70588235294
121 Sarchand 1996-1-27 4200 9361.11111111111
146 King 10000 9394.73684210526
123 Bell 4000 9125
...

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

-- 4: sort by employment date. The average salary of each row of data with the same manager_id row is the same as that of all previous rows, this average value includes the average values of all the rows with the same manager_id for the same date and all the previous rows with the same manager_id for each row (the data window for each row is the same as the current row manager_id and less than or equal to all rows of the current row 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 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 11000 11591.6666666667
100 Mourgos 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 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. sort by employment date. The data in each row is sorted by hire_date and the average salary of the next row)
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 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 12000 10666.6666666667
100 Raphaely 1994-12-7 11000 10300
100 Kaufling 1995-5-1 7900 7333.33333333333
114 Khoo 3100 4866.66666666667
123 Ladwig 3600 3400
124 Rajs 1995-10-17 3500 3766.66666666667
121 Sarchand 1996-1-27 4200 5900
146 King 10000 6066.66666666667
123 Bell 4000 9000
...

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

-- 6 sort by employment date, the average salary of each row of data and the previous and next rows with the same manager_id (the data window of each row is sorted by hire_date with the same manager_id as the current row, the first and last rows of the current row)
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 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 11000 7766.66666666667
100 mourgos 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 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