如何用PL SQL将行中操作与时间戳转列？Pivot返回空值求方案

原始数据 #

NAME    OPERATION   TIMESTAMP
A       START      18/01/2024 8:24
A       START      18/01/2024 8:27
A       START      18/01/2024 8:30
A       END        18/01/2024 8:34
B       START      18/01/2024 9:28
B       END        18/01/2024 9:32
B       START      18/01/2024 9:37
B       END        18/01/2024 9:39
C       START      18/01/2024 10:14
C       END        18/01/2024 10:18

期望结果 #

NAME    START_EVENT START_TIME       END_EVENT  END_TIME         DURATION
A       START       18/01/2024 8:24  END        18/01/2024 8:34  0.006944444
B       START       18/01/2024 9:28  END        18/01/2024 9:32  0.002777778
B       START       18/01/2024 9:37  END        18/01/2024 9:39  0.001388889
C       START       18/01/2024 10:14 END        18/01/2024 10:18 0.002777778

解决方案 #

方法1：使用MATCH_RECOGNIZE（Oracle 12c+推荐） #

MATCH_RECOGNIZE是Oracle专门用于序列模式匹配的特性，能精准匹配每个START事件对应的后续END事件，即使中间存在多个START（如示例中的A）。

SELECT
    NAME,
    START_EVENT,
    START_TIME,
    END_EVENT,
    END_TIME,
    (END_TIME - START_TIME) AS DURATION
FROM
    YOUR_TABLE_NAME
MATCH_RECOGNIZE(
    PARTITION BY NAME
    ORDER BY TIMESTAMP
    MEASURES
        FIRST(START_OP.OPERATION) AS START_EVENT,
        FIRST(START_OP.TIMESTAMP) AS START_TIME,
        END_OP.OPERATION AS END_EVENT,
        END_OP.TIMESTAMP AS END_TIME
    PATTERN (START_OP+ END_OP)
    DEFINE
        START_OP AS OPERATION = 'START',
        END_OP AS OPERATION = 'END'
)
ORDER BY NAME, START_TIME;

逻辑说明：

• PARTITION BY NAME：按NAME分组处理每个对象的事件序列
• ORDER BY TIMESTAMP：确保事件按时间顺序匹配
• PATTERN (START_OP+ END_OP)：匹配一个或多个START事件后跟随一个END事件的模式
• MEASURES：提取第一个START事件的操作和时间，以及对应的END事件的操作和时间
• 时长计算：Oracle中日期类型相减结果为天数，与示例中的DURATION格式一致

方法2：使用窗口函数兼容低版本Oracle #

如果你的Oracle版本低于12c，可以用窗口函数给START和END事件分别编号，再关联配对：

WITH start_events AS (
    SELECT
        NAME,
        OPERATION AS START_EVENT,
        TIMESTAMP AS START_TIME,
        ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY TIMESTAMP) AS start_seq
    FROM YOUR_TABLE_NAME
    WHERE OPERATION = 'START'
),
end_events AS (
    SELECT
        NAME,
        OPERATION AS END_EVENT,
        TIMESTAMP AS END_TIME,
        ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY TIMESTAMP) AS end_seq
    FROM YOUR_TABLE_NAME
    WHERE OPERATION = 'END'
)
SELECT
    s.NAME,
    s.START_EVENT,
    s.START_TIME,
    e.END_EVENT,
    e.END_TIME,
    (e.END_TIME - s.START_TIME) AS DURATION
FROM start_events s
JOIN end_events e
    ON s.NAME = e.NAME
    AND s.start_seq = e.end_seq
ORDER BY s.NAME, s.START_TIME;

逻辑说明：

• 先分别筛选START和END事件，用ROW_NUMBER()给每个NAME下的事件按时间编号
• 通过编号关联同一NAME下的第N个START和第N个END
• 这种方法适用于START和END一一对应的场景（如示例中的B、C），对于A这种多START对应一个END的情况，会只保留第一个START对应END（与期望结果一致）

注意：如果你的TIMESTAMP字段是字符串类型，需要先用TO_DATE转换为日期类型再计算时长，例如：

(TO_DATE(e.END_TIME, 'DD/MM/YYYY HH24:MI') - TO_DATE(s.START_TIME, 'DD/MM/YYYY HH24:MI')) AS DURATION

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