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
