Re: [SQL] obtaining difference between minimum value and next insize

#1

John Lister <john.lister-ps (AT) kickstone (DOT) com> wrote:

Quote:

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°

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [SQL] obtaining difference between minimum value and next insize

mailing.database.pgsql-sql mailing.database.pgsql-sql

Re: [SQL] obtaining difference between minimum value and next insize - 11-17-2010 , 10:02 AM