BigQuery中统计PTP出现前呼叫次数并重置计数的实现问题

正确实现代码 #

WITH seq_grp AS (
  SELECT
    calls.source_id AS customer_id,
    calls.rdate AS call_datetime,
    calls.outbound_log_id AS calls_id,
    CASE 
      WHEN dispo.q_code_grouping_desc = 'PTP' 
      THEN 'PTP' 
      ELSE 'NO PTP'
    END AS ptp_ind,
    -- 按用户分组，累计到当前行的PTP出现次数，作为重置分组的依据
    SUM(CASE WHEN dispo.q_code_grouping_desc = 'PTP' THEN 1 ELSE 0 END) 
      OVER (PARTITION BY calls.source_id ORDER BY calls.rdate ASC) AS grp
  FROM db.prepared_layers.outbound_log AS calls
  LEFT JOIN db.latest_dim.outbound_service_dm AS service
    ON service.id = calls.service_id
  LEFT JOIN db.latest_dim.outbound_dispo_all AS dispo
    ON dispo.code = calls.q_code
    AND dispo.service_id = calls.service_id
  WHERE service.service_type_dept = 'collections' 
    AND DATE(rdate) >= '2024-06-01'
    AND calls.source_id IN (1600, 1601)
),
call_counts AS (
  SELECT
    *,
    -- 在每个用户+分组内，按时间生成行号
    ROW_NUMBER() OVER (PARTITION BY customer_id, grp ORDER BY call_datetime ASC) AS rn
  FROM seq_grp
)
SELECT
  customer_id,
  call_datetime,
  calls_id,
  ptp_ind,
  CASE
    WHEN ptp_ind = 'PTP' THEN 0
    -- 首次PTP前的组（grp=0），行号直接作为累计次数
    WHEN grp = 0 THEN rn
    -- 后续组中，非PTP行的计数为行号-1（跳过组内的PTP行）
    ELSE rn - 1
  END AS calls_made_before
FROM call_counts
ORDER BY customer_id, call_datetime ASC;

思路说明 #

1. 分组逻辑：用SUM(CASE...) OVER()窗口函数按用户累计PTP出现次数，将记录划分为多个组——每个组包含「上次PTP之后（或初始状态）到当前PTP」的所有记录。
2. 组内计数：在每个组内用ROW_NUMBER()生成按时间排序的行号，标记组内的顺序。
3. 最终计数：
   • PTP行直接设为0；
   • 首次PTP前的非PTP行，行号即为累计呼叫次数；
   • 后续组的非PTP行，行号减1（因为组内第一行是PTP，行号从1开始），得到从上次PTP后的累计次数。

原代码问题分析 #

你之前的错误在于最后一步用LAG(rn)获取计数，这会取上一行的行号而非当前组内的累计值。正确的方式是直接基于组内的行号，结合PTP标记来计算当前行的计数，无需使用LAG()。

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