Friday, January 23, 2009

Monday, January 5, 2009

Oracle SQL: Calculating on a Running Window

select t1me, m1n, m1n_1, m1n_2, m1n_3, m1n_4, m1n+m1n_1+m1n_2+m1n_3+m1n_4 fiveminspan from
(select
trunc(record_timestamp,'MI') t1me,
count(*),
LEAD(count(*), 0, -1) OVER (ORDER BY trunc(record_timestamp,'MI') ASC) m1n,
LEAD(count(*), 1, -1) OVER (ORDER BY trunc(record_timestamp,'MI') ASC) m1n_1,
LEAD(count(*), 2, -1) OVER (ORDER BY trunc(record_timestamp,'MI') ASC) m1n_2,
LEAD(count(*), 3, -1) OVER (ORDER BY trunc(record_timestamp,'MI') ASC) m1n_3,
LEAD(count(*), 4, -1) OVER (ORDER BY trunc(record_timestamp,'MI') ASC) m1n_4
from REP_OWNER.BAM_MEDIA_REPORT where
record_timestamp > TO_DATE('03/30/2008', 'MM/DD/YYYY') and
record_timestamp < TO_DATE('09/29/2008', 'MM/DD/YYYY')
group by trunc(record_timestamp,'MI')
order by count(*) desc)