如何在BigQuery SQL中计算最大聊天会话并发数及在线客服数

原查询的问题 #

你之前的查询仅按sessionstart分组，统计的是该时刻新启动的聊天会话和客服数量，无法覆盖那些在该时刻之前启动、但仍处于活跃状态的会话，所以才会出现13:00时只统计到3条，而实际有5条活跃会话的情况。

解决方案思路 #

要统计任意时刻的活跃会话数，核心是判断每个会话是否在该时刻处于[sessionstart, sessionend)的区间内（即会话已启动但未结束）。这里提供两种可行的SQL方案，适配不同的数据量场景：

方案1：时间点关联法（直观易理解，适合小数据集）

先提取所有可能的关键时间点（所有会话的开始、结束时间），再对每个时间点统计重叠的活跃会话和客服数：

WITH all_time_points AS (
    -- 提取所有会话的开始和结束时间，去重得到关键时间点
    SELECT STR_TO_DATE(sessionstart, '%Y%m%d-%H%i%s') AS time_point FROM source
    UNION
    SELECT STR_TO_DATE(sessionend, '%Y%m%d-%H%i%s') AS time_point FROM source
),
active_stats AS (
    SELECT
        tp.time_point,
        -- 统计该时间点活跃的会话数：会话开始<=当前时间，且会话结束>当前时间
        COUNT(DISTINCT s.chatsessionID) AS active_chats,
        -- 统计当前活跃会话对应的唯一客服数
        COUNT(DISTINCT s.agentID) AS active_agents
    FROM all_time_points tp
    LEFT JOIN source s
        ON STR_TO_DATE(s.sessionstart, '%Y%m%d-%H%i%s') <= tp.time_point
        AND STR_TO_DATE(s.sessionend, '%Y%m%d-%H%i%s') > tp.time_point
    GROUP BY tp.time_point
)
-- 若仅需最大活跃数：
SELECT
    MAX(active_chats) AS max_active_chats,
    MAX(active_agents) AS max_active_agents
FROM active_stats;

-- 若需查看每个时间点的详细统计：
-- SELECT time_point, active_chats, active_agents FROM active_stats ORDER BY active_chats DESC;

说明：

• 用STR_TO_DATE将字符串格式的时间戳转换为可比较的时间类型（MySQL语法，PostgreSQL可换成TO_TIMESTAMP(sessionstart, 'YYYYMMDD-HH24MISS')）
• 会话结束时间用>而非>=，是因为sessionend时刻会话已结束，不再计入活跃数（可根据业务调整逻辑）

方案2：事件流累加发（高效，适合大数据集）

将每个会话拆分为"开始（+1）"和"结束（-1）"两个事件，按时间排序后累加计数，同时追踪活跃客服：

WITH event_data AS (
    -- 会话开始事件：计数+1
    SELECT
        STR_TO_DATE(sessionstart, '%Y%m%d-%H%i%s') AS event_time,
        chatsessionID,
        agentID,
        1 AS session_change,
        1 AS event_order -- 开始事件排序靠后，确保同一时间先处理结束事件
    FROM source
    UNION ALL
    -- 会话结束事件：计数-1
    SELECT
        STR_TO_DATE(sessionend, '%Y%m%d-%H%i%s') AS event_time,
        chatsessionID,
        agentID,
        -1 AS session_change,
        0 AS event_order
    FROM source
),
sorted_events AS (
    -- 按时间排序，同一时间先处理结束事件
    SELECT * FROM event_data ORDER BY event_time, event_order
),
running_stats AS (
    SELECT
        event_time,
        -- 累计计算活跃会话数
        SUM(session_change) OVER (ORDER BY event_time, event_order) AS active_chats,
        -- 统计当前活跃会话对应的唯一客服数（以PostgreSQL为例，MySQL可改用方案1的关联逻辑）
        COUNT(DISTINCT agentID) FILTER (
            WHERE SUM(session_change) OVER (PARTITION BY chatsessionID ORDER BY event_time, event_order) > 0
        ) OVER () AS active_agents
    FROM sorted_events
)
-- 去重后取最大值
SELECT
    MAX(active_chats) AS max_active_chats,
    MAX(active_agents) AS max_active_agents
FROM (
    SELECT DISTINCT event_time, active_chats, active_agents FROM running_stats
) AS unique_stats;

说明：

• 事件排序时优先处理结束事件，避免同一时间点结束的会话被误计入活跃数
• 大数据量下，这种方式的性能远优于方案1，因为不需要对每个时间点做全表关联

示例验证 #

针对你提到的场景：3条会话13:00开始，2条会话12:55开始、13:05结束。用方案1查询13:00这个时间点时，会匹配到所有5条活跃会话，统计出active_chats=5，符合你的需求。

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