在BigQuery中实现FCR(首次联系解决率)：T-SQL转BigQuery方案求助

原T-SQL依赖的OUTER APPLY语法BigQuery不支持，咱们可以用窗口函数+子查询或者LEFT JOIN聚合子查询来实现相同的首呼解决率计算逻辑，以下是两种可行方案：

假设原表结构（和原问题一致） #

表包含这些核心字段：

• TicketNumber：工单编号
• CallDateTime：通话时间
• CallResult：通话结果（比如'Resolved'、'Escalated'）
• AgentID：坐席ID

方案1：窗口函数标记首次通话（推荐，高效易维护） #

WITH ticket_calls AS (
  SELECT
    TicketNumber,
    CallDateTime,
    CallResult,
    AgentID,
    -- 给每个工单的通话按时间排序，标记首次通话为1
    ROW_NUMBER() OVER(PARTITION BY TicketNumber ORDER BY CallDateTime) AS call_seq
  FROM
    `你的项目ID.你的数据集ID.你的表名` -- 替换成你的BigQuery表路径
)
SELECT
  t1.TicketNumber,
  t1.CallDateTime AS FirstCallDateTime,
  t1.CallResult AS FirstCallResult,
  t1.AgentID AS FirstCallAgent,
  -- 判断是否首呼解决：首次通话就解决，且没有后续的解决通话
  CASE
    WHEN t1.CallResult = 'Resolved' 
         AND NOT EXISTS (
             SELECT 1 FROM ticket_calls t2
             WHERE t2.TicketNumber = t1.TicketNumber
               AND t2.call_seq > 1
               AND t2.CallResult = 'Resolved'
         ) THEN 'Yes'
    ELSE 'No'
  END AS IsFirstCallResolution
FROM
  ticket_calls t1
WHERE
  t1.call_seq = 1; -- 只保留每个工单的首次通话记录

方案2：LEFT JOIN关联首次与后续通话（贴近原OUTER APPLY逻辑） #

WITH first_calls AS (
  SELECT
    TicketNumber,
    MIN(CallDateTime) AS FirstCallDateTime
  FROM
    `你的项目ID.你的数据集ID.你的表名`
  GROUP BY
    TicketNumber
)
SELECT
  fc.TicketNumber,
  fc.FirstCallDateTime,
  c1.CallResult AS FirstCallResult,
  c1.AgentID AS FirstCallAgent,
  -- 判断逻辑：首次通话解决，且没有后续的解决通话
  CASE
    WHEN c1.CallResult = 'Resolved' 
         AND c2.TicketNumber IS NULL THEN 'Yes'
    ELSE 'No'
  END AS IsFirstCallResolution
FROM
  first_calls fc
LEFT JOIN
  `你的项目ID.你的数据集ID.你的表名` c1
  ON fc.TicketNumber = c1.TicketNumber
  AND fc.FirstCallDateTime = c1.CallDateTime
LEFT JOIN
  `你的项目ID.你的数据集ID.你的表名` c2
  ON fc.TicketNumber = c2.TicketNumber
  AND c2.CallDateTime > fc.FirstCallDateTime
  AND c2.CallResult = 'Resolved'
GROUP BY
  fc.TicketNumber, fc.FirstCallDateTime, c1.CallResult, c1.AgentID;

注意事项 #

• 把代码里的你的项目ID.你的数据集ID.你的表名替换成你实际的BigQuery表路径
• 首呼解决的判断逻辑是：首次通话结果为'Resolved'，且该工单没有后续的解决通话（确保问题在第一次通话就彻底解决）

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