OracleSQL在列中的第二個0之后拉入記錄-有解無憂

如何編輯我的查詢以僅提取0“Within_100”欄位中第二個最新值之后的記錄

我的查詢：

SELECT id,
       unixdatetime,
       ordernumber,
       date_time,
       next_unixdatetime,
       next_unixdatetime - unixdatetime AS diff,
       CASE cls
       WHEN 'WITHIN_100' THEN 1
       ELSE 0
       END AS within_100
from   (
  select distinct 
         ID,
         UnixDateTime,
         OrderNumber,
         TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           AS Date_Time
  from   DB
  where  TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           > SYSTIMESTAMP - INTERVAL '2' DAY
)
MATCH_RECOGNIZE(
  ORDER BY unixdatetime
  MEASURES
    NEXT(unixdatetime) AS next_unixdatetime,
    classifier() AS cls
  ALL ROWS PER MATCH
  PATTERN (within_100* any_row)
  DEFINE
    within_100 AS NEXT(unixdatetime) < unixdatetime   100
) m

輸出結果：

ID	UNIX日期時間	訂單號	約會時間	NEXT_UNIXDATETIME	差異	WITHIN_100
1	1662615688	100	2022-09-08 05:41:28	1662615752	64	1
2	1662615752	100	2022-09-08 05:42:32	1662615765	13	1
3	1662615765	100	2022-09-08 05:42:45	1662615859	94	1
4	1662615859	100	2022-09-08 05:44:19	1662615987	128	0
5	1662615987	100	2022-09-08 05:46:27	1662616031	44	1
6	1662616031	100	2022-09-08 05:47:11	無效的	無效的	0

所以我只想提取 ID 記錄 5 和 6 謝謝

uj5u.com熱心網友回復：

添加$到模式以僅匹配最近的模式而不是全域匹配：

SELECT id,
       unixdatetime,
       ordernumber,
       date_time,
       next_unixdatetime,
       next_unixdatetime - unixdatetime AS diff,
       CASE cls
       WHEN 'WITHIN_100' THEN 1
       ELSE 0
       END AS within_100
from   (
  select distinct 
         ID,
         UnixDateTime,
         OrderNumber,
         TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           AS Date_Time
  from   DB
  where  TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           > SYSTIMESTAMP - INTERVAL '2' DAY
)
MATCH_RECOGNIZE(
  ORDER BY unixdatetime
  MEASURES
    NEXT(unixdatetime) AS next_unixdatetime,
    classifier() AS cls
  ALL ROWS PER MATCH
  PATTERN (within_100* any_row $)
  DEFINE
    within_100 AS NEXT(unixdatetime) < unixdatetime   100
) m

其中，對于樣本資料：

CREATE TABLE db (ID, UnixDateTime, OrderNumber) AS
SELECT 1, 1662615688, 100 FROM DUAL UNION ALL
SELECT 2, 1662615752, 100 FROM DUAL UNION ALL
SELECT 3, 1662615765, 100 FROM DUAL UNION ALL
SELECT 4, 1662615859, 100 FROM DUAL UNION ALL
SELECT 5, 1662615987, 100 FROM DUAL UNION ALL
SELECT 6, 1662616031, 100 FROM DUAL;

輸出：

ID	UNIX日期時間	訂單號	約會時間	NEXT_UNIXDATETIME	差異	WITHIN_100
5	1662615987	100	2022-09-08 05:46:27.000000000 UTC	1662616031	44	1
6	1662616031	100	2022-09-08 05:47:11.000000000 UTC	無效的	無效的	0

小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/506869.html

標籤：sql 甲骨文

上一篇：嘗試對查詢進行分組以按小時顯示日事件

下一篇：根據if陳述句僅選擇前n行