Thread: obtaining difference between minimum value and next in size

obtaining difference between minimum value and next in size

From

"John Lister"

Date:

17 November 2010, 14:34:22

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/

Re: obtaining difference between minimum value and next in size

From

Tom Lane

Date:

17 November 2010, 14:43:11

"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

Re: obtaining difference between minimum value and next in size

From

Andreas Kretschmer

Date:

17 November 2010, 15:02:54

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.01125 |          2 | 10.05128 |          2 | 11.30134 |          3 |
9.45147|          3 | 11.42157 |          3 | 12.08167 |          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°

Re: obtaining difference between minimum value and next in size

From

"Oliveiros d'Azevedo Cristina"

Date:

17 November 2010, 15:09:24

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/

Re: obtaining difference between minimum value and next in size

From

"John Lister"

Date:

17 November 2010, 21:46:34

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/

Re: obtaining difference between minimum value and next in size

From

"Oliveiros d'Azevedo Cristina"

Date:

18 November 2010, 09:09:21

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/