Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!feeder.news-service.com!138.199.65.86.MISMATCH!sn-xt-ams-06!sn-xt-ams-03!sn-ams!sn-feed-ams-01!sn-post-ams-01!sn-post-sjc-01!supernews.com!corp.supernews.com!news.victoria.tc.ca!vtn1!yf110 From: yf110@vtn1.victoria.tc.ca (Malcolm Dew-Jones) Newsgroups: comp.databases.oracle.misc Subject: Re: Analytic function insight wanted Date: 3 Mar 2007 20:45:48 -0700 Organization: Victoria Telecommunity Network Message-ID: <45ea4efc$1@news.victoria.tc.ca> References: <45e8f498$1@news.victoria.tc.ca> <1172937842.763161.45390@30g2000cwc.googlegroups.com> X-Newsreader: TIN [version 1.2 PL2] X-Original-NNTP-Posting-Host: 199.60.222.3 X-Original-Trace: 3 Mar 2007 20:45:48 -0700, 199.60.222.3 XPident: yf110 X-Complaints-To: abuse@supernews.com Lines: 53 Xref: news.f.de.plusline.net comp.databases.oracle.misc:78445 Maxim Demenko (mdemenko@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