Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Analytic function insight wanted

Re: Analytic function insight wanted

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 3 Mar 2007 14:31:32 -0800
Message-ID: <1172961092.588293.249970@v33g2000cwv.googlegroups.com>


On Mar 3, 3:05 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Charles Hooper schrieb:
> > Did I mention that I don't like this problem? There might be other
> > ways to solve the problem.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> I don't think, this is an "uncomfotable" problem. It seems to me rather
> to be typical problem regarding data densification ( there are really
> good examples in data warehousing guide -http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/ana...).
> My query may be influenced by "magic of input data" - in cases, if all
> categories are not available for certain year and still should be
> calculated for the same year, one need a join with row generator to
> create missing years, as you have shown ( in my query i silly ignore
> such possibility, as i'm not sure, such requirement exists, on top - i
> arrogantly stole the T1 table which you created ;-).
>
> SQL> with t2 as(
> 2 select distinct
> 3 year,
> 4 last_value(decode(category,'A1',value1,null) ignore nulls)
> over(order by year) a1,
> 5 last_value(decode(category,'A2',value1,null) ignore nulls)
> over(order by year) a2,
> 6 last_value(decode(category,'B1',value1,null) ignore nulls)
> over(order by year) b1,
> 7 last_value(decode(category,'B2',value1,null) ignore nulls)
> over(order by year) b2,
> 8 last_value(decode(category,'C',value1,null) ignore nulls)
> over(order by year) c
> 9 from t1)
> 10 select
> 11 year,
> 12 a1+a2,
> 13 b1+b2,
> 14 c
> 15 from t2
> 16 order by year;
>
> YEAR A1+A2 B1+B2 C
> ---------- ---------- ---------- ----------
> 2000 11 22 50
> 2001 13 24 50
> 2002 63 28 7
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1616948475
>
> ---------------------------------------------------------------------------­---
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
> ---------------------------------------------------------------------------­---
> | 0 | SELECT STATEMENT | | 10 | 780 | 5 (40)|
> 00:00:01 |
> | 1 | SORT ORDER BY | | 10 | 780 | 5 (40)|
> 00:00:01 |
> | 2 | VIEW | | 10 | 780 | 5 (40)|
> 00:00:01 |
> | 3 | HASH UNIQUE | | 10 | 290 | 5 (40)|
> 00:00:01 |
> | 4 | WINDOW SORT | | 10 | 290 | 5 (40)|
> 00:00:01 |
> | 5 | TABLE ACCESS FULL| T1 | 10 | 290 | 3 (0)|
> 00:00:01 |
> ---------------------------------------------------------------------------­---
>
> Best regards
>
> Maxim

I don't know what to say... I have never had much luck making LAST_VALUE perform as I expected, even though I did try a simple solution using that function prior to my post (as well as solutions with FIRST_VALUE, ROW_NUMBER, and a couple other analytic functions).

Nice solution - I still do not like the problem (even though I can see situations where it would be very useful).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Mar 03 2007 - 16:31:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US