29. Средняя сетевая задержка

В одном из сервисов организована древовидная структура бэкендов. Запросы в сервис обрабатываются следующим образом: в корневой бэкенд (назовем его balancer.test.yandex.ru) поступает запрос, он формирует подзапросы в бэкенды-потомки (не более одного подзапроса в потомок), ожидает от них ответ, формирует свой ответ и отправляет его пользователю. Каждый из потомков обрабатывает подзапросы по той же схеме. На всех бэкендах регистрируются события следующей структуры:

• datetime — время наступления события;

• request_id — id запроса;

• parent_request_id — id родительского запроса (для корневого бэкенда NULL);

• host — имя бэкенда, на котором возникло событие;

• type — тип события (список указан ниже);

• data — описание события.

События бывают следующих типов:

• RequestReceived — на бэкенд поступил новый запрос (поле data пустое);

• RequestSent — на бэкенд-потомок был отправлен подзапрос (в поле data записывается имя бэкенда-потомка);

• ResponseSent — бэкенд отправил ответ родителю (data пустое);

• ResponseReceived — бэкенд получил ответ от потомка (в поле data записываются имя бэкенда-потомка и статус — OK или ERROR — , разделенные символом табуляции).

Все события собираются в одну таблицу. Очевидно, что на каждом бэкенде имеются сетевые издержки на пересылку запросов и ответов. Для одного запроса в корневой бэкенд считаем, что на них тратится сумма разниц datetime между всеми соответствующими парами событий RequestSent/RequestReceived и ResponseSent/ResponseReceived, которые возникли при обработке запроса. Вам нужно посчитать эту величину, усредненную по запросам в корневой бэкенд.

Используется БД postgresql 10.6.1 x64.

Система перед проверкой создает таблицу с событиями следующим запросом:

CREATE TABLE requests (
    datetime TIMESTAMP,
    request_id UUID,
    parent_request_id UUID,
    host TEXT,
    type TEXT,
    data TEXT
);

После таблица заполняется тестовыми данными.

Напишите SELECT выражение, которое вернет таблицу из одной строки с колонкой avg_network_time_ms типа numeric, в которую будет записана средняя сетевая задержка в миллисекундах.

Внимание! Текст выражения подставится в систему как подзапрос, поэтому завершать выражение точкой с запятой не надо (в противном случае вы получите ошибку Presentation Error).

Для таблицы requests с таким содержимым (здесь для компактности пишем числа вместо UUID'а и миллисекунды в datetime, в проверочной таблице будут UUID'ы и timestamp'ы):

запрос участника должен возвращать следующий результат:

Тут два корневых запроса. Выпишем времена, которые прошли между отправкой запроса/ответа и его получением.

1. Запрос с id $0$

2. balancer.test.yandex.ru -> backend1.ru — $50$ мс (от $.100$ до $.150$)

3. balancer.test.yandex.ru -> backend2.ru — $99$ мс (от $.101$ до $.200$)

4. backend1.ru -> backend3.ru — $45$ мс (от $.155$ до $.200$)

5. backend2.ru -> balancer.test.yandex.ru — $40$ мс (от $.210$ до $.250$)

6. backend3.ru -> backend1.ru — $40$ мс (от $.220$ до $.260$)

7. backend1.ru -> balancer.test.yandex.ru — $10$ мс (от $.300$ до $.310$)

   Суммарно это $50 + 99 + 45 + 40 + 40 + 10 = 284$ мс

8. Запрос с id $4$

9. balancer.test.yandex.ru -> backend1.ru — $5$ мс (от $.505$ до $.510$)

10. backend1.ru -> balancer.test.yandex.ru — $10$ мс (от $.700$ до $.710$)

    Суммарно это $5 + 10 = 15$ мс

Итого, ответ $(284 + 15) / 2 = 149.5$.