From: | Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Calculated values |
Date: | 2001-01-26 18:51:00 |
Message-ID: | 3A71C714.AFF4860@bacbuc.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Camm Maguire wrote:
>
> Greetings! What is the 'best way' or 'guiding philosophy' if there is
> one for dealing with calculated values in a pg database? For example,
> say you have a table with a column of floats, and you want to make a
> view showing this column as a percent of the total across the column.
> Should you
>
> a) create another table with the total, and put on triggers on insert,
> update, and delete to modify the right total or
>
> b) create the view with a specific subselect to recalculate the total
> at select time. This has the disadvantage that the total seems to be
> recalculated for each row. Is there any sql syntax which can merge a
> dynamically generated aggregate, *calculated only once*, into each
> output row?
Hmmm ... You want to avoid "remerging", if I follow you ...
Coud you try :
CREATE VIEW my view AS
SELECT id, partialsum, (partialsum/totalsum) AS percentage
FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause
That way, totalsum should be computed once.
"Caching" computations in a secondary table is a (somewhat hidden) form
of redundancy, and therefore a nice way to ask for trouble ...
Hope this helps ...
--
Emmanuel Charpentier
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-01-26 18:58:51 | Re: Update Trigger Inconsistency with 7.1? |
Previous Message | Tom Lane | 2001-01-26 18:49:43 | Re: Performance: Unix sockets vs. TCP/IP sockets |