SQL & PL/SQL

Hello,

I'm trying to find an analytic function to get a value on another row by looking on a date with Oracle 11gR2.

I have a table with a date_id (truncated date), a flag and a measure. For each date, I have at least one row (sometimes 2), so it is gapless.

I would like to find analytic functions to show for each date :

• sum of the measure for that date
• sum of the measure one week ago
• sum of the measure one year ago

As it is gapless I managed to do it the week doing a group by date in a subquery and using a LAG with offset set to 7 on top of it (see below).

However I'm struggling on how to do that for the data one year ago as we might have leap years. I cannot simply set the offset to 365.

Is it possible to do it with a RANGE BETWEEN window clause? I can't manage to have it working with dates.

Week :LAG with offset 7

SQL Fiddle

or

create table daily_counts
(
  date_id date,
  internal_flag number,
  measure1 number
);


insert into daily_counts values ('01-Jan-2013', 0, 8014);
insert into daily_counts values ('01-Jan-2013', 1, 2);
insert into daily_counts values ('02-Jan-2013', 0, 1300);
insert into daily_counts values ('02-Jan-2013', 1, 37);
insert into daily_counts values ('03-Jan-2013', 0, 19);
insert into daily_counts values ('03-Jan-2013', 1, 14);
insert into daily_counts values ('04-Jan-2013', 0, 3);
insert into daily_counts values ('05-Jan-2013', 0, 0);
insert into daily_counts values ('05-Jan-2013', 1, 1);
insert into daily_counts values ('06-Jan-2013', 0, 0);
insert into daily_counts values ('07-Jan-2013', 1, 3);
insert into daily_counts values ('08-Jan-2013', 0, 33);
insert into daily_counts values ('08-Jan-2013', 1, 9);


commit;

select
    date_id,
    total1,
    LAG(total1, 7) OVER(ORDER BY date_id) total_one_week_ago
  from
    (
      select
        date_id,
        SUM(measure1) total1
      from daily_counts
      group by date_id
  )
order by 1;

Year : no idea?

I can't give a gapless example, would be too long but if there is a solution with the date directly :

SQL Fiddle

or add this to the schema above :

insert into daily_counts values ('07-Jan-2012', 0, 11);

insert into daily_counts values ('07-Jan-2012', 1, 1);

insert into daily_counts values ('08-Jan-2012', 1, 4);

Thank you for your help.

Floyd