On Wed, 2006-06-14 at 18:34 -0400, Chris Browne wrote: > 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... Yes, SQL Window Functions should be explicitly part of the TODO. They are already described in detail as part of SQL:2003. Window functions allow you to work with ordered result sets, moving averages etc. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
