如何修改Oracle SQL查询，提取Within_100字段第二个最近0值后的记录

要提取within_100字段中第二个最近的0值之后的记录，你可以在原查询外层添加两层CTE，通过标记0值记录并筛选阈值的方式实现，修改后的完整查询如下：

WITH base_data AS (
    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
),
zero_records AS (
    SELECT unixdatetime,
           ROW_NUMBER() OVER (ORDER BY unixdatetime DESC) AS rn
    FROM base_data
    WHERE within_100 = 0
)
SELECT bd.*
FROM base_data bd
CROSS JOIN (SELECT unixdatetime FROM zero_records WHERE rn = 2) z
WHERE bd.unixdatetime > z.unixdatetime;

逻辑说明 #

1. base_data：完全保留原查询的所有结果，确保原有计算逻辑不变。
2. zero_records：筛选出所有within_100=0的记录，按时间倒序（最近的0值在前）给每条0值记录分配序号rn，其中rn=2对应的就是第二个最近的0值记录的时间戳。
3. 最后一步：筛选出原数据中时间戳晚于第二个最近0值时间戳的所有记录，即你需要的ID5和6对应的内容。

内容的提问来源于stack exchange，提问作者Jdbfkfo