tag:blogger.com,1999:blog-29286778.post2888363683260013814..comments2023-07-02T05:53:35.132-04:00Comments on SQL Garbage Collector: A Scenario to Ponder #9Unknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-29286778.post-35497591653667787892009-07-27T18:37:32.110-04:002009-07-27T18:37:32.110-04:00I came up with this solution, rather similar to Ro...I came up with this solution, rather similar to Rob's:<br /><br />select runl.id<br /> , runl.curval<br /> , sum(power(2,runr.id-1)*runr.curval)/power(2,runl.id) average<br /><br />from #runningavg runl<br />join #runningavg runr on runl.id >= runr.id<br />group by runl.id, runl.curval<br />order by runl.idUnknownhttps://www.blogger.com/profile/05548594037353915088noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-4139888353945365812006-11-17T02:34:00.000-05:002006-11-17T02:34:00.000-05:00A traditional cursor would be your best option. It...A traditional cursor would be your best option. It's not limited to the recursion level.<br /><br />And yes, whenever you're doing running totals, a cursor becomes the best option for larger datasets.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-85123840351790448972006-11-17T00:48:00.000-05:002006-11-17T00:48:00.000-05:00Rob, beautiful dude. Just answer I had.. Here is m...Rob, beautiful dude. Just answer I had.. Here is my solution..<br /><br />select a.id,a.curval,sum(b.curval*power(cast(2 as decimal(16,10)),b.id-a.id-1))<br />from #runningavg a, #runningavg b<br />where a.id >=b.id<br />group by a.id,a.curval<br />order by a.id<br /><br />But, I think it can be improved. I guess we will be better off using a temporary table than this query. <br /><br />I tried doing a load test on this..<br />added a few more rows to the table with this batch..<br /><br />declare @a int<br />set @a = 1<br />while (@a < 15)<br />begin<br />insert into #runningavg (curval) select curval from #runningavg<br />set @a = @a + 1<br />end<br /><br />Now I tried to run the CTE solution I gave and the other query with the join, and cte was performing much better. Though I would attribute it to the calculations in the latter, it calls for some serious thinking on when to go for cursors. <br />Your opinions please!Omnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-57365005744372590252006-11-17T00:41:00.000-05:002006-11-17T00:41:00.000-05:00Very true, its not a running average. I was just c...Very true, its not a running average. I was just coming up with a question. Not really a real time scenario.. <br /><br />Basically, all these questions are for the drive against using cursors :)Omnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-11054040904499540642006-11-16T21:01:00.000-05:002006-11-16T21:01:00.000-05:00Hi
Are you sure you want an average calculated th...Hi<br /><br />Are you sure you want an average calculated that way? It's not really the running average...Mitch Wheathttps://www.blogger.com/profile/04779485555967868532noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-64669453709972163682006-11-16T20:53:00.000-05:002006-11-16T20:53:00.000-05:00Here you go:
select r.id, sum(r.vals)
from
(sele...Here you go:<br /><br />select r.id, sum(r.vals)<br />from <br />(select r1.id, r2.curval / power(2.,r1.id - r2.id+1) as vals<br />from #runningavg r1<br /> join<br /> #runningavg r2<br /> on r2.id <= r1.id<br />) r<br />group by r.id<br /><br />Works in either system. It works by having a think about what you're actually asking for.Anonymousnoreply@blogger.com