kleptog@xxxxxxxxx (Martijn van Oosterhout) writes: > On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote: >> > [3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN >> >> The sample problem in [3] is one that shows pretty nicely a >> significant SQL weakness; it's very painful to build SQL to do complex >> things surrounding cumulative statistics. > > I havn't managed to wrap my brain around them yet, but this seems like > something that SQL WINDOW functions would be able to do. For each row > define the window frame to be all the preceding rows, do a SUM() and > divide that over the total. Or perhaps the PERCENT_RANK() function does > this already, not sure. > > Mind you, postgres doesn't support them yet, but it's interesting that > it may be possible at all... Yes, you are exactly right; I have seen a couple references to OVER and PARTITION BY which look as though they are the relevant SQL additions... http://blogs.ittoolbox.com/database/technology/archives/olap-sql-part-5-windowing-aggregates-8373 http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk http://www.experts-exchange.com/Databases/Oracle/Q_21793507.html I'm not sure the degree to which these are standardized, but they are available in some form or another in late-breaking versions of Oracle, DB2, and Microsoft SQL Server. I'm not quite sure how to frame this so as to produce something that should go on the TODO list, but it looks like there's a possible TODO here... -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://cbbrowne.com/info/sap.html "The newsreader abuse likely stems from more fundamental, than merely just the UI, design disagreements. Requests from Unix programmers to replicate Free Agent rightfully so should trigger the throwing of sharp heavy objects at the requesting party." -- jedi@xxxxxxxxxxxxxxxx (jedi)
