From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Chris Browne <cbbrowne(at)acm(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [HACKERS] Fabian Pascal and RDBMS deficiencies in fully |
Date: | 2006-06-16 13:55:35 |
Message-ID: | 1150466135.2691.928.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Wed, 2006-06-14 at 18:34 -0400, Chris Browne wrote:
> kleptog(at)svana(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-06-16 14:01:30 | Re: postgres and ldap |
Previous Message | Magnus Hagander | 2006-06-16 13:51:01 | Re: postgres and ldap |
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-06-16 14:17:27 | Re: Re-thing PG_MODULE_MAGIC |
Previous Message | Simon Riggs | 2006-06-16 13:51:41 | Re: Re-thing PG_MODULE_MAGIC |