2

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.

6

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.

  • LAG(*) OVER (...) an option, or would I have to be explicit? – Eldamir Jun 6 '16 at 7:04
  • 2
    No, there is no LAG(*) syntax. You have to explicitly state it for every column you need. Added an alternative. – ypercubeᵀᴹ Jun 6 '16 at 8:28
  • Finally got around to giving this a try. Unfortunatly, the query is incredibly inefficient. took 46s on my dataset. I'll look into alternative solution, but yours is the best I've got so far – Eldamir Jul 27 '16 at 9:34
  • What version of Postgres, how big is the table and what indexes are there? Do you have an index on (id, date) or on (id, date DESC)? – ypercubeᵀᴹ Jul 27 '16 at 9:40
  • 1
    After indexing on id and date, I'm down from 46s to 128ms. Couldn't be happier – Eldamir Jul 28 '16 at 6:43
2

Just adding an alternative solution.

In the case where the date is actually a date range, you can use the postgres adjacency operator

   SELECT t1.id, t1.name, t1.date, t2.id, t2.name, t2.date
     FROM my_table t1
LEFT JOIN my_table t2
       ON t1.id = t2.id
      AND t2.date < t1.date
      AND t2.date -|- t1.date

This reads "left join same table where date range is before this date range and directly adjacent to it"

This assumes that the ranges are actually adjacent and never overlap, which is the case for my concrete implementation.

0

I'll add my bit too: self join on ROW_NUMBER:

With MyTable as (
  select 
    id,
    name,
    row_number() OVER (id ORDER BY date DESC) as RowNo 
  from table_name 
)

select 
  A.id,
  A.name as CurrentName,
  B.name as PreviousName,
  A.date as LastChangedOnDate,
  B.date as PreviouslyChangedOnDate
from MyTable A
left join MyTable B 
   on A.id = B.id and a.RowNo + 1 = B.RowNo
where A.RowNo = 1
  • That actually looks really interesting. How is the performance on that? – Eldamir Aug 11 '16 at 9:15
  • @Eldamir, I cannot test performance, but on other sql based systems it work good, especially with an index (id, date). – Stoleg Aug 12 '16 at 16:07

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.