在BigQuery中分区统计人类对话阶段的文本消息行数

需求回顾 #

统计聊天数据中仅人类交互的文本消息数量，规则如下：

• 对话触发点：Client+Click事件之后
• 计数起点：对话内第一条Employee+default消息
• 计数终点：下一个非人类活动（机器人/系统交互）出现前，或对话最后一条消息

假设表结构 #

假设你的表包含以下核心字段（可根据实际结构调整）：

• conversation_id：对话唯一标识
• messageID：消息/活动唯一值
• timestamp：事件发生时间
• sender：发送方（client/employee/bot/system）
• event：事件类型（Click/Stop/message/default）

实现代码 #

WITH conversation_events AS (
  SELECT
    *,
    -- 按对话分区，时间顺序生成行号
    ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY timestamp) AS row_num,
    -- 标记当前对话中Client+Click的时间点
    MAX(IF(sender = 'client' AND event = 'Click', timestamp, NULL)) OVER (PARTITION BY conversation_id) AS client_click_time,
    -- 找到当前对话中第一条Employee+default消息的时间
    MIN(IF(sender = 'employee' AND event = 'default', timestamp, NULL)) OVER (PARTITION BY conversation_id) AS first_employee_default_time
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
valid_records AS (
  SELECT
    *,
    -- 判断记录是否在有效窗口内：Click之后 + 第一条Employee消息之后
    IF(timestamp >= client_click_time AND timestamp >= first_employee_default_time, TRUE, FALSE) AS in_valid_window,
    -- 标记非人类活动（根据业务调整判断条件）
    IF(sender IN ('bot', 'system') OR event IN ('Click', 'Stop'), TRUE, FALSE) AS is_non_human_activity
  FROM
    conversation_events
  WHERE
    -- 过滤掉Click之前的无效记录
    timestamp >= client_click_time
),
non_human_boundary AS (
  SELECT
    conversation_id,
    -- 找到有效窗口内第一个非人类活动的时间，作为计数终点
    MIN(IF(in_valid_window AND is_non_human_activity, timestamp, NULL)) OVER (PARTITION BY conversation_id) AS first_non_human_time
  FROM
    valid_records
),
final_counts AS (
  SELECT
    vr.conversation_id,
    COUNT(*) AS human_interaction_message_count
  FROM
    valid_records vr
  JOIN
    non_human_boundary nhb ON vr.conversation_id = nhb.conversation_id
  WHERE
    vr.in_valid_window
    -- 仅统计到第一个非人类活动前，或无后续非人类活动则统计到对话结束
    AND (vr.timestamp < nhb.first_non_human_time OR nhb.first_non_human_time IS NULL)
    -- 仅统计人类文本消息：Employee的default消息 + 人类Client的message
    AND ((vr.sender = 'employee' AND vr.event = 'default') OR (vr.sender = 'client' AND vr.event = 'message'))
  GROUP BY
    vr.conversation_id
)
SELECT * FROM final_counts;

代码说明 #

1. conversation_events：按对话分区，标记对话的触发点（Client+Click）和计数起点（第一条Employee+default消息）。
2. valid_records：过滤掉Click之前的记录，标记有效计数窗口和非人类活动。
3. non_human_boundary：确定每个对话中有效窗口内的第一个非人类活动时间，作为计数的截止点。
4. final_counts：统计符合条件的人类交互消息数量，仅包含Employee的默认消息和人类客户端的文本消息。

注意事项 #

• 替换代码中的表路径your-project.your-dataset.your-table为实际的BigQuery表地址。
• 非人类活动的判断条件需根据业务实际调整（比如如果机器人模拟的Client消息有特定标识，需补充到is_non_human_activity的判断中）。
• 如果对话中没有Employee+default消息，该对话不会被统计（因为计数起点不存在）。

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