append last 3 date entries for each row and id mysql

Question

I'm currently doing this logic outside of SQL, but it takes forever. Is it possible to do this with SQL to speed it up?

I have data:

id    date          
1     2020-01-01
1     2019-08-01
1     2018-06-01
1     2018-03-01
2     2020-04-01
2     2020-07-01

Every row has an ID and date column.

For every row in this table, I want to append the previous 3 dates for that id.

So the output in this example would be:

id    date0         date1         date2         date3
1     2020-01-01    2019-08-01    2018-06-01    2018-03-01 
1     2019-08-01    2018-06-01    2018-03-01    
1     2018-06-01    2018-03-01    
1     2018-03-01   
2     2020-04-01    2020-07-01
2     2020-07-01

Is this possible?

Answer 1

If you are running MySQL 8.0, you can just use the two-arguments form of lag():

select id, date,
    lag(date, 1) over(partition by id order by date) date1,
    lag(date, 2) over(partition by id order by date) date2,
    lag(date, 3) over(partition by id order by date) date3
from mytable

In earlier versions, an alternative is a couple of correlated subqueries:

select id, date,
    (select t1.date from mytable t1 where t1.id = t.id and t1.date < t.date order by t1.date desc limit 1) date1,
    (select t1.date from mytable t1 where t1.id = t.id and t1.date < t.date order by t1.date desc limit 1, 1) date2,
    (select t1.date from mytable t1 where t1.id = t.id and t1.date < t.date order by t1.date desc limit 2, 1) date3
from mytable t