| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Efficient query for this year/last year/two years ago
Lerch wrote:
> 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!
The create and insert statements help, a couple more to provide
interesting data.
insert into fiscal_weeks values (200605, 2006, 2, 200505);
insert into sales_by_week values (200605, 870);
insert into fiscal_weeks values (200505, 2005, 2, 200405);
insert into sales_by_week values (200505, 1500);
insert into fiscal_weeks values (200405, 2004, 2, 200305);
insert into sales_by_week values (200405, 1230);
insert into fiscal_weeks values (200626, 2006, 7, 200526);
insert into sales_by_week values (200626, 999);
insert into fiscal_weeks values (200526, 2005, 7, 200426);
insert into sales_by_week values (200526, 444);
insert into fiscal_weeks values (200426, 2004, 7, 200326);
insert into sales_by_week values (200426, 333);
The example below does not use analytical functions. SELECT
MAX(FISCAL_YEAR_WEEK) FISCAL_YEAR_WEEK, MAX(FISCAL_YEAR) FISCAL_YEAR, MAX(FISCAL_MONTH) FISCAL_MONTH,
FW.FISCAL_YEAR_WEEK, FW.FISCAL_YEAR, FW.FISCAL_MONTH, FW.FISCAL_WEEK_NUM, FW.SALES,
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
FW.FISCAL_YEAR_WEEK, FW.FISCAL_YEAR, FW.FISCAL_MONTH, FW.FISCAL_WEEK_NUM, FW.SALES)
Output:
FISCAL_YEAR_WEEK FISCAL_YEAR FISCAL_MONTH FISCAL_WEEK_NUM
200602
2006
1
2
200
500
800
300
900
1500
300
900
1500
300
900
1500
200603
2006
1
3
300
600
900
600
1500
2400
600
1500
2400
600
1500
2400
200605
2006
2
5
870
1500
1230
1470
3000
3630
870
1500
1230
1470
3000
3630
200626
2006
7
26
999
444
333
2469
3444
3963
999
444
333
999
444
333
How does it work?
There are a series of nested inline views, with the top inline view in
the nest retrieving the one week's worth of sales, and the second of
the nested inline views providing the year to date, quarter to date,
and month to date figures. Outside of the inline views, the MAX and
DECODE functions are used to place the related information from the
different years onto the same row, ADD_MONTHS was used, rather than
hard coding the year into the SQL statement.
The starting point of the whole SQL statement is this simple statement: SELECT
FW.FISCAL_YEAR_WEEK, FW.FISCAL_YEAR, FW.FISCAL_MONTH,
FISCAL_YEAR, FISCAL_MONTH, FISCAL_YEAR_WEEK;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Nov 09 2006 - 12:32:58 CST
 |
 |