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

From: Lerch <mike.lerch_at_gmail.com>
Date: 9 Nov 2006 13:25:49 -0800
Message-ID: <1163107549.655646.187490@b28g2000cwb.googlegroups.com>

Ah, I figured it out. I had the parameters for the analytical function right, I just gave it bad data to partition by when I worked up my sample: because all the rows shared the same month it delivered weirdo results. Thanks everyone for help so far, and this is a more appropriate (and to Charles' point, more interesting) set of data. Note that I'm hard-coding the date just for simplicity of the example here.

--Assume this table for our dates

create table fiscal_weeks (

     fiscal_year_week number PRIMARY KEY,
     fiscal_year number,
     fiscal_year_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, 200601, 200501);
insert into fiscal_weeks values (200602, 2006, 200601, 200502);
insert into fiscal_weeks values (200603, 2006, 200601, 200503);
insert into fiscal_weeks values (200604, 2006, 200602, 200504);
insert into fiscal_weeks values (200501, 2005, 200501, 200401);
insert into fiscal_weeks values (200502, 2005, 200501, 200402);
insert into fiscal_weeks values (200503, 2005, 200501, 200403);
insert into fiscal_weeks values (200504, 2005, 200502, 200404);
insert into fiscal_weeks values (200401, 2004, 200401, 200301);
insert into fiscal_weeks values (200402, 2004, 200401, 200302);
insert into fiscal_weeks values (200403, 2004, 200401, 200303);
insert into fiscal_weeks values (200404, 2004, 200402, 200304);
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);
insert into sales_by_week values (200604, 150);

insert into sales_by_week values (200504, 250); insert into sales_by_week values (200404, 350); 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_year_month
          order by t2.fiscal_year_week
          range between fiscal_year_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;

Charles demonstrated a way to deliver this with non-analytic functions.  Are there other fundamental ways to tackle this? Thanks much! Received on Thu Nov 09 2006 - 15:25:49 CST