Re: Fabian Pascal and RDBMS deficiencies in fully implementing

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...
--
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(at)dementia(dot)mishnet (jedi)