1

I have a table full of prices, items, and dates. An example of this is:

AA, 1/2/3024, 1.22
AA, 1/3/3024, 1.23
BB, 1/2/3024, 4.22
BB, 1/3/3024, 4.23

Within the data there are only two rows per price, and they are ordered by date. How would I condense this data set into a single product row showing the difference from the last price to the previous? [Also this applies to a ratio, so AA would produce 1.23/1.22].

The result should look like

AA, todays price-yesterdays price

Despite being a sum function, there is no subtraction function over a list.

I'm using postgres 9.1.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
monksy
  • 14,156
  • 17
  • 75
  • 124

3 Answers3

3

Since there are only two rows per price, this can be much simpler and faster:

SELECT n.item, n.price - o.price AS diff, n.price / o.price AS ratio
FROM   price n                 -- "new"
JOIN   price o USING (item)    -- "old"
WHERE  n.day > o.day;

->SQLfiddle

This form carries the additional benefit, that you can use all columns from both rows directly.

For more complex scenarios (not necessary for this), you could use window functions as has been pointed out. Here is a simpler approach than what has been suggested:

SELECT DISTINCT ON (item)
       item
      ,price - lead(price) OVER (PARTITION BY item ORDER BY day DESC) AS diff
FROM   price
ORDER  BY item, day DESC;

Only one window function is needed here. And one query level, since DISTINCT ON is applied after window functions. The sort order in the window agrees with overall sort order, helping performance.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2
select product,
       sales_date,
       current_price - prev_price as diff
from (
  select product,
         sales_date, 
         price as current_price,
         lag(price) over (partition by product order by sales_date) as prev_price,
         row_number() over (partition by product order by sales_date desc) as rn
  from the_unknown_table
) t
where rn = 1;

SQLFiddle example: http://sqlfiddle.com/#!15/9f7d6/1

  • It took me a while to understand what the row number was needed for but now I understand – monksy Nov 28 '13 at 22:30
  • Third comment.. but thats rather clever about the use of order by and order by desc. I thought it was a mistake at first. – monksy Nov 28 '13 at 23:09
1

If there's only two rows per item, then

SELECT item, MAX(price) - MIN(price) AS diff, MAX(price) / MIN(price) AS ratio
FROM yourtable
GROUP BY item

will do the trick.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • That won't work bescause the difference must be based on the date. Aka today's price - yesterday's price. – monksy Nov 28 '13 at 21:58