代码之家 › 专栏 › 技术社区 › Ali Adnan

Oracle SQL按组查找日期差之和

oracle12c sql

Ali Adnan · 技术社区 · 8 年前

我试图通过在下面的查询中计算日期差来找到一个组消耗的总持续时间

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
)
select id, min(timestamp) as call_start_ts, max(timestamp) as call_end_ts,
   max(timestamp) - min(timestamp) as duration
from event t
group by id
order by 1;

我还配置了 SQLFiddle

请帮帮我

编辑

预期结果如下

4 回复 | 直到 8 年前

MT0 8 年前

使用 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 |

Pelin 8 年前

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

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

misza2000 8 年前

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

Nishant Gupta 8 年前

问题的解决方案:

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