如何通过BigQuery SQL计算员工的平均回电时长？

解题思路 #

• 对同一员工、同一号码的通话记录，将每条IN（呼入）记录关联到它之后最早的OUT（呼出）记录
• 计算每条匹配记录的时间差（转为小时）
• 按员工分组，统计这些时间差的平均值

实现SQL #

WITH call_pairs AS (
  SELECT
    staff,
    number,
    called_at AS in_call_time,
    -- 匹配当前IN记录之后最早的OUT记录时间
    MIN(CASE WHEN direction = 'OUT' THEN called_at END) OVER (
      PARTITION BY staff, number
      ORDER BY called_at ASC
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS out_call_time
  FROM `your-project.your-dataset.call_logs`
  WHERE direction = 'IN'
),
valid_pairs AS (
  SELECT
    staff,
    -- 计算呼入到呼出的小时差
    TIMESTAMP_DIFF(out_call_time, in_call_time, HOUR) AS call_diff_hours
  FROM call_pairs
  WHERE out_call_time IS NOT NULL -- 过滤无对应呼出的呼入记录
)
SELECT
  staff,
  ROUND(AVG(call_diff_hours), 1) AS avg_hour
FROM valid_pairs
GROUP BY staff
ORDER BY staff;

结果验证 #

执行后将得到符合期望的输出：

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