SQL如何获取同NUMBER分组上一行数据计算时间间隔天数

原SQL问题说明 #

你写的SQL只做了同NUMBER字段的关联，没有限定关联的是同分组内时间早于当前记录的最新一条数据，会导致同一条当前记录关联上同分组的所有历史记录，出现重复或计算错误的问题。

最优实现方案（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、Oracle、SQL Server等） #

直接用窗口函数LAG()即可实现取同分组内上一条记录的字段值，逻辑简洁性能更好：

-- 示例为MySQL 8.0+语法，其他数据库仅需替换时间差计算函数即可
SELECT 
    ID,
    NUMBER,
    TIMESTAMP,
    CASE 
        WHEN prev_timestamp IS NULL THEN '0 Days'
        -- REPLACE作用是把小数点替换为逗号，匹配你示例的输出格式
        ELSE CONCAT(REPLACE(ROUND(TIMESTAMPDIFF(HOUR, prev_timestamp, TIMESTAMP)/24, 1), '.', ','), ' Days')
    END AS `RANGE`
FROM (
    SELECT 
        *,
        -- 按NUMBER分组，组内按时间升序排序，取上一条记录的时间
        LAG(TIMESTAMP) OVER (PARTITION BY NUMBER ORDER BY TIMESTAMP) AS prev_timestamp
    FROM my_table
) AS temp

如果你的TIMESTAMP字段是字符串存储而非 datetime 类型，需要先转成时间格式再计算，MySQL示例：
把LAG(TIMESTAMP)替换为LAG(STR_TO_DATE(TIMESTAMP, '%m/%d/%Y %H:%i:%s'))，TIMESTAMPDIFF参数里的TIMESTAMP也同步替换为STR_TO_DATE(TIMESTAMP, '%m/%d/%Y %H:%i:%s')即可。

旧版不支持窗口函数的数据库实现（如MySQL 5.x） #

可以用自关联+分组的方式实现：

SELECT 
    a.ID,
    a.NUMBER,
    a.TIMESTAMP,
    CASE 
        WHEN MAX(b.TIMESTAMP) IS NULL THEN '0 Days'
        ELSE CONCAT(REPLACE(ROUND(TIMESTAMPDIFF(HOUR, MAX(b.TIMESTAMP), a.TIMESTAMP)/24, 1), '.', ','), ' Days')
    END AS `RANGE`
FROM my_table a
-- 关联同NUMBER、时间早于当前记录的所有数据
LEFT JOIN my_table b ON a.NUMBER = b.NUMBER AND b.TIMESTAMP < a.TIMESTAMP
-- 分组后取最大的b.TIMESTAMP就是当前记录的上一条时间
GROUP BY a.ID, a.NUMBER, a.TIMESTAMP
ORDER BY a.ID

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