Re: Analytic function insight wanted

Maxim Demenko (mdemenko_at_gmail.com) wrote:
: 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/analysis.htm#sthref1840).
: 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 ;-).

No, in fact it is best to leave out years with no change so the result is easier for a human to read - the categories change perhaps every five years so even for a hundred year period there are only about 25 lines, and it's easy enough for a human to find the line they need.

: 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

Ahah! Exactly the sort of simplicity I suspected but couldn't seem to get my head around for some reason.

Thanks muchly.

Malcolm Received on Sat Mar 03 2007 - 21:45:48 CST