PostgreSQL – How to Select Rows and Add Their Previous Rows

postgresql

I have a table structure that holds historical data for another table. Something like

id      | name        | date
----------------------------------------
1       | John        | 2016-01-01 14:38.123123
1       | John Smith  | 2016-01-03 16:46.123123
2       | ...         | 2016-01-01 14:38.123123
3       | ...         | 2016-01-01 14:38.123123

I want to make a query that gets each row, and for each row, also get the previous row (latest date before the rows date) with the same id, e.g. something like

SELECT id, name, date, previous.id, previous.name, previous.date
FROM ..?

Is this possible? This is a pretty general question, but in my specific case, I am using PostgreSQL, so if there is some postgres features that accomplishes this, that is fine.

Best Answer

This is exactly what the LAG() window function does, available since version 8.4:

SELECT id, name, date, 
       LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name, 
       LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
FROM table_name ;

There is also the option of using a LATERAL join (available from 9.3). Might be better if you want all the columns (of the previous row). This assumes that there is a UNIQUE constraint on (id, date):

SELECT t.id, t.name, t.date, 
       p.name AS previous_name, p.date AS previous_date
FROM table_name AS t
  LEFT JOIN LATERAL
    ( SELECT p.*
      FROM table_name AS p
      WHERE p.id = t.id
        AND p.date < t.date
      ORDER BY p.date DESC
      LIMIT 1
    ) AS p ON TRUE ;

An index on (id, date) would be helpful for both queries.

Related Question