Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!195.96.0.7.MISMATCH!newsfeed.utanet.at!newscore.univie.ac.at!newsfeed01.chello.at!newsfeed.arcor.de!newsspool2.arcor-online.net!news.arcor.de.POSTED!not-for-mail Message-ID: <4D6FE538.8050001@gmail.com> Date: Thu, 03 Mar 2011 20:00:08 +0100 From: Maxim Demenko User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.14) Gecko/20110221 Thunderbird/3.1.8 MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server To: yossarian Subject: Re: analytic question References: <4d6e681f$0$30910$5fc30a8@news.tiscali.it> In-Reply-To: <4d6e681f$0$30910$5fc30a8@news.tiscali.it> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 97 Organization: Arcor NNTP-Posting-Date: 03 Mar 2011 20:00:02 CET NNTP-Posting-Host: 6016982e.newsspool3.arcor-online.net X-Trace: DXC=SABHZQ7GQjY5TOT9_N5i I have a data table like this (Oracle 10.2.0.3.0): > Some time ago Maxim Demenko suggested a nice trick, but unfortunately it > works only at row level, and it ignores the fact that A and B can be NULL: > > select > day, value_a a,value_b b, > avg(value_a) over(order by day range between numtodsinterval(89,'day') > preceding and numtodsinterval(0,'day') following) avg_a, > avg(value_b) over(order by day range between numtodsinterval(59,'day') > preceding and numtodsinterval(0,'day') following) avg_b, > count(decode(rn_a,1,1)) over(order by day range between > numtodsinterval(89,'day') preceding and numtodsinterval(0,'day') > following) months_a, > count(decode(rn_b,1,1)) over(order by day range between > numtodsinterval(59,'day') preceding and numtodsinterval(0,'day') > following) months_b > from ( > select > day, > value_a, > value_b, > row_number() over(partition by trunc(day,'MM') order by day) rn_a, > row_number() over(partition by trunc(day,'MM') order by day) rn_b > from x > where day>=date'2007-01-01' > ) > order by day > > DAY A B AVG_A AVG_B CNT_A CNT_B > ---------- ---- ---- ---------- ---------- ---------- ---------- > 03-01-2007 293 ! 293 ! 1 1 > 24-01-2007 195 ! 244 ! 1 1 > 06-02-2007 140 ! 209.333333 ! 2 2 > 20-02-2007 208 ! 209 ! 2 2 > 02-03-2007 142 ! 195.6 ! 3 3 > 20-03-2007 179 ! 192.833333 ! 3 2 > 03-04-2007 167 ! 171.833333 ! 3 3 > 17-04-2007 145 ! 168 ! 3 2 > 07-05-2007 270 ! 185.166667 ! 3 2 > 14-05-2007 190 ! 185.857143 ! 3 2 > 22-05-2007 221 ! 187.714286 ! 3 2 > 05-06-2007 181 ! 193.285714 ! 3 2 > 18-06-2007 245 ! 202.714286 ! 3 2 > 10-07-2007 259 ! 215.857143 ! 3 2 > 19-07-2007 175 ! 220.142857 ! 3 2 > 09-08-2007 180 ! 207.285714 ! 3 2 > 20-08-2007 295 ! 222.5 ! 3 2 > 10-09-2007 198 ! 225.333333 ! 3 2 > 20-09-2007 294 ! 233.5 ! 3 2 > 04-10-2007 202 ! 229 ! 4 3 > 23-10-2007 218 ! 231.166667 ! 3 2 > 12-11-2007 183 ! 231.666667 ! 3 2 > 16-11-2007 225 14 230.714286 14 3 2 > 03-12-2007 167 22 212.428571 18 4 2 > 21-12-2007 188 51 197.166667 29 3 2 > > Any suggestion? > > Thank you. > > Kind regards, Y. Not sure, how the end result should look like, maybe this modification would work for you? select day, value_a a,value_b b, avg(value_a) over(order by day range between numtodsinterval(89,'day') preceding and numtodsinterval(0,'day') following) avg_a, avg(value_b) over(order by day range between numtodsinterval(59,'day') preceding and numtodsinterval(0,'day') following) avg_b, count(decode(rn_a,1,1)) over(order by day range between numtodsinterval(89,'day') preceding and numtodsinterval(0,'day') following) months_a, count(decode(rn_b,1,1)) over(order by day range between numtodsinterval(59,'day') preceding and numtodsinterval(0,'day') following) months_b from ( select day, value_a, value_b, decode(value_a,null,null,row_number() over(partition by trunc(day,'MM'),nvl2(value_a,1,null) order by day)) rn_a, decode(value_b,null,null,row_number() over(partition by trunc(day,'MM'),nvl2(value_b,1,null) order by day)) rn_b from x where day>=date'2007-01-01' ) order by day / Best regards Maxim