Example: Analytical Function -I

SQL> ed
Wrote file afiedt.buf

  1  create table t100
  2  as(
  3  select 1 empid, 1000 sal, 10 deptid from dual
  4  union all
  5  select 2 empid, 3000 sal, 10 deptid from dual
  6  union all
  7  select 3 empid, 5000 sal, 20 deptid from dual
  8  union all
  9  select 4 empid, 7000 sal, 20 deptid from dual
 10  union all
 11  select 5 empid, 1000 sal, 30 deptid from dual
 12* )
SQL> /

Table created.

Elapsed: 00:00:00.68
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
SQL> insert into t100 values(3,1000,10);

1 row created.

Elapsed: 00:00:00.09
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
         3       1000         10

6 rows selected.

 select
   row_number() over (order by empid) sl,
   n.*  ,
   row_number() over ( partition by deptid order by  empid) rn,
   max(sal) over() maxsal,
   max(sal) over ( partition by deptid ) maxsal_dept,
   max(sal) keep(dense_rank last order by sal) over ( partition by deptid ) deptmaxsal,
   first_value(sal ignore nulls)  over ( partition by deptid order by sal desc) fvalue,
   last_value(sal ignore nulls)  over ( partition by deptid order by sal desc) lvalue,
   dense_rank()   over (  order by sal) assignedrank,
   rank() over(order by sal) rank,
   sum(sal) over (order by sal rows between current row and 1 following) sum_1,
   sum(sal) over (order by sal rows between 1 preceding and 1 following) sum_2,
   sum(sal) over (order by sal rows between 1 preceding and current row) sum_3
 from
 t100 n
/
  Useful links:
  http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
  http://psoug.org/reference/analytic_functions.html