What I have learnt today: June 2012

Analytical Function Example -II

RANGE Windows

The general syntax of the <window_clause> is

[ROW or RANGE] BETWEEN <start_expr> AND <end_expr>

<start_expr> can be any one of the following

    UNBOUNDED PECEDING
    CURRENT ROW
    <sql_expr> PRECEDING or FOLLOWING.

    <end_expr> can be any one of the following
    UNBOUNDED FOLLOWING or
    CURRENT ROW or
    <sql_expr> PRECEDING or FOLLOWING.

For RANGE type windows the definition is in terms of values before or after the current ORDER. We will take this up in details latter.
The ROW or RANGE window cannot appear together in one OVER clause.

RANGE Windows

For RANGE windows the general syntax is same as that of ROW:

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

SQL> select * from t100 order by deptid,sal;

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

9 rows selected.

SQL> select empid, deptid,
2     sal,
3     count(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) cnt1,
4     sum(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) val1,
5     count(sal) over ( partition by deptid order by sal range between 1 preceding and (sal/2) following ) cnt2,
6     sum(sal) over ( partition by deptid order by sal range between 1 preceding and (sal/2) following ) val2
7   from
8   t100
9 order by deptid
10 /

     EMPID     DEPTID        SAL       CNT1       VAL1       CNT2       VAL2
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         10       1000          0                     2       2000
         3         10       1000          0                     2       2000
         2         10       3000          2       2000          1       3000
         8         20       3000          0                     2       7000
         9         20       4000          0                     3      15000
         3         20       5000          0                     3      18000
         9         20       6000          1       3000          2      13000
         4         20       7000          1       3000          1       7000
         5         30       1000          0                     1       1000

9 rows selected.

select l,
    sum(l) over ( order by l range between 1 preceding and (l/2) preceding) val1,
    count(l) over ( order by l range between 1 preceding and (l/2) preceding) cnt1,
    sum(l) over ( order by l range between 1 preceding and (l/2) following) val2,
    count(l) over ( order by l range between 1 preceding and (l/2) following) cnt2,
    sum(l) over ( order by l range between unbounded preceding and (l/2) preceding) val3,
    count(l) over ( order by l range between unbounded preceding and (l/2) preceding) cnt3,
    sum(l) over ( order by l range between 1 preceding and (l/3) following) val4,
    count(l) over ( order by l range between 1 preceding and (l/2) following) cnt4,
    sum(l) over ( order by l range between 1 following and (l/3) following) val5,
    count(l) over ( order by l range between 1 following and (l/2) following) cnt5
from
g10
/
Output:

         L       VAL1       CNT1       VAL2       CNT2       VAL3       CNT3       VAL4       CNT4       VAL5       CNT5
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1                     0          1          1                     0          1          1                     0
         2          1          1          6          3          1          1          3          3                     1
         3                     0          9          3          1          1          9          3          4          1
         4                     0         18          4          3          2         12          4          5          2
         5                     0         22          4          3          2         15          4          6          2
         6                     0         35          5          6          3         26          5         15          3
         7                     0         40          5          6          3         30          5         17          3
         8                     0         34          4         10          4         34          4         19          2
         9                     0         27          3         10          4         27          3         10          1
        10                     0         19          2         15          5         19          2                     0

Regards,
BKD

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

What I have learnt today

Sunday 17 June 2012

Saturday 16 June 2012