From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: obtaining difference between minimum value and next in size
Date: Wed, 17 Nov 2010 17:02:16 +0100
Message-ID: <20101117160216.GA32573@tux> (raw)
In-Reply-To: <3BC753A3784F488F98304300B6E437FD@squarepi.com>
References: <3BC753A3784F488F98304300B6E437FD@squarepi.com>
John Lister <john.lister-ps@kickstone.com> wrote:
> Hi, I was wondering if it is possible to do this with a single query rather
> than iterate over all of the rows in an application:
>
> I have a table which for brevity looks like:
> create table offers {
> integer id;
> integer product_id;
> double price;
> }
>
> where for each product there is a number of offers in this table. Now my
> question:
> Is it possible to obtain the difference between just the minimum price and the
> next one up per product, so say I have the following data:
> id, product_id, price
> 123, 2, 10.01
> 125, 2, 10.05
> 128, 2, 11.30
> 134, 3, 9.45
> 147, 3, 11.42
> 157, 3, 12.08
> 167, 3, 12.09
>
> then I would like the following returned
> product_id, difference
> 2, .04 (10.05-10.01)
> 3, 1.97 (11.42-9.45)
>
> ,etc
>
>
> Any ideas?
Sure, as Tom Lane pointed out, with >= 8.4:
test=*# select * from offers ;
id | product_id | price
-----+------------+-------
123 | 2 | 10.01
125 | 2 | 10.05
128 | 2 | 11.30
134 | 3 | 9.45
147 | 3 | 11.42
157 | 3 | 12.08
167 | 3 | 12.09
(7 Zeilen)
Zeit: 0,204 ms
test=*# select product_id, price, price - lag(price) over (partition by
product_id order by product_id, price), row_number() over (partition by
product_id)from offers;
product_id | price | ?column? | row_number
------------+-------+----------+------------
2 | 10.01 | | 1
2 | 10.05 | 0.04 | 2
2 | 11.30 | 1.25 | 3
3 | 9.45 | | 1
3 | 11.42 | 1.97 | 2
3 | 12.08 | 0.66 | 3
3 | 12.09 | 0.01 | 4
(7 Zeilen)
Zeit: 0,415 ms
test=*# select product_id, price, difference from (select product_id,
price, price - lag(price) over (partition by product_id order by
product_id, price) as difference, row_number() over (partition by
product_id) from offers) foo where row_number <= 2;
product_id | price | difference
------------+-------+------------
2 | 10.01 |
2 | 10.05 | 0.04
3 | 9.45 |
3 | 11.42 | 1.97
(4 Zeilen)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: obtaining difference between minimum value and next in size
In-Reply-To: <20101117160216.GA32573@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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