DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=GLUOKDXVak1/bsICvxB43jHhDEPmTDs6/H3PX4btd1b/DzO2UhInKYw3w9YbpbnyBaQ+jWUHqPL1Tn0iAc43uDq+6XxzXlLWxXllmC4EhoNfRLYmAOxFbBQc+Fop/zaGeBjWYlUMIrWxm63Y91htFVjSUrnFDS0kdxrswLIil5I=
Message-ID: <ddcd549e0510110620v601d5d5es6c88e87e609d1a73@mail.gmail.com>
Date: Tue, 11 Oct 2005 14:20:16 +0100
From: Neil Saunders <n.j.saunders@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Difference from average
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

I'm developing a property rental database. One of the tables tracks
the price per week for different properties:

CREATE TABLE "public"."prices" (
  "id" SERIAL,
  "property_id" INTEGER,
  "start_date" TIMESTAMP WITHOUT TIME ZONE,
  "end_date" TIMESTAMP WITHOUT TIME ZONE,
  "price" DOUBLE PRECISION NOT NULL
) WITH OIDS;

CREATE INDEX "prices_idx" ON "public"."prices"
  USING btree ("property_id");

I'd like to display the prices per property in a table, with each row
coloured different shades; darker shades representing the more
expensive periods for that property. To do this, I propose to
calculate the percentage difference of each rows price from the
average for that property, so if for example I have two rows, one for
price=3D200 and one for price=3D300, i'd like to retrieve both records
along with the calculated field indicating that the rows are -20%,
+20% from the average, respectively.

I've started with the following query, but since I'm still learning
how PostgreSQL works, I'm confused as to the efficiency of the
following statement:

SELECT *, (price - (SELECT avg(price) from prices)) as diff FROM prices;

EXPLAIN reveals (albeit not a real test, as only the two rows above)

Seq Scan on prices  (cost=3D1.03..2.05 rows=3D2 width=3D32)
  InitPlan
    ->  Aggregate  (cost=3D1.03..1.03 rows=3D1 width=3D8)
          ->  Seq Scan on prices  (cost=3D0.00..1.02 rows=3D2 width=3D8)

Does this mean that I'll be performing a nested table scan every time
I run this query? Also, I haven't yet calculated the percentage
difference for this, which in my eyes means another instance of
"SELECT avg(price) from prices". Is this the best way of doing this?
Can I optimize this away by re-writing this as a function and storing
"SELECT avg(price) from prices)" in a variable?

All opinions gratefully received.

Kind Regards,

Neil