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
No comments:
Post a Comment