如何基于分组变化计算同一列日期间隔及END与下一个Start时差

针对你的需求，我们可以通过两种高效的方式在Oracle 11g R2中实现计算：

方法1：使用OUTER APPLY关联子查询 #

这种方法直观易懂，直接为每条END记录匹配后续最早的Start记录：

SELECT
    end_log.id AS end_record_id,
    end_log.DateTime AS end_time,
    start_log.id AS next_start_record_id,
    start_log.DateTime AS next_start_time,
    -- 计算时间差（单位：天）
    start_log.DateTime - end_log.DateTime AS time_diff_days,
    -- 转换为小时
    (start_log.DateTime - end_log.DateTime) * 24 AS time_diff_hours,
    -- 转换为分钟
    (start_log.DateTime - end_log.DateTime) * 24 * 60 AS time_diff_minutes
FROM
    Logs end_log
OUTER APPLY (
    -- 子查询获取当前END之后的第一条Start记录
    SELECT *
    FROM (
        SELECT id, DateTime
        FROM Logs
        WHERE Type = 'Start'
          AND DateTime > end_log.DateTime
        ORDER BY DateTime ASC
    )
    WHERE ROWNUM = 1
) start_log
WHERE
    end_log.Type = 'END'
ORDER BY
    end_log.DateTime;

原理说明 #

• 主查询筛选所有Type='END'的记录
• OUTER APPLY会为每条END记录执行一次子查询，子查询按时间升序筛选出该END之后的第一条Start记录（用ROWNUM=1限制只取第一条）
• 最后计算两个时间的差值，Oracle中日期直接相减得到的是天数，乘以对应系数可转换为小时/分钟

方法2：使用窗口函数LAST_VALUE #

这种方法利用窗口函数一次性计算出每条END对应的下一个Start时间，性能更优（尤其是数据量大时）：

WITH ordered_logs AS (
    SELECT
        id,
        DateTime,
        Type,
        -- 从当前行开始向后查找第一个非NULL的Start时间
        LAST_VALUE(CASE WHEN Type = 'Start' THEN DateTime END IGNORE NULLS) OVER (
            ORDER BY DateTime ASC
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_start_time
    FROM Logs
)
SELECT
    id AS end_record_id,
    DateTime AS end_time,
    next_start_time,
    next_start_time - DateTime AS time_diff_days,
    (next_start_time - DateTime) * 24 AS time_diff_hours
FROM ordered_logs
WHERE Type = 'END'
ORDER BY DateTime;

原理说明 #

• 首先通过CTEordered_logs对所有记录按时间排序，并用LAST_VALUE结合IGNORE NULLS，为每条记录标记出它之后第一个Start的时间（非END记录的这个值可以忽略）
• 最后筛选出所有END记录，计算时间差即可

示例输出（基于你提供的测试数据） #

假设你的测试数据完整后包含：

ID   DateTime              Type
9000 2018-03-01 09:00:00  Start
9000 2018-03-10 10:00:00  END
9001 2018-03-11 11:00:00  Start

执行上述SQL后会得到类似结果：

可以看到，这条END记录和下一条Start记录的时间差是1天1小时，也就是25小时。

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