如何用SQL计算同房间每日相邻行时间差？能否用LAG函数？

问题描述 #

需要按房间和自然日（从0点开始），计算每行的Emp_IN与上一行的Emp_OUT的分钟差值；每组（同房间同日期）的首行差值为0点到Emp_IN的分钟数。

示例数据表 #

问题：是否可以使用LAG函数实现该计算，或是有其他可行的逻辑方法？

解答 #

一、LAG函数是最优实现方案 #

完全可以用LAG函数实现，这是现代SQL中处理这类行上下文计算最简洁高效的方式。核心逻辑是：

• 按Room和Date分组（确保只在同房间同日期内对比）
• 按Emp_IN排序（保证行顺序符合时间先后）
• 用LAG获取同组内上一行的Emp_OUT值，首行则用当日0点作为参考时间
• 通过日期函数计算两个时间点的分钟差值

不同数据库的示例代码

MySQL

SELECT 
    Date,
    EMP_ID,
    Room,
    Emp_IN,
    Emp_OUT,
    TIMESTAMPDIFF(
        MINUTE,
        COALESCE(
            LAG(Emp_OUT) OVER (PARTITION BY Room, Date ORDER BY Emp_IN),
            CONCAT(Date, ' 00:00:00')
        ),
        CONCAT(Date, ' ', Emp_IN)
    ) AS Difference_In_Min
FROM your_table_name;

PostgreSQL

SELECT 
    Date,
    EMP_ID,
    Room,
    Emp_IN,
    Emp_OUT,
    EXTRACT(EPOCH FROM (
        (Date::TIMESTAMP || ' ' || Emp_IN) - 
        COALESCE(LAG(Date::TIMESTAMP || ' ' || Emp_OUT) OVER (PARTITION BY Room, Date ORDER BY Emp_IN), Date::TIMESTAMP)
    )) / 60 AS Difference_In_Min
FROM your_table_name;

SQL Server

SELECT 
    Date,
    EMP_ID,
    Room,
    Emp_IN,
    Emp_OUT,
    DATEDIFF(
        MINUTE,
        COALESCE(
            LAG(CONVERT(DATETIME, CONCAT(Date, ' ', Emp_OUT))) OVER (PARTITION BY Room, Date ORDER BY Emp_IN),
            CONVERT(DATETIME, CONCAT(Date, ' 00:00:00'))
        ),
        CONVERT(DATETIME, CONCAT(Date, ' ', Emp_IN))
    ) AS Difference_In_Min
FROM your_table_name;

二、其他可行方法（适用于不支持窗口函数的场景） #

1. 自连接查询

通过自连接匹配同组内时间早于当前行的最大Emp_OUT，首行直接计算与0点的差值：

SELECT 
    t1.Date,
    t1.EMP_ID,
    t1.Room,
    t1.Emp_IN,
    t1.Emp_OUT,
    CASE
        WHEN MAX(t2.Emp_OUT) IS NULL THEN TIMESTAMPDIFF(MINUTE, CONCAT(t1.Date, ' 00:00'), CONCAT(t1.Date, ' ', t1.Emp_IN))
        ELSE TIMESTAMPDIFF(MINUTE, CONCAT(t1.Date, ' ', MAX(t2.Emp_OUT)), CONCAT(t1.Date, ' ', t1.Emp_IN))
    END AS Difference_In_Min
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.Room = t2.Room 
    AND t1.Date = t2.Date 
    AND t2.Emp_IN < t1.Emp_IN
GROUP BY t1.Date, t1.EMP_ID, t1.Room, t1.Emp_IN, t1.Emp_OUT
ORDER BY t1.Room, t1.Date, t1.Emp_IN;

2. MySQL变量赋值法

利用用户变量记录上一行的房间和Emp_OUT值，逐行计算差值：

SET @prev_room = '', @prev_out = '';
SELECT 
    Date,
    EMP_ID,
    Room,
    Emp_IN,
    Emp_OUT,
    TIMESTAMPDIFF(
        MINUTE,
        CASE 
            WHEN Room != @prev_room THEN CONCAT(Date, ' 00:00')
            ELSE CONCAT(Date, ' ', @prev_out)
        END,
        CONCAT(Date, ' ', Emp_IN)
    ) AS Difference_In_Min,
    @prev_room := Room,
    @prev_out := Emp_OUT
FROM your_table_name
ORDER BY Room, Date, Emp_IN;

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