Re: Calculated values

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