编写PostgreSQL递归查询计算树状网络根请求平均延迟

问题说明 #

要计算树状主机网络里所有根请求的平均网络延迟。每个根请求的延迟是它整个请求树里所有RequestSent/RequestReceived、ResponseSent/ResponseReceived事件对的时间差之和。比如测试数据里两个根请求延迟分别是284ms和15ms，平均就是149.5ms。

原查询问题解析 #

你写的非递归查询算出了负值，问题出在两点：

• 关联逻辑搞反：t1.request_id = t2.parent_request_id这个关联把事件对的归属弄反了，导致有些发送时间比接收时间晚，算出来的时间差是负数。
• 没处理树状层级：非递归只能关联直接的父子请求，没法遍历整个请求树，会漏掉深层子节点的事件对，延迟计算肯定不全。

递归查询解决办法 #

用PostgreSQL的递归CTE（WITH RECURSIVE）遍历整个请求树，把所有关联的事件对都收集到，再汇总根请求的总延迟，最后算平均值：

WITH RECURSIVE request_tree AS (
    -- 先抓所有根请求：parent_request_id为空的RequestReceived事件对应的request_id
    SELECT 
        r.request_id AS root_id,
        r.request_id AS current_id
    FROM requests r
    WHERE r.parent_request_id IS NULL 
      AND r.type = 'RequestReceived'
    
    UNION ALL
    
    -- 递归遍历所有子请求，把每个子请求绑定到对应的根请求
    SELECT 
        rt.root_id,
        r.request_id AS current_id
    FROM request_tree rt
    JOIN requests r ON rt.current_id = r.parent_request_id
),
-- 收集所有要计算的有效事件对
event_pairs AS (
    -- 父请求发RequestSent，子请求收RequestReceived的时间差
    SELECT
        rt.root_id,
        (r_received.datetime - r_sent.datetime) * 1000 AS latency
    FROM requests r_sent
    JOIN requests r_received 
        ON r_sent.request_id = r_received.parent_request_id
        AND r_sent.type = 'RequestSent'
        AND r_received.type = 'RequestReceived'
    JOIN request_tree rt ON r_sent.request_id = rt.current_id
    
    UNION ALL
    
    -- 子请求发ResponseSent，父请求收ResponseReceived的时间差
    SELECT
        rt.root_id,
        (r_received.datetime - r_sent.datetime) * 1000 AS latency
    FROM requests r_sent
    JOIN requests r_received 
        ON r_sent.parent_request_id = r_received.request_id
        AND r_sent.type = 'ResponseSent'
        AND r_received.type = 'ResponseReceived'
    JOIN request_tree rt ON r_sent.request_id = rt.current_id
),
-- 算出每个根请求的总延迟
root_latency AS (
    SELECT
        root_id,
        SUM(latency) AS total_latency
    FROM event_pairs
    GROUP BY root_id
)
-- 最后算所有根请求的平均延迟
SELECT AVG(total_latency) AS average_latency
FROM root_latency;

代码说明 #

• request_tree递归CTE：从根请求开始，把所有子请求都关联到对应的根ID，确保整个请求树里的事件都能归到正确的根请求下。
• event_pairsCTE：分两类收集事件对的时间差，所有事件对都通过request_tree绑定到根请求。
• root_latencyCTE：按根ID汇总所有时间差，得到每个根请求的总延迟。
• 最后一步计算所有根请求总延迟的平均值。

用测试数据跑的话，会正确算出ID0总延迟284ms、ID4总延迟15ms，平均149.5ms。

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