编写postgresql SELECT-expression，根据存储在单个数据库中的事件数据，查找树型主机网络中根主机处理的每个请求的平均网络延迟。通过查找RequestSent/RequestReceived和ResponseSent/ResponseReceived事件之间的日期时间差之和，计算平均网络延迟。计算所有对根主机的请求的平均查询执行时间。
x1c 0d1x导出的sql文件https://dropmefiles.com/Y5dXS
有两个根查询。让我们写下发送请求/响应和接收请求/响应之间经过的时间。

Request with id 0:
balancer.test -> backend1 – 50ms (from 0.100s to 0.150s)
balancer.test -> backend2 – 99ms (from 0.101s to 0.200s)
backend1 -> backend3 – 45ms (from 0.155s to 0.200s)
backend2 -> balancer.test – 40ms (from 0.210s to 0.250s)
backend3 -> backend1 – 40ms (from 0.220s to 0.260s)
backend1 -> balancer.test – 10ms (from 0.300s to 0.310s)

总和为284 ms

Request with id 4:
balancer.test -> backend1 – 5ms (from 0.505s to 0.510s)
backend1 -> balancer.test – 10ms (from 0.700s to 0.710s)

总和为15 ms
所以，答案是（284 + 15）/2 = 149.5。
我试着实现这个非递归查询，但是给出了一个错误的否定结果。我想我们需要递归，但是我不知道如何实现。你能帮我吗？

WITH event_pairs AS (
    SELECT
        t1.request_id,
        t1.datetime AS sent_time,
        MIN(t2.datetime) AS received_time
    FROM
        requests t1
        JOIN requests t2 ON t1.request_id = t2.parent_request_id
    WHERE
        (t1.type = 'RequestSent' AND t2.type = 'RequestReceived')
        OR (t1.type = 'ResponseSent' AND t2.type = 'ResponseReceived')
    GROUP BY
        t1.request_id,
        t1.datetime
),
request_latency AS (
    SELECT
        request_id,
        SUM((received_time - sent_time) * 1000) AS latency
    FROM
        event_pairs
    GROUP BY
        request_id
),
root_requests AS (
    SELECT
        request_id
    FROM
        requests
    WHERE
        parent_request_id IS NULL
        AND type = 'RequestReceived'
)
SELECT
    AVG(latency) AS average_latency
FROM
    request_latency
    JOIN root_requests USING (request_id);