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