如何用SQL统计同一ID下同一列中相邻日期的间隔天数

问题描述 #

在SQL中，针对相同ID的记录，计算同一Date列中相邻日期的天数差，并将结果存入新的Days列。规则如下：

• 相同ID的第一条记录Days为0
• 后续记录为当前日期与前一条记录日期的天数差
• 若当前日期与前一条日期相同，天数差为0

输入示例数据 #

期望输出 #

解决方案 #

核心思路是用**窗口函数LAG()**获取同ID分组内的前一条记录日期，再结合日期差函数计算天数，最后处理默认值。

通用SQL代码 #

SELECT
    ID,
    Date,
    COALESCE(
        DATEDIFF(Date, LAG(Date) OVER (PARTITION BY ID ORDER BY Date)),
        0
    ) AS Days
FROM
    your_table_name;

代码说明 #

• PARTITION BY ID：按ID分组，确保只计算同一ID内的相邻记录
• ORDER BY Date：对每个ID内的记录按日期排序，保证相邻记录是时间顺序的
• LAG(Date)：获取当前记录的前一条记录的Date值，第一条记录无前置记录，返回NULL
• DATEDIFF(当前日期, 前一条日期)：计算两天的天数差（不同数据库的DATEDIFF参数顺序可能有差异，需注意调整）
• COALESCE(..., 0)：把NULL（第一条记录）替换为0，日期相同时DATEDIFF返回0，符合需求

不同数据库适配 #

MySQL/MariaDB

MySQL的DATEDIFF参数顺序为(结束日期, 开始日期)，直接使用通用版代码即可：

SELECT
    ID,
    Date,
    COALESCE(
        DATEDIFF(Date, LAG(Date) OVER (PARTITION BY ID ORDER BY Date)),
        0
    ) AS Days
FROM
    your_table_name;

SQL Server

SQL Server的DATEDIFF需要指定日期单位（这里用day），参数顺序为(单位, 开始日期, 结束日期)：

SELECT
    ID,
    Date,
    COALESCE(
        DATEDIFF(day, LAG(Date) OVER (PARTITION BY ID ORDER BY Date), Date),
        0
    ) AS Days
FROM
    your_table_name;

PostgreSQL

PostgreSQL可直接用日期减法，再转成整数：

SELECT
    ID,
    Date,
    COALESCE(
        (Date - LAG(Date) OVER (PARTITION BY ID ORDER BY Date))::INTEGER,
        0
    ) AS Days
FROM
    your_table_name;

验证 #

运行上述代码后，会生成与期望一致的结果，满足所有规则要求。

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