Re: running aggregates entirely in SQL?

I could have sworn that I saw an email from Josh Berkus on sfpug about how to
do running aggregates (ie. a running tally or total, say in an invoice)
entirely in PostgreSQL, however googling only finds:

http://archives.postgresql.org/pgsql-novice/2003-09/msg00305.php

and I can't find it in the sfpug archives:

http://archives.postgresql.org/sfpug/

am I mistaken?

If so, is there a way to keep a running tally entirely in PostgreSQL?

Thanks,
Adi

I have information on running aggregates.
Let me dig up my references. The examples
for plpython are on my website at
www.varlena.com/GeneralBits/Tidbits
under talks for last year's OSCON.

But I think I have another article or two on them.

--elein
============================================================
elein(at)varlena(dot)com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.

On Tue, May 11, 2004 at 01:10:58PM -0400, Aditya wrote:
> I could have sworn that I saw an email from Josh Berkus on sfpug about how to
> do running aggregates (ie. a running tally or total, say in an invoice)
> entirely in PostgreSQL, however googling only finds:
>
> http://archives.postgresql.org/pgsql-novice/2003-09/msg00305.php
>
> and I can't find it in the sfpug archives:
>
> http://archives.postgresql.org/sfpug/
>
> am I mistaken?
>
> If so, is there a way to keep a running tally entirely in PostgreSQL?
>
> Thanks,
> Adi

Joe Celko has some examples in his "SQL for Smarties" book for doing
running aggregates entirely in SQL. I believe they will work in most
database servers. I don't have the book handy but it's a place to look.

On the Celko note - has anyone implemented his nested tree model? It's
really great for arbitrarily deeply nested hierarchies.

Brian

> On Tue, May 11, 2004 at 01:10:58PM -0400, Aditya wrote:
>
>>I could have sworn that I saw an email from Josh Berkus on sfpug about how to
>>do running aggregates (ie. a running tally or total, say in an invoice)
>>entirely in PostgreSQL, however googling only finds:
>>
>> http://archives.postgresql.org/pgsql-novice/2003-09/msg00305.php
>>
>>and I can't find it in the sfpug archives:
>>
>> http://archives.postgresql.org/sfpug/
>>
>>am I mistaken?
>>
>>If so, is there a way to keep a running tally entirely in PostgreSQL?

On Tue, May 11, 2004 at 11:01:13AM -0700, Brian Ghidinelli wrote:
>
> Joe Celko has some examples in his "SQL for Smarties" book for doing
> running aggregates entirely in SQL. I believe they will work in most
> database servers. I don't have the book handy but it's a place to look.

It's handy :)

BTW, one way I've done it is to create a temporary sequence, then use
it to impose an ordered row structure.

> On the Celko note - has anyone implemented his nested tree model?
> It's really great for arbitrarily deeply nested hierarchies.

See contrib/ltree.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Brian,

> On the Celko note - has anyone implemented his nested tree model? It's
> really great for arbitrarily deeply nested hierarchies.

I have. I find it really, really fast for "in branch" queries (which other
trees suck at) but really, really slow to update. So it's a good model for
a tree which will be modified, say, once every 10,000 reads.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

David,

> > On the Celko note - has anyone implemented his nested tree model?
> > It's really great for arbitrarily deeply nested hierarchies.
>
> See contrib/ltree.

ltree isn't a nested set ... it's a combination text chain and adjacency list.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Tue, May 11, 2004 at 11:01:13AM -0700, Brian Ghidinelli wrote:
> Joe Celko has some examples in his "SQL for Smarties" book for doing
> running aggregates entirely in SQL. I believe they will work in most
> database servers. I don't have the book handy but it's a place to look.

Thanks for all the suggestions, and of course the most obvious way (in
hindsight) eluded me:

a table mytable with:

id, tdate, description, cost

and you can produce a "invoice" like thing with a running total as:

select
t.id,
t.tdate,
t.description,
t.cost,
(select sum(cost) from mytable where tdate <= t.tdate)
from
mytable t
order by
2
;

needless to say, the subselect gets more expensive as your table gets larger
so using an "aggregates" table is probably worth doing for large tables.

Adi

Josh Berkus wrote:
> I have. I find it really, really fast for "in branch" queries (which other
> trees suck at) but really, really slow to update. So it's a good model for
> a tree which will be modified, say, once every 10,000 reads.

Same here. I've used it in a Sybase/MSSQL environment with Transact-SQL
stored procedures but my postgres kung-fu is nowhere near adequate to
replicate that functionality. I have used it for a message board system
that allowed deep threading successfully. I think it would also be good
for large organizational charts or family tree type models but I don't
have any of those really in my current project (motorsport event
management software).

Cheers,

Brian