CREATE TABLE test_tab (
col_name VARCHAR2(20),
log_time TIMESTAMP(6),
status VARCHAR2(20)
);
INSERT INTO test_tab VALUES('Engineering','08-06-22 08:09:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:28:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:13:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 4:59:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:34:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:49:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','01-06-22 2:15:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 07:04:16.366000000 PM','UP');
COMMIT;
Tool used: Oracle Developer(18c)
我有一張表test_tab，其中有多個col_name值，例如“工程”、“商務”等，僅出于測驗目的，我給出了兩個列名。我需要找出特定的開始日志時間和結束日志時間col_name
Start_time 邏輯：特別是col_name“Commerce”，我需要檢查狀態為 DOWN 的最短日志時間。
End_time 邏輯：對于同樣col_name的“商務”，我需要檢查start_time從上述邏輯派生的結果，看看哪個比狀態 UP 中的時間大。那將是我的結束時間。
我的嘗試（僅適用于“商業”）：
WITH a AS(
SELECT col_name,MIN(log_time)start_time
FROM test_tab WHERE status = 'DOWN'
GROUP BY col_name
),
b AS(
SELECT col_name,log_time end_time
FROM( SELECT col_name,log_time,
dense_rank() over (order by log_time asc)rnk
FROM test_tab WHERE status = 'UP')
WHERE rnk = 2
)
SELECT a.col_name,a.start_time,b.end_time FROM a
JOIN b ON(a.col_name = b.col_name);
但上述解決方案并未給出“工程”的結果。它只是為“Commerce”獲取詳細資訊
預期輸出：
Col_name start_time end_time
Commerce 07-06-22 4:59:16.366000000 PM 07-06-22 6:34:16.366000000 PM
Engineering 09-06-22 08:13:16.366000000 PM 09-06-22 08:28:16.366000000 PM
uj5u.com熱心網友回復：
您可以使用每個磁區的分析函式找到最小“停機”時間col_name
，然后過濾以僅具有后續“啟動”時間并找到其中的最小值：
SELECT col_name,
MIN(min_down) AS start_time,
MIN(log_time) AS end_time
FROM (
SELECT t.*,
MIN(CASE status WHEN 'DOWN' THEN log_time END)
OVER (PARTITION BY col_name) AS min_down
FROM test_tab t
)
WHERE log_time >= min_down
AND status = 'UP'
GROUP BY col_name
其中，對于樣本資料，輸出：
COL_NAME 開始時間 時間結束 商業 07-JUN-22 16.59.16.366000 07-JUN-22 18.34.16.366000 工程 09-JUN-22 20.13.16.366000 09-JUN-22 20.28.16.366000
db<>在這里擺弄
uj5u.com熱心網友回復：
從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE來執行逐行處理：
SELECT *
FROM test_tab
MATCH_RECOGNIZE(
PARTITION BY col_name
ORDER BY log_time
MEASURES
FIRST(down.log_time) AS start_time,
FIRST(up.log_time) AS end_time
PATTERN (^ other*? down up)
DEFINE
down AS status = 'DOWN',
up AS status = 'UP'
)
其中，對于樣本資料，輸出：
COL_NAME 開始時間 時間結束 商業 07-JUN-22 16.59.16.366000 07-JUN-22 18.34.16.366000 工程 09-JUN-22 20.13.16.366000 09-JUN-22 20.28.16.366000
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/489469.html