mysql group by uid to calculate consecutive date diff

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

mysql version: 5.5

Answer

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;

screen capture from demo link below

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

Leave a Reply

Your email address will not be published. Required fields are marked *