Analytic function syntax :

Analytic_function(argument) OVER (PARTITION BY argument ORDER BY argument);

Where argument : column name

Analytic functions operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query.

Eg :Lets take the example of calculating Avg from emp table group by deptno:

Traditional way :

SELECT deptno, AVG(sal)

FROM   emp

GROUP BY deptno

ORDER BY deptno;

Output :

DEPTNO   AVG(SAL)

———-      ———-

10               2916.66667

20               2175

30               1566.66667

Now , by using Analytical function :

SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_salFROM   emp;

Output :

EMPNO     DEPTNO     SAL    AVG_DEPT_SAL

———-      ———-       ——–   ————– ——–

7782         10               2450            2916.66667

7839         10                 5000           2916.66667

7934         10                1300            2916.66667

7566         20                 2975           2175

7902         20                 3000           2175

7876         20                 1100           2175

7369         20                  800            2175

7788         20                  3000         2175

7521         30                  1250         1566.66667

7844         30                   1500        1566.66667

7499         30                 1600        1566.66667

7900         30                    950         1566.66667

7698         30                    2850      1566.66667

7654         30                     1250       1566.66667

This time AVG is an analytic function, operating on the group of rows defined by the contents of the OVER clause.

AVG function is still reporting the departmental average, like it did in the GROUP BY query, but the result is present in each row, rather than reducing the total number of rows returned. This is because analytic functions are performed on a result set after all join, WHERE, GROUP BY and HAVINGclauses are complete, but before the final ORDER BY operation is performed.

Thanks,

Rupam Bhardwaj

Leave a Reply