Re: Calculated values

Greetings, and thanks so much for your reply!

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Camm Maguire <camm(at)enhanced(dot)com> writes:
> > Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)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(at)enhanced(dot)com
==========================================================================
"The earth is but one country, and mankind its citizens." -- Baha'u'llah