Thread: Difference from average

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=200 and one for price=300, 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=1.03..2.05 rows=2 width=32) InitPlan   ->  Aggregate  (cost=1.03..1.03 rows=1 width=8)
->  Seq Scan on prices  (cost=0.00..1.02 rows=2 width=8) 

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

Neil Saunders wrote:
> 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=200 and one for price=300, 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;

I'd personally write it something like:

SELECT  prices.property_id,  prices.price AS actual_price,  averages.avg_price,  (averages.avg_price - prices.price) AS
price_diff ((averages.avg_price - prices.price)/averages.avg_price) AS pc_diff
 
FROM  prices,  (SELECT property_id, avg(price) as avg_price FROM prices) AS averages
WHERE  prices.property_id = averages.property_id
;

That's as much to do with how I think about the problem as to any 
testing though.

--   Richard Huxton  Archonet Ltd