如何使用CTE獲取準確的行作為結果集？

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: SQL Developer(18c)

這是其中的資料集

1. 我需要根據status = 'DOWN'我將獲得 3 行作為我的主資料進行過濾。
2. 對于上述 3 行，我需要檢查同一個表test_tab，即使用過濾器status = 'UP'并找到剛好大于狀態的日志時間的日志時間DOWN

我的嘗試：

WITH a AS(
SELECT  col_name, log_time AS start_time, status
  FROM  test_tab
WHERE status = 'DOWN'),
b AS(
SELECT col_name,log_time AS end_time, status
FROM test_tab
WHERE status = 'UP')
SELECT a.col_name, a.start_time,a.status,
b.end_time,b.status
FROM a
JOIN b ON(a.col_name = b.col_name)
WHERE a.start_time < b.end_time;

但是從上面的查詢中，我得到了重復的記錄，col_name = 'Commerce'并在我的結果中給出了 4 行，但理想情況下，它應該只有 3 行，基于過濾器 DOWN。

預期輸出：

Col_name    start_time                      status      end_time                  status
Engineering 09-06-22 8:13:16.366000000 PM   DOWN    09-06-22 8:28:16.366000000 PM   UP
Commerce    07-06-22 4:59:16.366000000 PM   DOWN    07-06-22 6:34:16.366000000 PM   UP
Commerce    07-06-22 6:49:16.366000000 PM   DOWN    07-06-22 7:04:16.366000000 PM   UP

uj5u.com熱心網友回復：

您做對了所有事情，但您需要決定第二張表的兩個結果中的哪一個要在結果中顯示？當您嘗試僅根據名稱連接兩個表時，您知道結果中會有很多資料嗎（檢查我的演示中的第三個查詢）？然后您添加一個 where 子句并進一步過濾該資料。但是對于 cte a 中的一個資料與 cte b 中的資料配對兩次，所以在我的提議中，我決定選擇最小的一個。這能滿足您的需要嗎？

WITH a AS(
SELECT  col_name, log_time AS start_time, status
  FROM  test_tab
WHERE status = 'DOWN'),
b AS(
SELECT col_name,log_time AS end_time, status
FROM test_tab
WHERE status = 'UP')
SELECT a.col_name
       , a.start_time
       , a.status
       , MIN(b.end_time)
       , b.status
FROM a
LEFT JOIN b ON a.col_name = b.col_name
WHERE a.start_time < b.end_time
GROUP BY a.col_name
       , a.start_time
       , a.status
       , b.status ;

這是一個演示

uj5u.com熱心網友回復：

這是一個選項，它執行test_tab表的自連接并使用子查詢獲取高于其“DOWN”時間的第一個“UP”時間。

SQL> select d.col_name, d.log_time start_time, d.status,
  2                     u.log_time end_time  , u.status
  3  from test_tab d join test_tab u on u.col_name = d.col_name
  4  where d.status = 'DOWN'
  5    and u.status = 'UP'
  6    and u.log_time = (select min(u1.log_time)
  7                      from test_tab u1
  8                      where u1.col_name = u.col_name
  9                        and u1.status = u.status
 10                        and u1.log_time > d.log_time
 11                     );

COL_NAME    START_TIME                     STATUS END_TIME                       STATUS
----------- ------------------------------ ------ ------------------------------ ------
Engineering 09-06-22 08:13:16:366000000 pm DOWN   09-06-22 08:28:16:366000000 pm UP
Commerce    07-06-22 04:59:16:366000000 pm DOWN   07-06-22 06:34:16:366000000 pm UP
Commerce    07-06-22 06:49:16:366000000 pm DOWN   07-06-22 07:04:16:366000000 pm UP

SQL>

uj5u.com熱心網友回復：

從 Oracle 12 開始，這應該使用MATCH_RECOGNIZE執行逐行處理（并且不需要自聯接）來完成。

如果你想要一個DOWN然后是緊隨其后的UP然后：

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 (down up)
  DEFINE
    down AS status = 'DOWN',
    up   AS status = 'UP'
)

如果你想要一個DOWN然后是最早的，UP那么：

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
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (down other*? up)
  DEFINE
    down AS status = 'DOWN',
    up   AS status = 'UP'
)

如果你想這樣做，MATCH_RECOGNIZE那么你可以使用視窗分析函式（也不需要自連接）：

SELECT col_name,
       log_time AS start_time,
       end_time
FROM   (
  SELECT t.*,
         MIN(CASE status WHEN 'UP' THEN log_time END) OVER (
           PARTITION BY col_name
           ORDER BY log_time
           ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
         ) AS end_time
  FROM   test_tab t
)
WHERE  status = 'DOWN'

其中，對于您的示例資料，所有輸出：

COL_NAME	開始時間	時間結束
商業	2022-06-07 16:59:16.366000	2022-06-07 18:34:16.366000
商業	2022-06-07 18:49:16.366000	2022-06-07 19:04:16.366000
工程	2022-06-09 20:13:16.366000	2022-06-09 20:28:16.366000

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：SvelteTypeScript缺少以下屬性組件

下一篇：根據日期欄位獲取活動行