Oracle SQL按组查找日期差之和

使用 LAG 或 LEAD 分析函数以获取下一个 END 事件时间: SQL Fiddle

Oracle 11g R2架构设置 :

CREATE TABLE event ( id, timestamp, event ) AS
  SELECT 9000, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03/10 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-10 11:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03/20 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03-20 10:05:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03/25 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-25 10:15:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03/26 12:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2017-03-26 14:30:27','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2017-04-05 15:02:56','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL;

查询1 :

SELECT id,
       MIN( timestamp ) AS start_ts,
       MAX( end_time  ) AS end_ts,
       SUM( end_time - timestamp ) AS duration
FROM   (
  SELECT id,
         timestamp,
         event,
         LEAD( CASE event WHEN 'END' THEN timestamp END )
           OVER ( PARTITION BY id ORDER BY timestamp ) AS end_time
  FROM   event
)
WHERE  event = 'Start'
GROUP BY id
ORDER BY id

Results :

|   ID |             START_TS |               END_TS |           DURATION |
|------|----------------------|----------------------|--------------------|
| 9000 | 2018-03-01T09:00:00Z | 2018-03-25T09:00:00Z | 13.996527777777779 |
| 9001 | 2018-03-10T11:00:00Z | 2018-03-26T12:00:00Z |           11.03125 |
| 9002 | 2017-03-26T14:30:27Z | 2017-04-05T15:02:56Z |  10.02255787037037 |

我分两步解决了这个问题。首先,我在相同的时间间隔内匹配记录,然后总结它们的持续时间。

http://sqlfiddle.com/#!4/73f48/83

   SELECT
    Id,
    round(SUM(duration))
FROM
    (
        SELECT
            t.id,
            MIN (t2. TIMESTAMP) - t. TIMESTAMP AS duration
        FROM
            event t,
            event t2
        WHERE
            t.Id = t2.Id
        AND t2.Event = 'END'
        AND t.Event = 'Start'
        AND t2. TIMESTAMP > t. TIMESTAMP
        GROUP BY
            t. TIMESTAMP,
            t.Id
    )
GROUP BY
    Id

select 
id, round(sum(end_timestamp - start_timestamp),3) DURATION
from (
select 
       t.id, 
       t.timestamp START_TIMESTAMP,
       case when LEAD(t.event,1) OVER (partition by id order by timestamp, event desc) = 'END' 
            then LEAD(t.timestamp,1) OVER (partition by id order by timestamp, event desc)
       else null end as END_TIMESTAMP
from event t
)tt
where end_timestamp is not null
group by id

问题的解决方案:

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/20 10:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL UNION ALL
SELECT 9000 AS ID, TO_DATE('2018-03-20 10:05:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL UNION ALL
SELECT 9000 AS ID, TO_DATE('2018-03/25 09: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-25 10:15:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL UNION ALL
SELECT 9001 AS ID, TO_DATE('2018-03/26 12:00:00','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL UNION ALL
SELECT 9002 AS ID, TO_DATE('2017-03-26 14:30:27','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'Start' AS EVENT FROM DUAL UNION ALL
SELECT 9002 AS ID, TO_DATE('2017-04-05 15:02:56','RRRR-MM-DD HH24:MI:SS') AS TIMESTAMP, 'END' AS EVENT FROM DUAL
)
,rn_event AS 
(
  select event.*,ROW_NUMBER() OVER (Partition BY ID ORDER BY TimeSTAMP) AS rn from event
)
, diff_event AS 
(
SELECT e.ID, f.TIMESTAMP AS Start_time, e.timestamp AS End_Time, e.TIMESTAMP - f.timestamp AS duration
FROM rn_event e
INNER JOIN rn_event f 
ON f.id = e.id AND f.EVENT = 'Start' AND f.rn = e.rn - 1
)
SELECT ID,MIN(Start_Time) START_TS, MAX(END_TIME) END_TS, ROUND(SUM(Duration)) AS Duration
FROM diff_event
GROUP BY ID;

输出:

ID      START_TS                END_TS                 DURATION
9000    2018-03-01T09:00:00Z    2018-03-25T09:00:00Z    14
9001    2018-03-10T11:00:00Z    2018-03-26T12:00:00Z    11
9002    2017-03-26T14:30:27Z    2017-04-05T15:02:56Z    10

上述查询的演示:

http://sqlfiddle.com/#!4/73f48/87