price difference based on week dates

Question

I have the input like:

+-----+---------+-------+------------+------------+
| key | company | price | week_start | price_diff |
+-----+---------+-------+------------+------------+
| 123 | xxx     |    15 | 02-06-2015 |            |
| 456 | xxx     |    20 | 02-06-2015 |            |
| 789 | yyy     |    30 | 02-06-2015 |            |
| 122 | zzz     |    40 | 02-06-2015 |            |
| 123 | xxx     |    50 | 09-06-2015 |            |
| 456 | xxx     |    60 | 09-06-2015 |            |
| 333 | yyy     |    70 | 09-06-2015 |            |
+-----+---------+-------+------------+------------+

expected output:

update the column price_diff

+-----+---------+-------+------------+------------+
| key | company | price | week_start | price_diff |
+-----+---------+-------+------------+------------+
| 123 | xxx     |    15 | 02-06-2015 |            |
| 456 | xxx     |    20 | 02-06-2015 |            |
| 789 | yyy     |    30 | 02-06-2015 |            |
| 122 | zzz     |    40 | 02-06-2015 |            |
| 123 | xxx     |    50 | 09-06-2015 |         35 |
| 456 | xxx     |    60 | 09-06-2015 |         40 |
| 333 | yyy     |    70 | 09-06-2015 |            |
+-----+---------+-------+------------+------------+

for company= 'xxx' and key = 123 I want to take the price difference based on week_start. From the above input for company = 'xxx' and key = 123, second week has price = 50 and the first week price = 15. I need to update the price_diff with difference of those two values.

Thanks In Advance

What about `y`? There are only 2 weeks? Are they always like 02---09---16... or there can be 02---04---07---10---16...? — Giorgi Nakeuri, Jun 26 '15 at 08:19
Thanks for Reply.. i have many weeks it will be always like 02---09---16 .. and i want to update the price_diff week to week and its only for company = 'xxx' — Rajesh, Jun 26 '15 at 08:21

score 0 · Answer 1 · edited May 23 '17 at 10:26

The way you should proceed is to do a self-join, meaning joining the table with itself based on the 1 week difference between dates of the same company and the ID's of the company.

In my below query I have also added a JOIN on KEYs since this logic would apparently map your data, but I don't recommend using duplicate KEY values, if this the column which should contain unique values (in case you're not using a composite key).

But anyway, here is what you can try (assuming that your table is called "prices"):

SELECT p1.*
    , p1.price - p2.price AS 'price_diff'
FROM prices p1
    LEFT JOIN prices p2 ON p1.company = p2.company
        AND p2.week_start = p1.week_start - 7 * INTERVAL '1 day'
        AND p1.KEY = p2.KEY
ORDER BY p1.week_start, p1.company, p1.KEY

Also, a sample working SQLFiddle.

Update:

In order to update the price_diff column, based on my above query you will have to change it to an UPDATE, like below:

UPDATE prices
    SET price_diff = p1.price - p2.price
FROM prices p1
    LEFT JOIN prices p2 ON p1.company = p2.company
        AND p2.week_start = p1.week_start - 7 * INTERVAL '1 day'
        AND p1.KEY = p2.KEY

Thanks for your reply.. I want to update the price_diff column — Rajesh, Jun 26 '15 at 08:37
I got an error while while updating Error:Target table must be part of an equijoin predicate.. I'm using Postgres — Rajesh, Jun 26 '15 at 09:03

Patrick · Answer 2 · 2015-06-26T08:46:40.377

0

You can do this with a window function:

WITH diff (key, company, week_start, price) AS (
  SELECT key, company, week_start,
         price - lag(price) OVER (PARTITION BY key, company ORDER BY week_start)
  FROM mytable)
UPDATE mytable
SET price_diff = diff.price
FROM diff
WHERE company = 'xxx' AND diff.company = 'xxx'
  AND key = 123 AND diff.key = 123
  AND week_start = diff.week_start;

edited Jun 26 '15 at 08:46

answered Jun 26 '15 at 08:28

Patrick

29,357
6
62
90

Your query gives me a syntax error. You have an extra apostrophe before `diff.key = 123`. – Radu Gheorghiu Jun 26 '15 at 08:36

score 0 · Answer 3 · answered Jun 26 '15 at 10:35

update mytable
set price_diff = price - (
    select price
    from mytable as t2
    where 
        and t2.key = k1.key and t2.company = 'xxx'
        and t2.week_start = mytable.week_start - 7 * INTERVAL '1 day'
    )
where company = 'xxx' and price_diff is null

price difference based on week dates

3 Answers3