Oracle 19c查询：计算请求响应时间差及统计耗时分布

现有单表A，请求（REQUEST）与响应（RESPONSE）以两行记录分别存储，需编写Oracle 19c查询完成以下两个需求：

1. 计算同一TRAN_ID下REQUEST与RESPONSE的时间差；
2. 按1秒、2秒…的耗时区间统计处理记录数。

表结构与示例数据 #

表A：

期望输出 #

1. 各TRAN_ID的耗时 #

2. 耗时分布统计 #

尝试的错误查询 #

select RES.timestamp - REQ.timestamp diff
from A
where REQ.TRAN_ID = RES.TRAN_ID;

1. 计算各TRAN_ID的耗时 #

方法一：条件聚合（推荐，性能更优） #

SELECT 
    TRAN_ID AS TRANID,
    ROUND(
        (MAX(CASE WHEN Stage = 'RESPONSE' THEN Timestamp END) - 
         MAX(CASE WHEN Stage = 'REQUEST' THEN Timestamp END)) * 86400
    ) AS Seconds
FROM A
GROUP BY TRAN_ID;

说明：

• Oracle中日期类型相减结果为天数，乘以86400（一天的秒数）转换为秒；
• ROUND()函数用于将小数秒取整，匹配示例的整数输出；
• 条件聚合通过CASE WHEN提取同一TRAN_ID下的响应、请求时间，再计算差值。

方法二：自连接 #

SELECT 
    REQ.TRAN_ID AS TRANID,
    ROUND((RES.Timestamp - REQ.Timestamp) * 86400) AS Seconds
FROM A REQ
JOIN A RES 
    ON REQ.TRAN_ID = RES.TRAN_ID
    AND REQ.Stage = 'REQUEST'
    AND RES.Stage = 'RESPONSE';

2. 耗时分布统计 #

基于第一个查询的结果，通过CTE先计算单条交易耗时，再分组统计：

WITH TranTime AS (
    SELECT 
        ROUND(
            (MAX(CASE WHEN Stage = 'RESPONSE' THEN Timestamp END) - 
             MAX(CASE WHEN Stage = 'REQUEST' THEN Timestamp END)) * 86400
        ) AS Seconds
    FROM A
    GROUP BY TRAN_ID
)
SELECT 
    Seconds,
    COUNT(*) AS count
FROM TranTime
GROUP BY Seconds
ORDER BY Seconds;

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