使用Oracle SQL按组计算日期差总和，求分组总耗时

先把你提供的示例事件数据SQL补全并格式化好：

WITH event AS (
    SELECT 9000 AS ID, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9000 AS ID, TO_DATE('2018-03-10 10:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-10 11:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-15 14:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
)

接下来给你两种实用的计算方式，按需选择：

方式1：针对单组Start/End的简单计算 #

如果每个ID只有一对Start和End事件，直接用聚合函数匹配时间差即可：

WITH event AS (
    SELECT 9000 AS ID, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9000 AS ID, TO_DATE('2018-03-10 10:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-10 11:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-15 14:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
)
SELECT
    ID,
    -- 格式化显示耗时：X天X小时X分钟
    EXTRACT(DAY FROM (max_ts - min_ts)) || '天 ' ||
    EXTRACT(HOUR FROM (max_ts - min_ts)) || '小时 ' ||
    EXTRACT(MINUTE FROM (max_ts - min_ts)) || '分钟' AS total_duration,
    -- 或者直接计算总小时数（保留两位小数）
    ROUND((max_ts - min_ts)*24, 2) AS total_hours
FROM (
    SELECT
        ID,
        MAX(CASE WHEN EVENT = 'END' THEN TIMESTAMP END) AS max_ts,
        MIN(CASE WHEN EVENT = 'Start' THEN TIMESTAMP END) AS min_ts
    FROM event
    GROUP BY ID
)
WHERE max_ts IS NOT NULL AND min_ts IS NOT NULL;

方式2：支持多组Start/End的通用方案 #

如果每个ID可能有多轮Start-End循环，用窗口函数LEAD()关联每一对事件再汇总：

WITH event AS (
    SELECT 9000 AS ID, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9000 AS ID, TO_DATE('2018-03-10 10:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-10 11:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL
    UNION ALL
    SELECT 9001 AS ID, TO_DATE('2018-03-15 14:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
)
SELECT
    ID,
    -- 汇总所有轮次的总耗时（按小时计算）
    ROUND(SUM((end_ts - start_ts)*24), 2) AS total_hours,
    -- 或者格式化总时长
    SUM(EXTRACT(DAY FROM (end_ts - start_ts))) || '天 ' ||
    SUM(EXTRACT(HOUR FROM (end_ts - start_ts))) || '小时 ' ||
    SUM(EXTRACT(MINUTE FROM (end_ts - start_ts))) || '分钟' AS total_duration
FROM (
    SELECT
        ID,
        TIMESTAMP AS start_ts,
        -- 按ID分组、时间排序，获取当前Start对应的下一条End时间
        LEAD(TIMESTAMP) OVER (PARTITION BY ID ORDER BY TIMESTAMP) AS end_ts
    FROM event
    WHERE EVENT = 'Start'
)
WHERE end_ts IS NOT NULL -- 过滤没有对应End的Start事件
GROUP BY ID;

核心思路说明 #

• 单组场景：用MAX/MIN配合CASE分别提取每个ID的End和Start时间，直接计算差值。
• 多组场景：用LEAD()窗口函数，给每个Start事件匹配紧随其后的End事件，再逐组计算耗时后汇总。

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