| 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:
> 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!
The reason that I included the other rows was to place some data in another quarter and in another month to make certain that the calculations were working correctly.
Analytic solution:
SELECT
FW.FISCAL_YEAR_WEEK, FW.FISCAL_YEAR, FW.FISCAL_MONTH,
200401 2004 1 1 700 700 700 700 200402 2004 1 2 800 1500 1500 1500 200403 2004 1 3 900 2400 2400 2400 200405 2004 2 5 1230 1230 3630 3630 200426 2004 7 26 333 333 333 3963 200501 2005 1 1 400 400 400 400 200502 2005 1 2 500 900 900 900 200503 2005 1 3 600 1500 1500 1500 200505 2005 2 5 1500 1500 3000 3000 200526 2005 7 26 444 444 444 3444 200601 2006 1 1 100 100 100 100 200602 2006 1 2 200 300 300 300 200603 2006 1 3 300 600 600 600 200605 2006 2 5 870 870 1470 1470 200626 2006 7 26 999 999 999 2469
Now to feed the above into the MAX DECODE syntax to combine the years: 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,
This yields the same output as my non-analytic approach, and might be a bit more efficient.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Nov 09 2006 - 16:07:42 CST
 |
 |