21 July, 2011

Oracle analytical functions - Part 1


Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions Syntax :-
analytic_function(arguments) over (query_partition_by Order_by_clause) 
 
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses.
You can specify analytic functions with this clause in the select list or ORDER BY clause.
To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested sub-query.


query_partition_by
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.

example : sum(sal) over ( partition by deptno,mgrno)

Order_by_clause
Use the order_by_clause to specify how data is ordered within a partition.
ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

example : sum(sal) over ( partition by deptno,mgrno order by empno DESC NULLS FIRST )

Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.

analytic_function
AVG
COUNT
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
RANK
ROW_NUMBER
SUM
VARIANCE 
I will clarify later using of that functions in part 2.

Hint
For IT people who use Microsoft SQL Server, It is the same syntax.
Unfortunately Mysql doesn't support partition by clause.

/********************************************************************************/*
/*                        The following is comment on challenge indicated to the previoud topic                              /*
/********************************************************************************/
I created challenge at my Facebook wall that indicated to that topic.
Challenge Question
I want select statement that show employees and his serial in his department
like the following
EmpNO ENAME DeptNO Serial
7782      "CLARK" 10          1
7839      "KING"    10          2
7369      "SMITH"  20          1
7566      "JONES"  20          2
7499      "ALLEN"  30         1
7521      "WARD"   30         2
Challenge Solution
1- First solution that uses partition by clause (Oracle RDBMS and Microsoft SQL Server)
SELECT empno, ename, deptno,
       COUNT (deptno) OVER (PARTITION BY deptno ORDER BY empno)
  FROM scott.emp

2- Second solution that uses left outer join and that solution have bad performance. ( SQL Standard )
SELECT   a.empno, a.ename, a.deptno, COUNT (*) serial
    FROM scott.emp a LEFT OUTER JOIN scott.emp b
         ON a.deptno = b.deptno AND a.empno >= b.empno
GROUP BY a.empno, a.ename, a.deptno
ORDER BY a.deptno, a.empno 

3- Third solution that can be executed at Mysql only
set @deptartment = 0;
set @serial  = 1;

select empno, ename, deptno
   @serial := if(@deptartment = deptno, @serial + 1, 1) as row_number,
   @deptartment := deptno as dummy
from scott.emp
order by deptno,empno;

I hope that post is helpful and useful

Thanks

1 comment:

  1. Very helpful post. I have worked on this issue for many days but didn't find a solution. I really appreciate your knowledge as you have suggested such a nice solution.

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...