Average(AVG) analytic function in Oracle SQL

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.