mysql group by uid 计算连续日期差异

在 MySQL 8+ 上，我们可以尝试将 LAG 与 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

上面的 HAVING 子句过滤掉每个 uid 组的“第一条”记录，因为第一条记录在技术上没有任何与之关联的日期差异（您的预期输出也不包含此记录）。

@H_675_3@编辑：

这是一个应该在 MySQL 5.7 或更早版本上运行的版本。它使用相关子查询而不是 LAG() 来查找滞后日期：

SELECT uid,(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