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
Best Open Source Business Intelligence Software Helical Insight is Here
