MySQL按用户计算连续日期的DATEDIFF差值问题

问题说明 #

现有MySQL数据表如下：

需要按用户分组，计算其每一组连续日期的差值，期望输出格式如下：

自行编写的SQL语句存在重复起始日期的问题，不符合需求：

SELECT 
    p1.id AS id,
    p1.name AS user,
    p1.date AS date1,
    p2.date AS date2,
    DATEDIFF(p2.date, p1.date) AS date_difference
FROM prova p1
JOIN prova p2 ON p1.name = p2.name AND p2.date > p1.date
ORDER BY p1.name, p1.date;

解决方案 #

使用窗口函数LEAD()可以精准匹配每个用户按日期排序后的下一条记录，从而获取连续的起始和结束日期，计算差值。

正确SQL语句（适配数据表prova） #

SELECT 
    name AS 用户,
    date AS 起始日期,
    LEAD(date) OVER (PARTITION BY name ORDER BY date) AS 结束日期,
    DATEDIFF(LEAD(date) OVER (PARTITION BY name ORDER BY date), date) AS 差值
FROM prova
WHERE LEAD(date) OVER (PARTITION BY name ORDER BY date) IS NOT NULL
ORDER BY name, date;

关键逻辑说明 #

• PARTITION BY name：按用户分组，确保每个用户的日期数据独立处理
• ORDER BY date：对每个用户的日期进行升序排序，保证相邻日期的顺序正确
• LEAD(date)：获取当前行的下一行日期，形成连续的起始-结束日期对
• WHERE子句：过滤掉无后续日期的记录（即每个用户的最后一条数据，无需计算差值）
• DATEDIFF：计算结束日期与起始日期的天数差值

执行该语句后，输出结果将完全符合期望，不会出现重复的起始日期。

内容的提问来源于stack exchange，提问作者Alessandro