Oracle 18c筛选相邻间隔≤60分钟的最新订单数据

针对你的需求——保留每个Line最大Order_Number记录中，与前一条记录时间间隔≤60分钟的记录，且必须保留每条Line的最新记录——以下是基于递归CTE的优化方案：

完整SQL代码 #

WITH base_data AS (
    -- 保留原有逻辑：过滤每个Line的最大Order_Number的所有记录
    SELECT
        ID,
        Create_Date_Time_Unix_TimeStamp,
        Line,
        Order_Number,
        TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(Create_Date_Time_Unix_TimeStamp,'SECOND') + 1/24 AS Date_Time
    FROM (
        SELECT
            ID,
            Create_Date_Time_Unix_TimeStamp,
            Line,
            Order_Number,
            MAX(Order_Number) OVER (PARTITION BY Line) AS max_Order
        FROM ... -- 替换为你的原始表和JOIN逻辑
        WHERE ... -- 替换为你的原始WHERE条件
    )
    WHERE Order_Number = max_Order
),
ranked_data AS (
    -- 对每个Line的记录按时间降序排名，相同时间的记录排名相同
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY Line ORDER BY Date_Time DESC) AS rn
    FROM base_data
),
recursive_data AS (
    -- 递归起始点：每个Line的最新记录（排名第一的所有记录）
    SELECT * FROM ranked_data WHERE rn = 1
    UNION ALL
    -- 递归步骤：依次加入下一批时间的记录，仅保留与上一批间隔≤60分钟的
    SELECT rd_next.*
    FROM ranked_data rd_next
    JOIN recursive_data rd_current 
        ON rd_next.Line = rd_current.Line 
        AND rd_next.rn = rd_current.rn + 1
    -- 判断时间间隔≤60分钟（1/24天）
    WHERE rd_current.Date_Time - rd_next.Date_Time <= 1/24
)
-- 输出最终结果，按Line和时间降序排列
SELECT
    ID,
    Create_Date_Time_Unix_TimeStamp,
    Line,
    Order_Number,
    Date_Time
FROM recursive_data
ORDER BY Line, Date_Time DESC;

逻辑说明 #

1. base_data：复用你原有的查询逻辑，过滤出每个Line对应最大Order_Number的所有记录，同时转换Unix时间戳为Oracle DATE类型。
2. ranked_data：使用DENSE_RANK()对每个Line的记录按时间降序排名，相同时间的记录会获得相同排名，确保同时间的多条记录被批量处理。
3. recursive_data：
   • 起始部分：先获取每个Line的最新记录（排名为1的所有记录），这部分是必保留的。
   • 递归部分：依次获取下一批排名的记录，仅当这批记录与上一批记录的时间间隔≤60分钟时，才将其纳入结果集；一旦某批记录与上一批间隔超过60分钟，递归终止，更早的记录不再被纳入。
4. 最终结果：输出符合条件的记录，按Line和时间降序排列，确保最新记录在前。

注意事项 #

• 如果你的数据中同时间的重复记录不需要全部保留，可以将DENSE_RANK()替换为ROW_NUMBER()，并在ORDER BY中加入ID或其他唯一标识，这样每条记录会获得唯一排名，递归时会逐条判断。
• 时间间隔判断使用rd_current.Date_Time - rd_next.Date_Time <= 1/24，是因为Oracle中DATE类型相减得到的是天数，1/24代表1小时（60分钟），该方式比转换为分钟更精准，避免浮点数误差。

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