Average(AVG) analytic function in Oracle SQL
AVG – Average function is used to find the avarage value of data. You can use different oracle clause with it to find specific things.
I have using the sample HR schema to show you the example of AVG function.
Example:
1. Find the average salary in your company or from employees tables.
SQL> select avg(salary) from employees;
AVG(SALARY) ----------- 6461.83178
2. Check the average salary by department wise we have department id column in employee table.
SQL> select department_id,avg(salary) from employees group by department_id order by 1;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
10 4400
20 9500
30 4150
3. Check average salary of employees in department with analytic style.
SET PAGESIZE 50
BREAK ON department_id SKIP 1 DUPLICATES
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_sal
FROM employees;
EMPLOYEE_ID DEPARTMENT_ID SALARY AVG_DEPT_SAL
----------- ------------- ---------- ------------
200 10 4400 4400
201 20 13000 9500
202 20 6000 9500
114 30 11000 4150
115 30 3100 4150
116 30 2900 4150
117 30 2800 4150
118 30 2600 4150
119 30 2500 4150
4. Make Average salary rowwise with department id then you can use Order by clause. see output carefully.
order_by_clause is used to order rows, or siblings, within a partition.
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS avg_dept_sal_sofar
FROM employees;
EMPLOYEE_ID DEPARTMENT_ID SALARY AVG_DEPT_SAL_SOFAR
----------- ------------- ---------- ------------------
200 10 4400 4400
202 20 6000 6000
201 20 13000 9500
119 30 2500 2500
118 30 2600 2550
117 30 2800 2633.33333
116 30 2900 2700
115 30 3100 2780
114 30 11000 4150
5. If you have same value of salary in column then range will give you the last average value, but row_avg will give you row wise average.
Windowing_clause gives some analytic functions a further degree of control over this window within the current partition,
or whole result set if no partitioning clause is used.
RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point
Parameter value defines:
UNBOUNDED PRECEDING : The window starts at the first row of the partition.
UNBOUNDED FOLLOWING : The window ends at the last row of the partition.
CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.
value_expr PRECEDING : A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used with RANGE, it can also be an interval literal if the order_by_clause uses a DATE column.
value_expr FOLLOWING : As above, but an offset after the current row.
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_avg,
AVG(salary) OVER (PARTITION BY department_id ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_avg
FROM employees;
EMPLOYEE_ID DEPARTMENT_ID SALARY RANGE_AVG ROWS_AVG
----------- ------------- ---------- ---------- ----------
200 10 4400 4400 4400
202 20 6000 6000 6000
201 20 13000 9500 9500
132 50 2100 2100 2100
136 50 22002166.666 2150
128 50 22002166.666 2166.66
135 50 2400 2260 2225
127 50 2400 2260 2260
140 50 2500 2380 2300
144 50 2500 2380 2328.57143
131 50 2500 2380 2350
191 50 2500 2380 2366.66667
182 50 2500 2380 2380