計算一個id的每個進出持續時間

我有如下資料

預期輸出：

where dateis of DATEand timeisVARCHAR型別列。我想計算一個 ID 的所有輸入/輸出持續時間。我無法思考邏輯如何為此獲得所有輸入/輸出持續時間的持續時間。

任何人都可以提出任何想法嗎？

uj5u.com熱心網友回復：

這是一種典型gaps-and-islands的問題，解決的一個選擇是有條件地使用SUM() OVER ()分析函式，例如

WITH t1 AS
(
 SELECT t.*,
        TO_TIMESTAMP(TO_CHAR("date",'yyyy-mm-dd ')||time,'yyyy-mm-dd hh24:mi:ss') AS dt
   FROM t --> your table
), t2 AS
(   
  SELECT t1.*, 
         SUM(CASE WHEN type = 'in' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY dt) AS rn_in,
         SUM(CASE WHEN type = 'out' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY dt) AS rn_out,
         CASE WHEN type = 'in' THEN dt END AS "in",
         CASE WHEN type = 'out' THEN dt END AS "out"  
    FROM t1
) 
SELECT id, MIN("in") AS "start", MAX("out") AS "end", MAX("out")-MIN("in") AS "totaltime"
  FROM t2
 WHERE rn_in >= 1 
 GROUP BY id, rn_in
 ORDER BY "start"

where"date"被認為是date型別列，而不是注釋中的普通字串。

Demo

uj5u.com熱心網友回復：

通過一條評論，我假設每個“輸入”記錄在同一天都有匹配的“輸出”記錄，所以我通過在一天中獲取下一個“輸入”并在兩者之間找到最大“輸出”來解決它：

with t1 as 
(select ID, "date" , "time" as "start", LEAD("time",1,'24:00') over(partition by id, "date" order by  "time") as "next"
 from test 
 where "type" = 'in'),
t3 as
(select ID,  "start", (select max("time") from test t2
                        where t1.id = t2.id and t1."date" = t2."date" 
                          and t2."type" = 'out' and t2."time" between t1."start"
                          and t1."next" ) as "end"
from t1)
select ID, "start", "end", (case when "end" is null then null else (TO_DSINTERVAL('0 '||"end"||':00') - TO_DSINTERVAL('0 '||"start"||':00'))  end) as totaltime
from t3 

uj5u.com熱心網友回復：

在 Oracle 中，DATE資料型別是由 7 個位元組組成的二進制資料型別，分別表示：世紀、世紀年、月、日、小時、分鐘和秒，并且它總是具有這些組件。鑒于此，擁有單獨的日期和時間列是沒有意義的，您可以將兩列合并為一列并擁有示例資料：

CREATE TABLE table_name (Id, datetime, type) AS
SELECT 1, DATE '2022-01-01'   INTERVAL '08:00' HOUR TO MINUTE, 'in'  FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-01'   INTERVAL '11:30' HOUR TO MINUTE, 'out' FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-01'   INTERVAL '11:35' HOUR TO MINUTE, 'out' FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-01'   INTERVAL '12:45' HOUR TO MINUTE, 'in'  FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-01'   INTERVAL '17:30' HOUR TO MINUTE, 'out' FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-01'   INTERVAL '01:00' HOUR TO MINUTE, 'out' FROM DUAL;

注意：創建示例資料的另一個選項是使用TO_DATE('2022-01-01 12:45', 'YYYY-MM-DD HH24:MI').

然后，從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE對資料執行逐行操作：

SELECT m.*,
       (end_dt - start_dt) DAY TO SECOND AS duration
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY datetime
  MEASURES
    FIRST(type)     AS type,
    FIRST(datetime) AS start_dt,
    NEXT(datetime)  AS end_dt
  PATTERN (same_type )
  DEFINE same_type AS FIRST(type) = type
) m

哪個輸出：

ID	型別	START_DT	END_DT	期間
1	出去	2022-01-01 01:00:00	2022-01-01 08:00:00	00 07:00:00.000000
1	在	2022-01-01 08:00:00	2022-01-01 11:30:00	00 03:30:00.000000
1	出去	2022-01-01 11:30:00	2022-01-01 12:45:00	00 01:15:00.000000
1	在	2022-01-01 12:45:00	2022-01-01 17:30:00	00 04:45:00.000000
1	出去	2022-01-01 17:30:00	空值	空值

如果您確實保留了單獨的日期和時間列（您不應該），那么您可以在使用之前將它們組合成一個列MATCH_RECOGNIZE：

SELECT m.*,
       (end_dt - start_dt) DAY TO SECOND AS duration
FROM   (
  SELECT id,
         TO_DATE(TO_CHAR("DATE", 'YYYY-MM-DD') || time, 'YYYY-MM-DDHH24:MI') AS datetime,
         type
  FROM   table_name
)
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY datetime
  MEASURES
    FIRST(type)     AS type,
    FIRST(datetime) AS start_dt,
    NEXT(datetime)  AS end_dt
  PATTERN (same_type )
  DEFINE same_type AS FIRST(type) = type
) m

db<>在這里擺弄

標籤：sql 甲骨文 差距和岛屿

上一篇：OracleREGEXP_REPLACE同時用于空格和“%”

下一篇：無法在高山容器中運行Oraclesqlldr