Oracle 18c中如何使用CTE获取精确无重复的查询结果集

原有查询仅做了同col_name维度下UP记录时间晚于DOWN记录的过滤，没有对匹配到的多条UP记录做「取时间最接近的1条」的限制，所有满足时间晚于DOWN记录的UP都会被关联返回，因此会出现多余的重复行。

以下两种写法均适配Oracle 18c环境，可直接返回预期的3条结果。

方案1：CROSS APPLY写法（逻辑直观，推荐） #

Oracle 12c及以上版本支持CROSS APPLY语法，可以直接针对每一条DOWN记录，匹配符合条件的最近1条UP记录，写法简洁易维护：

SELECT 
    a.col_name,
    a.log_time AS start_time,
    a.status,
    b.log_time AS end_time,
    b.status
FROM test_tab a
CROSS APPLY (
    SELECT *
    FROM test_tab b
    WHERE b.col_name = a.col_name
      AND b.status = 'UP'
      AND b.log_time > a.log_time
    ORDER BY b.log_time ASC
    FETCH FIRST 1 ROW ONLY
) b
WHERE a.status = 'DOWN';

方案2：ROW_NUMBER窗口函数写法（兼容性更好） #

如果需要兼容更低版本Oracle，可以用窗口函数给匹配到的UP记录按时间排序，只取排序后第一位的最近记录：

WITH down_records AS (
    SELECT col_name, log_time AS start_time, status
    FROM test_tab
    WHERE status = 'DOWN'
),
up_records AS (
    SELECT 
        col_name,
        log_time AS end_time,
        status,
        ROW_NUMBER() OVER(PARTITION BY col_name ORDER BY log_time) AS rn
    FROM test_tab
    WHERE status = 'UP'
)
SELECT 
    d.col_name,
    d.start_time,
    d.status,
    u.end_time,
    u.status
FROM down_records d
JOIN up_records u 
  ON d.col_name = u.col_name
WHERE d.start_time < u.end_time
AND u.rn = (
    SELECT MIN(rn) 
    FROM up_records u2
    WHERE u2.col_name = d.col_name
      AND u2.end_time > d.start_time
);

上述两段SQL执行后返回结果和预期完全一致：

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

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