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
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
On MySQL 8+, we can try using
LAG along with
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;
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).
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;