BigQuery中按O_id统计会议前后t_id数量的实现方案

需求说明 #

• 针对每个O_id，独立统计两类任务数量：
  • Before Meeting：该O_id下，task_type为"Meeting"的任务发生前的t_id总数
  • After Meeting：该O_id下，"Meeting"任务发生后的t_id总数

实现逻辑（假设每个O_id仅存在1个会议任务） #

1. 通过窗口函数标记每个O_id对应的会议任务时间戳
2. 基于会议时间戳，用条件计数分别统计前后的任务数量

完整SQL代码 #

WITH task_meeting_ref AS (
  SELECT
    O_id,
    t_id,
    task_time,
    -- 提取当前O_id下会议任务的时间（若多个会议，可替换MIN为MAX取最晚会议时间）
    MIN(CASE WHEN task_type = 'Meeting' THEN task_time END) OVER (PARTITION BY O_id) AS meeting_time
  FROM `your-project.dataset.task_table`
)
SELECT
  O_id,
  -- 统计会议前的任务数（不含会议本身）
  COUNT(CASE WHEN task_time < meeting_time THEN t_id END) AS `Before Meeting`,
  -- 统计会议后的任务数（不含会议本身）
  COUNT(CASE WHEN task_time > meeting_time THEN t_id END) AS `After Meeting`
FROM task_meeting_ref
GROUP BY O_id
ORDER BY O_id;

补充说明 #

• 如果单个O_id存在多个会议任务，可根据业务需求调整：
  • 按第一个会议统计：保留MIN(CASE ...)逻辑
  • 按最后一个会议统计：替换为MAX(CASE ...)逻辑
  • 按每一个会议分别统计：需额外增加会议维度的分组，调整窗口函数逻辑

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