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