Update previous value field

oracleupdate

I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value (and the values do not occur every day). I am looking for a way to quickly fill in a "previous value" field such that for any given record, previous value contains the value from that table where the object ID is the same and the date is the maximum date that occurs before the target row.

Note: I am using Oracle.

Example: {object, date, value}

1,2014-04-21,12
2,2014-03-01,38
1,2014-02-28,22
1,2014-01-01,28

In this case, the first row should have prev_value set to 22 (third row) because the third row matches on object ID and has the maximum date that occurs before the first row's date.

POSSIBLE SOLUTION:

I am using lag, but it is a bit of a nasty query. I select object_id and lag date over partition on object_id ordered by date. Then, I merge that back into the table to fill the previous date. It takes a very long time, but it appears to work.

Best Answer

You can use the LAG() function:

SELECT 
    "objectID", 
    "date",
    "value",
    LAG("value", 1) OVER (PARTITION BY "objectID" ORDER BY "date") 
        AS previous_value
FROM 
    tableX ;

Tested at SQLFiddle