On Wed, 2006-06-14 at 18:34 -0400, Chris Browne wrote:
> kleptog@svana.org (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