Oracle CTE查询无法返回全部分组的状态起止时间记录

原查询的CTE b 逻辑存在两处错误：

• 窗口函数dense_rank()未添加PARTITION BY col_name分区规则，是对全表所有UP状态记录做全局排序，取的是全局排名第2的UP记录，并非按col_name分组计算
• 未关联每个分组对应的start_time做时间过滤，没有筛选出log_time大于同组start_time的UP记录，导致Engineering分组无法匹配到正确的end_time值。

WITH a AS(
    SELECT col_name,MIN(log_time) start_time
    FROM test_tab WHERE status = 'DOWN'
    GROUP BY col_name
),
b AS(
    SELECT 
        col_name,
        log_time end_time
    FROM( 
        SELECT 
            t.col_name,
            t.log_time,
            ROW_NUMBER() OVER (PARTITION BY t.col_name ORDER BY t.log_time ASC) rnk
        FROM test_tab t
        JOIN a ON t.col_name = a.col_name
        WHERE t.status = 'UP'
        AND t.log_time > a.start_time
    )
    WHERE rnk = 1
)
SELECT a.col_name,a.start_time,b.end_time 
FROM a
JOIN b ON(a.col_name = b.col_name);

• 第一个CTE a 保留原有正确逻辑，按col_name分组，取每组状态为DOWN的最小log_time作为start_time
• 修正后的CTE b 先关联CTE a拿到每组的start_time，筛选出同组下状态为UP、且log_time晚于start_time的记录，通过窗口函数按col_name分区、log_time升序排序，取排名第1的记录，就是每组恰好大于start_time的最早UP记录，对应要求的end_time
• 最后关联两个CTE输出字段，即可得到符合预期的结果

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