MySQL：计算同一ID不同数据行的时间差问题

我来帮你搞定这个时间差计算的问题！你已经把数据按ID分组、按时间戳排好序了，接下来用窗口函数就能轻松算出同一ID内每行和上一行的时间差，比用rownum关联表要简单得多。

先看你的原始数据（假设表名为status_changes）：

ID status-before status-current DATE-timestamp
5 Un-Assigned Assigned 2013-12-17 14:14:04
5 Assigned Completed 2013-12-21 14:14:04
7 Un-Assigned Assigned 2014-01-02 14:14:04
7 Assigned Completed 2014-01-21 14:14:04
7 Completed Locked 2014-01-21 14:14:04
8 Un-Assigned Assigned 2014-12-21 14:14:04
8 Assigned Completed 2014-12-27 14:14:04

最优解决方案：使用LAG()窗口函数 #

这是最简洁的方法，直接在SELECT里就能计算时间差，不需要复杂的自连接：

SELECT 
    ID,
    status_before,
    status_current,
    DATE_timestamp,
    -- 计算当前行与同ID上一行的时间差（转换为小时，保留2位小数）
    ROUND((DATE_timestamp - LAG(DATE_timestamp) OVER (PARTITION BY ID ORDER BY DATE_timestamp)) * 24, 2) AS "Time Diff(Hours)"
FROM status_changes
ORDER BY ID, DATE_timestamp;

代码解释： #

• LAG(DATE_timestamp) OVER (PARTITION BY ID ORDER BY DATE_timestamp)：这个窗口函数会在每个ID分组内，按时间戳排序，自动获取当前行的「上一行时间戳」。
• Oracle中两个日期直接相减得到的是天数，乘以24就转换成小时，用ROUND()保留两位小数让结果更整洁。
• 每个ID的第一行因为没有上一行，结果会是NULL，如果想显示0或者其他默认值，用NVL()包裹即可：NVL(ROUND(...), 0)。

备选方案：用ROW_NUMBER()+自连接（兼容旧版本SQL） #

如果你的数据库不支持窗口函数（比如老版本Oracle），可以用行号+自连接的方式实现，原理是先给每个ID内的行按时间戳编号，再把当前行和行号-1的行关联：

WITH ranked_data AS (
    SELECT 
        ID,
        status_before,
        status_current,
        DATE_timestamp,
        -- 给每个ID内的行按时间戳排序编号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE_timestamp) AS rn
    FROM status_changes
)
SELECT 
    r1.ID,
    r1.status_before,
    r1.status_current,
    r1.DATE_timestamp,
    ROUND((r1.DATE_timestamp - r2.DATE_timestamp) * 24, 2) AS "Time Diff(Hours)"
FROM ranked_data r1
LEFT JOIN ranked_data r2 
    ON r1.ID = r2.ID AND r1.rn = r2.rn + 1
ORDER BY r1.ID, r1.rn;

实际运行结果示例 #

对应你的数据，运行后会得到这样的输出：

ID status-before status-current DATE-timestamp       Time Diff(Hours)
5 Un-Assigned   Assigned        2013-12-17 14:14:04  NULL
5 Assigned      Completed       2013-12-21 14:14:04  96.00
7 Un-Assigned   Assigned        2014-01-02 14:14:04  NULL
7 Assigned      Completed       2014-01-21 14:14:04  456.00
7 Completed     Locked          2014-01-21 14:14:04  0.00
8 Un-Assigned   Assigned        2014-12-21 14:14:04  NULL
8 Assigned      Completed       2014-12-27 14:14:04  144.00

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