How to get column value from previous row for each group?

Question

I'll try to explain the problem right:

I have a table like this:

Table: stg_mov_salida

id_movement | id_product | id_warehouse | movement_date | id_sale | sale_value | ...
209338          54545          2          "2011-05-21"    163299     75.95
209306          54545          2          "2011-06-05"    163267     75.95
209347          54545          2          "2012-10-26"    163308     77.95
209399          54545          3          "2011-01-22"    163360     79.95
209302          54545          3          "2011-01-23"    163263     72.95
209304          54545          3          "2011-01-24"    163265     72.95

And I want to get the sale_value of the previous row, but only if the previous row belongs to the same id_product and id_warehouse that the current row.

The table stg_mov_salida is a staging table ordered by:

 id_product asc, id_warehouse asc, movement_date asc

As I've read on other questions (like this or similar), I've tried to do it without success. The last query (and the closest to my solution) I've tried is this:

SELECT x2.*,
   (SELECT x2.sale_value AS last_sale_value
            FROM stg_mov_salida ms 
                WHERE x2.id_product = ms.id_product 
                AND x2.id_warehouse = ms.id_warehouse
                AND x2.date_movement  > ms.date_movement 
            ORDER BY ms.id_product asc, 
                             ms.id_warehouse asc, 
                             ms.date_movement asc
            LIMIT 1)
FROM stg_mov_salida x2

But this query is so slow that I've never seen it finishing, even for only one given id_product, therefore if this query is correct, I can't use it because is too slow.

Anyone knows how to solve my problem?

UPDATE:

Expected result:

id_movement | id_product | id_ware | mov_date | id_sale | sale_value | prev_sale_value
  209338       54545          2    "2011-05-21"  163299     75.95           null
  209306       54545          2    "2011-06-05"  163267     75.95           75.95
  209347       54545          2    "2012-10-26"  163308     77.95           75.95
  209399       54545          3    "2011-01-22"  163360     79.95           null
  209302       54545          3    "2011-01-23"  163263     72.95           79.95
  209304       54545          3    "2011-01-24"  163265     72.95           72.95

The null values on prev_sale_value would be because that row is the first on the group of (id_product, id_warehouse), but the idea is to replace that null with the sale_value of the same row directly, but I'll do it when I get the correct prev_sale_value for each row.

Any help would be appreciated

For easily response, here is the SQLFiddle

I've tried this query too, but it doesn't works properly:

select x2.*,
lag(sale_value, 1) over (partition by sale_value
                                order by id_product asc,
                                id_warehouse asc,
                                date_movement asc) as last_sale_value

from stg_mov_salida x2
order by id_product asc, id_warehouse asc, date_movement asc

What is the expected output for the example table that you have given ? — Sunny Nanda, Jan 31 '14 at 13:27
Updated question with expected result. If you need additional info don't doubt to ask! — carexcer, Jan 31 '14 at 13:36
Check out the `lag()` function: http://www.postgresql.org/docs/current/static/tutorial-window.html — , Jan 31 '14 at 15:47
I've tried using `lag()`, and it improves the execution time but using it I don't get the expected result yet. — carexcer, Jan 31 '14 at 16:18

Mureinik · Accepted Answer · 2014-01-31T17:09:58.693

1

Using lag is the right way to go, but your arguments are wrong. As you stated:

I want to get the sale_value of the previous row, but only if the previous row belongs to the same id_product and id_warehouse that the current row.

This means that your partiton by clause should include id_product and id_warehouse:

select x2.*,
lag(sale_value, 1) over (partition by id_product asc, id_warehouse asc
                         order by date_movement asc) as last_sale_value    
from stg_mov_salida x2
order by id_product asc, id_warehouse asc, date_movement asc

edited Jan 31 '14 at 17:09

answered Jan 31 '14 at 16:26

Mureinik

297,002
52
306
350

Perfect! Only you should delete `sale_value` from `partition by` clause and it works perfectly! Correct it for future visitors ;) Thank you very much! – carexcer Jan 31 '14 at 16:41
@carexcer arg, rigtht. Copy-paste bug - fixed. Thanks for noticing. – Mureinik Jan 31 '14 at 17:10

How to get column value from previous row for each group?

1 Answers1