pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedobtaining difference between minimum value and next in size
6+ messages / 4 participants
[nested] [flat]
* obtaining difference between minimum value and next in size
@ 2010-11-17 15:11 John Lister <john.lister-ps@kickstone.com>
2010-11-17 15:42 ` Re: obtaining difference between minimum value and next in size Tom Lane <tgl@sss.pgh.pa.us>
2010-11-17 16:02 ` Re: obtaining difference between minimum value and next in size Andreas Kretschmer <akretschmer@spamfence.net>
2010-11-17 16:09 ` Re: obtaining difference between minimum value and next in size Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 3 replies; 6+ messages in thread
From: John Lister @ 2010-11-17 15:11 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: obtaining difference between minimum value and next in size
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
@ 2010-11-17 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-11-17 15:42 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-sql
"John Lister" <john.lister-ps@kickstone.com> writes:
> Is it possible to obtain the difference between just the minimum price and the next one up per product,
If you're using >= 8.4, try a window function. LEAD or LAG ought to
do it.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: obtaining difference between minimum value and next in size
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
@ 2010-11-17 16:02 ` Andreas Kretschmer <akretschmer@spamfence.net>
2 siblings, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2010-11-17 16:02 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: obtaining difference between minimum value and next in size
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
@ 2010-11-17 16:09 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-17 22:46 ` Re: obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
2 siblings, 1 reply; 6+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-11-17 16:09 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; pgsql-sql
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
----- Original Message -----
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size
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?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: obtaining difference between minimum value and next in size
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
2010-11-17 16:09 ` Re: obtaining difference between minimum value and next in size Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2010-11-17 22:46 ` John Lister <john.lister-ps@kickstone.com>
2010-11-18 10:09 ` Re: obtaining difference between minimum value and next in size Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 6+ messages in thread
From: John Lister @ 2010-11-17 22:46 UTC (permalink / raw)
To: pgsql-sql
Cheers oliverios and tom for your speedy replies. Unfortunately using v8.3 so the new functions are out. A big credit to oliverios for his sql fu, that seems to do exactly what I want and I think I pretty much understand the query. I always forget the comparison on the rows when thinking about groups.
John
----- Original Message -----
From: Oliveiros d'Azevedo Cristina
To: John Lister ; pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 4:09 PM
Subject: Re: [SQL] obtaining difference between minimum value and next in size
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
----- Original Message -----
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size
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?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: obtaining difference between minimum value and next in size
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
2010-11-17 16:09 ` Re: obtaining difference between minimum value and next in size Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-17 22:46 ` Re: obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
@ 2010-11-18 10:09 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 6+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-11-18 10:09 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; pgsql-sql
John,
Great to hear it helped you out
Best,
Oliver
----- Original Message -----
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 10:46 PM
Subject: Re: [SQL] obtaining difference between minimum value and next in size
Cheers oliverios and tom for your speedy replies. Unfortunately using v8.3 so the new functions are out. A big credit to oliverios for his sql fu, that seems to do exactly what I want and I think I pretty much understand the query. I always forget the comparison on the rows when thinking about groups.
John
----- Original Message -----
From: Oliveiros d'Azevedo Cristina
To: John Lister ; pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 4:09 PM
Subject: Re: [SQL] obtaining difference between minimum value and next in size
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
----- Original Message -----
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size
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?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-11-18 10:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-17 15:11 obtaining difference between minimum value and next in size John Lister <john.lister-ps@kickstone.com>
2010-11-17 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-17 16:02 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-11-17 16:09 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-17 22:46 ` John Lister <john.lister-ps@kickstone.com>
2010-11-18 10:09 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
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