0

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.

  • 1
    Check theLAG() function. – ypercubeᵀᴹ Jun 27 '14 at 11:35
  • I have checked the lag() function. I have to group by object ID because I don't care about events for other objects. All I get is "not a GROUP BY expression" when I use lag(). – kainaw Jun 27 '14 at 11:55
  • So, show us the command you ran, perhaps there is something wrong with your sql that had LAG and gave error? – Raj Jun 27 '14 at 15:25
  • No need for self-join. Do you actually want to UPDATE the table or just show the result? – ypercubeᵀᴹ Jun 28 '14 at 11:01
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.