Analytic Function – Over Partition

Analytic Functions compute an aggregate value based on a group of rows. How are they differ from aggregate functions that you are used to using it like AGV, SUM, MAX, MIN? They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window which is defined by the analytic clause. The easiest way to learn and understand is by Examples.

Syntax: analytic_function ([ arguments ]) OVER (analytic_clause)

We are going to use our standard employee table from HR schema.  First we will create employee table, and load data into it.

Employee Table Structure:

Employee Table Data:

To understand Analytic Functions, we will first introduce the aggregate function.  We will get the AVG salary from employee table.

SELECT TRUNC(AVG(sal)) FROM employee_temp;

 TRUNC(AVG(SAL))

-----------------------

 2073

We got one row of records as we want the average salary from employee table. Next we want the average salary from each department.

SELECT dept, 
       Trunc(Avg(sal)) 
FROM   prabin_employee_temp 
GROUP  BY dept 
ORDER  BY dept 

    DEPT                       TRUNC(AVG(SAL))
---------- ---------------------------------------
        10                                 2916
        20                                  2175
        30                                  1566

Here, we are displayed one record for each dept. The result displayed the average salary for each dept.  In both result, the query reduces the number of rows returned. Next, we want to display all the records with their dept average salary.

SELECT empno, 
       dept, 
       sal, 
       Avg(sal) OVER (partition BY dept) avg_by_dept 
FROM   employee_temp);

Analytic function is similar to that of group by queries with the exception of not reducing the number of rows returned by the query. The above analytic functions displayed all the rows with their dept AVG salary.

The query partition clause divides the result set into partitions, or groups of data. This is similar to that of GROUP BY clause. If you miss or omit this clause, the whole result set is treated as a single partition.

SELECT empno, 
       dept, 
       sal, 
       Avg(sal) OVER () avg_by_dept 
FROM   employee_temp);

Remember, Over partition is much faster and readable sql query over sub-query and you don't have to regret on using it.