CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
avi
avi

Reputation: 1846

Compare between values from the same table in postgresql

I have the following table:

id partid orderdate       qty price
1    10     01/01/2017    10    3
2    10     02/01/2017    5     9
3    11     01/01/2017    0.5   0.001
4    145    02/01/2017    5     18
5    10     12/12/2016    8     7
6    10     05/07/2010    81    7.5

Basically I want to compare the most recent purchasing of parts to the other purchasing of the same part in a period of 24 months. For that matter compare id=2 to id = 1,5.

I want to check if the price of the latest orderdate (per part) is larger than the average price of that part in the last 24 months.

So first I need to calculate the avg price:

partid avgprice
10       (3+9+7)/3=6.33  (7.5 is out of range)
11        0.001
145        18

I also need to know the latest orderdate of each part:

id partid
2    10
3    11
4    145

and then I need to check if id=2, id=3, id=6 (latest purchases) are bigger than the average. If they are I need to return their partid.

So I should have something like this:

id partid  avgprice   lastprice
2    10      6.33         9
3    11      0.001      0.001
4    145     18         18

Finally I need to return partid=10 since 9>6.33

Now to my questions... I'm not sure how I can find the latest order in PostgreSQL. I tried:

select id, distinct partid,orderdate
from table
where orderdate> current_date - interval '24 months'
order by orderdate desc

This gives :

ERROR: syntax error at or near "distinct".

I'm a bit of a lost here. I know what I want to do but I cant translate it to SQL. Any one can help?

Upvotes: 1

Views: 2323

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Get the avarage per part and the last order per price and join these:

select
  lastorder.id,
  lastorder.partid,
  lastorder.orderdate,
  lastorder.price as lastprice,
  avgorder.price as avgprice
from
(
  select
    partid, 
    avg(price) as price
  from mytable
  where orderdate >= current_date - interval '24 months'
  group by partid
) avgorder 
join
(
  select distinct on (partid)
    id,
    partid, 
    orderdate,
    price
  from mytable
  order by partid, orderdate desc
) lastorder on  lastorder.partid = avgorder.partid 
            and lastorder.price  > avgorder.price;

Upvotes: 2

Georgi Raychev
Georgi Raychev

Reputation: 1334

This can be solved without distinct (which is heavy on the DB anyways):

with avg_price as (
select partid, avg(price) as price
from table
where orderdate> current_date - interval '24 months'
group by partid
)
select f.id, f.partid, av.price, f.price
from (
        select id, partid, orderdate, price, rank() over (partition by partid order by orderdate desc)
        from table
    ) as f
join avg_price av on f.partid = av.partid
where f.rank = 1
and av.price < f.price

Upvotes: 1

Related Questions