Efficient query for this year/last year/two years ago

From: Lerch <mike.lerch_at_gmail.com>
Date: 9 Nov 2006 06:32:07 -0800
Message-ID: <1163082727.606469.108450@b28g2000cwb.googlegroups.com>

Greetings, everyone. I'm hoping someone has a moment to assist me in finding an efficient solution to deliver a table that contains ytd, qtd, mtd and wtd sales for this year, the corresponding period the year before, and the corresponding period the year before that, at the weekly grain, using a weekly sales table and dates table as sources.

Assume this table for our dates

create table fiscal_weeks (

     fiscal_year_week number PRIMARY KEY,
     fiscal_year number,
     fiscal_month number,
     fiscal_year_week_LY number

);

Assume this sales table:

create table sales_by_week (

     fiscal_year_week number primary key,
     sales number

);

Here's some sample data:

insert into fiscal_weeks values (200601, 2006, 1, 200501);
insert into fiscal_weeks values (200602, 2006, 1, 200502);
insert into fiscal_weeks values (200603, 2006, 1, 200503);
insert into fiscal_weeks values (200501, 2005, 1, 200401);
insert into fiscal_weeks values (200502, 2005, 1, 200402);
insert into fiscal_weeks values (200503, 2005, 1, 200403);
insert into fiscal_weeks values (200401, 2004, 1, 200301);
insert into fiscal_weeks values (200402, 2004, 1, 200302);
insert into fiscal_weeks values (200403, 2004, 1, 200303);
insert into sales_by_week values (200601, 100);
insert into sales_by_week values (200602, 200);
insert into sales_by_week values (200603, 300);
insert into sales_by_week values (200501, 400);
insert into sales_by_week values (200502, 500);
insert into sales_by_week values (200503, 600);
insert into sales_by_week values (200401, 700);

insert into sales_by_week values (200402, 800); insert into sales_by_week values (200403, 900); commit;

When I first took a crack at this I had loads of nested subqueries, self-joins, etc. to accomplish the cumulative sums. Then I discovered the analytical windowing queries and found that the cumulative sums by year and quarter and such could be delivered in the same row very easily:

create table sales_by_week_cume as
select t2.fiscal_year_week,

     sales as WTD,
     sum(sales) over(
          partition by fiscal_month
          order by t2.fiscal_year_week
          range between fiscal_month preceding and current row


     ) as Sales_MTD

from sales_by_week t1
inner join fiscal_weeks t2

     on t1.fiscal_year_week = t2.fiscal_year_week order by fiscal_year_week;

The windowing functions are repeated for each of the cume periods I need (QTD and YTD). But limiting just to MTD for the sake of this example, this essentially gives me a set that is my weekly sales table with cumulative, window-based sums, but I still need to get this year, last year and two years ago in the same row. To do that I did the following, which works, but is very costly and slow:

select ty.fiscal_year_week, ty.ty_sales_mtd, ly.ly_sales_mtd, lly.lly_sales_mtd
from (

          select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd as ty_sales_mtd

          from sales_by_week_cume t1
          inner join fiscal_weeks t2
               on t1.fiscal_year_week = t2.fiscal_year_week
          where t2.fiscal_year = 2006


     ) TY

inner join (
          select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd
as ly_sales_mtd
          from sales_by_week_cume t1
          inner join fiscal_weeks t2
               on t1.fiscal_year_week = t2.fiscal_year_week
          where t2.fiscal_year = 2005


     ) LY

     on ty.fiscal_year_week_ly = ly.fiscal_year_week
inner join (
          select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd
as lly_sales_mtd
          from sales_by_week_cume t1
          inner join fiscal_weeks t2
               on t1.fiscal_year_week = t2.fiscal_year_week
          where t2.fiscal_year = 2004


     ) LLY

     on ly.fiscal_year_week_ly = lly.fiscal_year_week

Again, this delivers the results I'm looking for, but I'm really wondering if you would tackle this problem differently? I'm wondering if there's some nifty, standard way to do this that I don't know about (like what happened when I discovered the ANSI-standard analytic functions. Talk about 'I coulda had a V-8!'). The cost for this tiny bit of sample data is very small, but my real data is of course gigantic and this takes a very long time.

And of course the devil is in the details: I trimmed this sample down for posting, but my REAL data set involves sales at a location and item level, and I need to get the set of all location/item combinations from the whole three year period and outer join it to each year (so we can do item-to-item comparisons across years, even if the item hasn't sold in one particular period). That's its own bear but I wanted to see if you agreed with the fundamentals I've presented here.

Thanks very much for your consideration! Received on Thu Nov 09 2006 - 08:32:07 CST