如何在QuestDB中查询并发查询的最大、平均值及每分钟平均最大并发数

前提说明 #

要计算并发查询数，核心逻辑是统计任意时刻处于执行状态的查询数量——每个查询的执行区间为 [ts, ts + execution_micros]（单位：微秒），我们通过拆分查询的"开始/结束"事件，累加计算并发数。

1. 查询全局并发查询的最大值与平均值 #

以下SQL通过构造事件流、累加并发数，最终得到全局范围内的并发最大值和平均值：

WITH events AS (
    -- 拆分每个查询的开始事件（并发数+1）和结束事件（并发数-1）
    SELECT ts AS event_time, 1 AS delta FROM _query_trace
    UNION ALL
    SELECT ts + execution_micros * 1micros AS event_time, -1 AS delta FROM _query_trace
),
concurrent_counts AS (
    -- 按时间排序累加delta，得到每个时间点的并发查询数
    SELECT 
        event_time,
        sum(delta) OVER (ORDER BY event_time) AS concurrent_queries
    FROM events
    ORDER BY event_time
)
-- 计算全局最大并发和平均并发
SELECT
    max(concurrent_queries) AS max_concurrent_queries,
    avg(concurrent_queries) AS avg_concurrent_queries
FROM concurrent_counts;

2. 查询每分钟并发查询的平均最大值 #

先统计每个分钟窗口内的并发最大值，再对这些最大值求平均，SQL如下：

WITH events AS (
    SELECT ts AS event_time, 1 AS delta FROM _query_trace
    UNION ALL
    SELECT ts + execution_micros * 1micros AS event_time, -1 AS delta FROM _query_trace
),
concurrent_counts AS (
    SELECT 
        event_time,
        sum(delta) OVER (ORDER BY event_time) AS concurrent_queries
    FROM events
    ORDER BY event_time
),
per_minute_max AS (
    -- 按分钟分组，计算每个分钟内的最大并发数
    SELECT
        date_trunc('minute', event_time) AS minute_window,
        max(concurrent_queries) AS max_concurrent_in_minute
    FROM concurrent_counts
    GROUP BY minute_window
)
-- 计算所有分钟最大并发的平均值
SELECT
    avg(max_concurrent_in_minute) AS avg_minute_max_concurrent
FROM per_minute_max;

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