in DB2 9.7, we have the new olap functions such LAG, LEAD, FIRST_VALUE, LAST_VALUE which help retrieving values from some column from a row with some offset. But, do you know, actually they are just special cases of the common OLAP functions? You may already used to write code such as "rownumber()over(partition by cola order by colb)" or
"sum(colc)over(partition by cola order by colb)" , so you just need one step further to know the common solutions DBMS already provided years ago, and, that is really much more powerful as it can aggregate a

**window**than just a**row**with offset.Before that, make sure you know what does the "order by" clause "sum(colc)over(partition by cola

**order by colb**)" really mean. Basically, aggregation will be done**over the window already counted till the current row**.Here's the deal. There is a (group) WINDOW specification can be used over the OLAP functions and it defines the range. It can be defined over either by ROWS or RANGE of keys. Then you know what I want to say in this blog...

window-aggregation-group-clause: |--+-ROWS--+--+-| group-start |---+-----------------------------| '-RANGE-' +-| group-between |-+ '-| group-end |-----' group-start: |--+-UNBOUNDED PRECEDING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ '-CURRENT ROW------------------' group-between: |--BETWEEN--| group-bound1 |--AND--| group-bound2 |-------------| group-bound1: |--+-UNBOUNDED PRECEDING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ +-unsigned-constant--FOLLOWING-+ '-CURRENT ROW------------------' group-bound2: |--+-UNBOUNDED FOLLOWING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ +-unsigned-constant--FOLLOWING-+ '-CURRENT ROW------------------' group-end: |--+-UNBOUNDED FOLLOWING----------+-----------------------------| '-unsigned-constant--FOLLOWING-'

eg.

with tbase(id,part,value)

as (

values (1,2,3)

union all values (2,3,4)

union all values (1,3,5)

union all values (1,3,6)

union all values (1,5,5)

union all values (1,4,7)

union all values (2,3,5)

union all values (2,3,5)

union all values (2,4,7)

union all values (1,6,5)

)

select

id, part, value,

sum(value)over(partition by id) as sum_default_no_order,

sum(value)over(partition by id order by part,value) as sum_default_has_order,

sum(value)over(partition by id order by part,value rows between 1 preceding and 1 following ) as sum_win_offset_1,

as (

values (1,2,3)

union all values (2,3,4)

union all values (1,3,5)

union all values (1,3,6)

union all values (1,5,5)

union all values (1,4,7)

union all values (2,3,5)

union all values (2,3,5)

union all values (2,4,7)

union all values (1,6,5)

)

select

id, part, value,

sum(value)over(partition by id) as sum_default_no_order,

sum(value)over(partition by id order by part,value) as sum_default_has_order,

sum(value)over(partition by id order by part,value rows between 1 preceding and 1 following ) as sum_win_offset_1,

sum(value)over(partition by id order by part,value rows between 1 preceding and 1
preceding) as sum_win_offset_2, --- looks like LEAD? so does the LAG.

sum(value)over(partition by id order by part,value rows between unbounded preceding and unbounded following ) as sum_unbounded,

sum(value)over(partition by id order by part,value range between unbounded preceding and unbounded following ) as sum_range_unbounded,

-- sum(value)over(partition by id order by part,value range between 1 preceding and 1 following ) as sum_range_limited, -- error: only one sort-key supported

sum(value)over(partition by id order by part range between 1 preceding and 1 following ) as sum_range_limited,

sum(value)over(partition by id order by part,value rows between unbounded preceding and current row) as sum_win_tillnow,

sum(value)over(partition by id order by part,value rows between unbounded preceding and 1 preceding) as sum_win_allbefore

from tbase;

sum(value)over(partition by id order by part,value range between unbounded preceding and unbounded following ) as sum_range_unbounded,

-- sum(value)over(partition by id order by part,value range between 1 preceding and 1 following ) as sum_range_limited, -- error: only one sort-key supported

sum(value)over(partition by id order by part range between 1 preceding and 1 following ) as sum_range_limited,

sum(value)over(partition by id order by part,value rows between unbounded preceding and current row) as sum_win_tillnow,

sum(value)over(partition by id order by part,value rows between unbounded preceding and 1 preceding) as sum_win_allbefore

from tbase;

pls run the example and taste how powerful it could be.