On Jan 27, 2009, at 7:12 PM, Karsten Hilbert wrote:
Hello all,
maybe some general advice can be had on this:
table test_results
modified_by integer foreign key staff(pk),
intended_reviewer integer foreign key staff(pk),
actual_reviewer integer foreign key staff(pk)
(this table will contain millions of rows)
table staff
pk integer
name text
(this table will contain at most 50 rows)
Now I want to set up a view which aggregates test results
with staff names for all three foreign keys. This would mean
I would either have to
- join test_results to staff three times, once for each
of the foreign keys, this is going to be messy with
tracking table aliases, duplicate column names etc
- write three explicit sub-selects for the columns I want
to denormalize into the view definition
Is there general advice as to which of the alternatives is
worse under most if not all circumstances ?
I did something similar once using expression logic for my aggregates:
SELECT
SUM(CASE WHEN modified_by = pk THEN 1 ELSE 0 END) AS modified_by_count,
SUM(CASE WHEN intended_reviewer = pk THEN 1 ELSE 0 END) AS
intended_reviewer_count,
SUM(CASE WHEN actual_reviewer = pk THEN 1 ELSE 0 END) AS
actual_reviewer_count
FROM test_results, staff
WHERE pk IN (modified_by, intended_reviewer, actual_reviewer)
Mind, this will very probably do a sequential scan over the product of
both tables, but at least now the staff table is in that product only
once.
In actuality I didn't use CASE statements but cast the boolean results
of the expressions directly to integer, something like
SUM((modified_by = pk)::int), but that cast may no longer work since
8.3.
I no longer have access to the project that I used this on, so I can't
verify unfortunately.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,497f5aa8747035160810079!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
