From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Neil Saunders <n(dot)j(dot)saunders(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Difference from average |
Date: | 2005-10-11 14:24:31 |
Message-ID: | 434BCB1F.7040403@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2005-10-11 14:27:25 | Returning NULL results? |
Previous Message | Judith Altamirano Figueroa | 2005-10-11 14:22:02 | ichar |