mysql group by uid to calculate consecutive date diff

Question

data

uid  data_date 
a    2020-12-31
a    2021-01-01
a    2021-01-05
b    2021-03-02
b    2021-03-05
b    2021-03-07

Expected

uid  date_diff
a    1
a    4
b    3
b    2

Try and Ref

• Getting date difference between consecutive rows in the same group. I could not use any windows function because of mysql version.

mysql version: 5.5

Answer 1

On MySQL 8+, we can try using LAG along with DATEDIFF:

WITH cte AS (
    SELECT *, LAG(data_date) OVER (PARTITION BY uid
                                   ORDER BY data_date) AS lag_data_date
    FROM yourTable
)

SELECT uid, DATEDIFF(data_date, lag_data_date) AS date_diff
FROM cte
WHERE lag_data_date IS NOT NULL
ORDER BY uid, data_date;

Demo

The HAVING clause above filters off the "first" record for each uid group, as that first record technically doesn't have any date diff associated with it (nor does your expected output contain this record).

Edit:

Here is a version which should run on MySQL 5.7 or earlier. It uses a correlated subquery instead of LAG(), to find the lag date:

SELECT uid,
    DATEDIFF(data_date,
        (SELECT t2.data_date FROM yourTable t2
         WHERE t2.uid = t1.uid AND t2.data_date < t1.data_date
         ORDER BY t2.data_date DESC LIMIT 1)) AS date_diff
FROM yourTable t1
HAVING date_diff IS NOT NULL
ORDER BY uid, data_date;

Demo