sql - 如何将一行两个日期时间列拆分为多行并按日期分组

Question

样本数据

如果这里的数据如下:

ID,EQP,PREVIOUSTIME,TRANSACTIONTIME,OLDSTATE,NEWSTATE
1,HT-001,2020/01/01 12:00:00,2020/01/01 13:00:00,IDLE,RUN
2,HT-001,2020/01/01 13:00:00,2020/01/05 15:00:00,RUN,IDLE
3,HT-001,2020/01/05 15:00:00,2020/01/05 16:00:00,IDLE,RUN
4,HT-001,2020/01/05 16:00:00,2020/01/05 18:00:00,RUN,IDLE

5,HT-002,2020/01/01 12:00:00,2020/01/01 13:00:00,IDLE,RUN
6,HT-002,2020/01/01 13:00:00,2020/01/05 15:00:00,RUN,IDLE
7,HT-002,2020/01/05 15:00:00,2020/01/05 16:00:00,IDLE,RUN
8,HT-002,2020/01/05 16:00:00,2020/01/05 18:00:00,RUN,IDLE

我期待什么

查询 select * from SomeView或 select * from SomeFunction(datetime_of_now)或存储程序
得到以下结果(现在的日期时间是 2020/01/06 12:00:00)

Date        ,EQP    ,STATE  ,COST_TIME(Hour)
2020/01/01  ,HT-001 ,IDLE   ,1
2020/01/01  ,HT-001 ,RUN    ,11
2020/01/02  ,HT-001 ,RUN    ,24
2020/01/03  ,HT-001 ,RUN    ,24
2020/01/04  ,HT-001 ,RUN    ,24
2020/01/05  ,HT-001 ,IDLE   ,7 (1+6)
2020/01/05  ,HT-001 ,RUN    ,2 (2+15)
2020/01/06  ,HT-001 ,IDLE   ,12

2020/01/01  ,HT-002 ,IDLE   ,1
2020/01/01  ,HT-002 ,RUN    ,11
2020/01/02  ,HT-002 ,RUN    ,24
2020/01/03  ,HT-002 ,RUN    ,24
2020/01/04  ,HT-002 ,RUN    ,24
2020/01/05  ,HT-002 ,IDLE   ,7 (1+6)
2020/01/05  ,HT-002 ,RUN    ,2 (2+15)
2020/01/06  ,HT-002 ,IDLE   ,12

测试演示链接

Oracle 11g Release 2 | db<>fiddle

我试图遇到的困难

我不知道将一天拆分为多个日期行。
例如:拆分一行前一次~交易时间是 01/01 ~ 01/05 到至少 5 行

将最后一个拆分为现在的日期时间

Answer 1

使用递归子查询分解子句生成日期边界并计算持续时间然后聚合:

Oracle 设置 :

CREATE TABLE table1 ( ID,EQP,PREVIOUSTIME,TRANSACTIONTIME,OLDSTATE,NEWSTATE ) AS
SELECT 1,'HT-001',DATE '2020-01-01' + INTERVAL '12:00:00' HOUR TO SECOND,DATE '2020-01-01' + INTERVAL '13:00:00' HOUR TO SECOND,'IDLE','RUN' FROM DUAL UNION ALL
SELECT 2,'HT-001',DATE '2020-01-01' + INTERVAL '13:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '15:00:00' HOUR TO SECOND,'RUN','IDLE' FROM DUAL UNION ALL
SELECT 3,'HT-001',DATE '2020-01-05' + INTERVAL '15:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '16:00:00' HOUR TO SECOND,'IDLE','RUN' FROM DUAL UNION ALL
SELECT 4,'HT-001',DATE '2020-01-05' + INTERVAL '16:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '18:00:00' HOUR TO SECOND,'RUN','IDLE' FROM DUAL UNION ALL
SELECT 5,'HT-002',DATE '2020-01-01' + INTERVAL '12:00:00' HOUR TO SECOND,DATE '2020-01-01' + INTERVAL '13:00:00' HOUR TO SECOND,'IDLE','RUN' FROM DUAL UNION ALL
SELECT 6,'HT-002',DATE '2020-01-01' + INTERVAL '13:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '15:00:00' HOUR TO SECOND,'RUN','IDLE' FROM DUAL UNION ALL
SELECT 7,'HT-002',DATE '2020-01-05' + INTERVAL '15:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '16:00:00' HOUR TO SECOND,'IDLE','RUN' FROM DUAL UNION ALL
SELECT 8,'HT-002',DATE '2020-01-05' + INTERVAL '16:00:00' HOUR TO SECOND,DATE '2020-01-05' + INTERVAL '18:00:00' HOUR TO SECOND,'RUN','IDLE' FROM DUAL UNION ALL
SELECT 9,'HT-002',DATE '2020-01-05' + INTERVAL '18:00:00' HOUR TO SECOND,DATE '2020-01-06' + INTERVAL '00:00:00' HOUR TO SECOND,'IDLE','IDLE' FROM DUAL;

查询 :

WITH days ( eqp, dt, state, next_dt, max_dt ) AS (
  SELECT eqp,
         previoustime,
         oldstate,
         LEAST( TRUNC( previoustime ) + 1, transactiontime ),
         transactiontime
  FROM   (
    SELECT eqp,
           previoustime,
           transactiontime,
           oldstate,
           newstate
    FROM   table1
    UNION ALL
    SELECT eqp,
           MAX( transactiontime ),
           TRUNC( MAX( transactiontime ) + INTERVAL '23:59:59' HOUR TO SECOND ),
           MAX( newstate ) KEEP ( DENSE_RANK LAST ORDER BY transactiontime ),
           NULL
    FROM   table1
    GROUP BY eqp
  )
UNION ALL
  SELECT eqp,
         next_dt,
         state,
         LEAST( next_dt + 1, max_dt ),
         max_dt
  FROM   days
  WHERE  next_dt < max_dt
)
SELECT TRUNC( dt ) AS dt,
       eqp,
       state,
       SUM( ROUND( ( next_dt - dt ) * 24 ) ) AS cost_time
FROM   days
GROUP BY eqp, TRUNC( dt ), state
HAVING SUM( ROUND( ( next_dt - dt ) * 24 ) ) > 0
ORDER BY eqp, dt, state;

输出 :

DT                  | EQP    | STATE | COST_TIME:------------------ | :----- | :---- | --------:2020-01-01 00:00:00 | HT-001 | IDLE  |         12020-01-01 00:00:00 | HT-001 | RUN   |        112020-01-02 00:00:00 | HT-001 | RUN   |        242020-01-03 00:00:00 | HT-001 | RUN   |        242020-01-04 00:00:00 | HT-001 | RUN   |        242020-01-05 00:00:00 | HT-001 | IDLE  |         72020-01-05 00:00:00 | HT-001 | RUN   |        172020-01-01 00:00:00 | HT-002 | IDLE  |         12020-01-01 00:00:00 | HT-002 | RUN   |        112020-01-02 00:00:00 | HT-002 | RUN   |        242020-01-03 00:00:00 | HT-002 | RUN   |        242020-01-04 00:00:00 | HT-002 | RUN   |        242020-01-05 00:00:00 | HT-002 | IDLE  |         72020-01-05 00:00:00 | HT-002 | RUN   |        17

分贝<> fiddle here