我想在我的查詢中創建一個新列，它考慮到當前行的差異datetime - previous datetime。此列可以是一個計數器，如果差值 <-100，則保持為 1，但一旦差值 > -100，則該列為 0。
理想情況下，我只想拉入最后一條 0 記錄之后的行。
我的查詢：
with products as (
select * from (
select distinct
ID,
UnixDateTime,
OrderNumber,
to_date('1970-01-01','YYYY-MM-DD') numtodsinterval(UnixDateTime,'SECOND') 1/24 as "Date_Time"
from DB
where
(date '1970-01-01' UnixDateTime * interval '1' second) interval '1' hour
> sysdate - interval '2' day
)
),
prod_prev AS (
SELECT p.*,
lag("Date_Time")over(order by "Date_Time" ASC) as Previous_Time,
lag(UnixDateTime)over(order by "Date_Time" ASC) as UnixDateTime_Previous_Time "Date_Time") - "Date_Time" AS diff
FROM products p
),
run_sum AS (
SELECT p.*, "Date_Time"-Previous_Time as "Diff", UnixDateTime_Previous_Time-UnixDateTime AS "UnixDateTime_Diff"
FROM prod_prev p
)
SELECT * FROM run_sum
ORDER By UnixDateTime, "Date_Time" DESC
我的上述查詢的查詢結果：
| ID | Unix日期時間 | 訂單號 | 約會時間 | Previous_Time | 差異 | UnixDateTime_Diff |
|---|---|---|---|---|---|---|
| 1 | 1662615688 | 100 | 2022 年 9 月 8 日 06:41:28 | （無效的） | （無效的） | （無效的） |
| 2 | 1662615752 | 100 | 2022 年 9 月 8 日 06:42:32 | 2022 年 9 月 8 日 06:41:28 | 0.00074 | -64 |
| 3 | 1662615765 | 100 | 2022 年 9 月 8 日 06:42:45 | 008-SEP-2022 06:42:32 | 0.000150 | -13 |
| 4 | 1662615859 | 100 | 2022 年 9 月 8 日 06:44:19 | 2022 年 9 月 8 日 06:42:45 | 0.001088 | -128 |
| 5 | 1662615987 | 100 | 2022 年 9 月 8 日 06:46:27 | 2022 年 9 月 8 日 06:44:19 | 0.00148 | -44 |
| 6 | 1662616031 | 100 | 2022 年 9 月 8 日 06:47:11 | 2022 年 9 月 8 日 06:46:27 | 0.00051 | -36 |
如果 UnixDateTime_Diff < -100 和 0 如果 >-100 則計數器是下面的示例應該是 1 如果我只能在最近的 0 記錄之后拉入記錄。
uj5u.com熱心網友回復：
你用：
lag("Date_Time")over(order by "Date_Time" DESC)
DESC并在值以結束順序排序時獲取先前的值；這將獲得先前的較高值。如果您想要以前的較低值，請使用：
lag("Date_Time") over (order by "Date_Time" ASC)
或者
lead("Date_Time") over (order by "Date_Time" DESC)
如果要執行逐行處理，那么從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE：
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 |
|---|---|---|---|---|---|---|
| 1 | 1662615688 | 100 | 2022-09-08 05:41:28.000000000 UTC | 1662615752 | 64 | 1 |
| 2 | 1662615752 | 100 | 2022-09-08 05:42:32.000000000 UTC | 1662615765 | 13 | 1 |
| 3 | 1662615765 | 100 | 2022-09-08 05:42:45.000000000 UTC | 1662615859 | 94 | 1 |
| 4 | 1662615859 | 100 | 2022-09-08 05:44:19.000000000 UTC | 1662615987 | 128 | 0 |
| 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/506878.html