pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference from average
2+ messages / 2 participants
[nested] [flat]
* Difference from average
@ 2005-10-11 13:20 Neil Saunders <n.j.saunders@gmail.com>
2005-10-11 14:24 ` Re: Difference from average Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Neil Saunders @ 2005-10-11 13:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Difference from average
2005-10-11 13:20 Difference from average Neil Saunders <n.j.saunders@gmail.com>
@ 2005-10-11 14:24 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-10-11 14:24 UTC (permalink / raw)
To: Neil Saunders <n.j.saunders@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-11 14:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-11 13:20 Difference from average Neil Saunders <n.j.saunders@gmail.com>
2005-10-11 14:24 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox