Re: Calculated values - Mailing list pgsql-general

Greetings, and thanks so much for your reply!

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Camm Maguire <camm@enhanced.com> writes:
> > Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> >> 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
>
> > I can't seem to get this syntax to work with pg.  No subselects seem
> > to be accepted in the from clause, and join doesn't seem to be a
> > reserved word at all.
>
> Sounds like you are trying to do it in 7.0 or before.  Emmanuel is
> relying on 7.1 features --- and the example won't work as given anyway,
> since (a) the subselects neglect to specify source tables; (b) you
> have to write CROSS JOIN not JOIN if you want to omit ON/USING.
>

Thanks!  Indeed, I'm using 7.0.3.

> In 7.0 you could accomplish the same thing with temp tables, or more
> straightforwardly by something like
>
> SELECT    id,
>     SUM(item) AS partialsum,
>     SUM(item) / (SELECT SUM(item) FROM table) AS percentage
> FROM table
> GROUP BY id


>
> This relies for efficiency on the poorly-documented fact that the
> sub-select will only be evaluated once, since it has no dependency
> on the state of the outer select.  (You can check this by seeing that
> EXPLAIN shows the subselect as an InitPlan not a SubPlan.)

Thanks again.  Alas, the 'explain' for me still shows the subplan, and
I can see why.  Here is my view definition:

create view csp2 as
    SELECT c1.asof, c1.ticker, c1.sp, c1.price AS p1,
    (((100 * float8(c1.sp)) * c1.price) /
        (select sum(price*sp) from sprices,sinfo
            where sprices.sinfo = sinfo.sinfo
            and sprices.asof = c1.asof)) AS wt,
    c2.price AS p2, c2.split, c1.div,
    (100 * c1.ret)
    FROM csp1 c1, csp1 c2,  dates
    WHERE ((((c1.asof = dates.asof))
        AND (c2.asof = dates.nasof))
        AND (c1.id = c2.id));

What is obviously doing this is the 'sprices.asof = c1.asof'
dependency between the inner and outer select.  Trouble is, my only
intention is to be able to use this view with a constant, albeit
'runtime-selectable', 'asof' or date, as in

select * from csp where asof = '20000103';

Any other suggestions?  This dependency issue slows the above query
down significantly, resulting in the calculation of the same sum ~
1000 times.  Do you also agree with the previous respondent that
trying to have a table of sums, updated dynamically with triggers, is
not a good idea?  I'm trying to find the right philosophy to the
design of this db, and am ending up searching for a happy medium
between select speed and insert complexity.


>
>             regards, tom lane
>
>

Thanks again,

--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah