BigQuery查询修改：统计24小时内同号码超3次的呼叫次数

现有表与原始查询 #

我在BigQuery中有以下表：

• foo.disposition
• foo.poc

原始查询用于统计指定条件下电话号码的总拨号次数：

SELECT
        ANI_DIALNUM, COUNT(*) as Dialed_Frequency                                                                                                                                                                
FROM
(
        select ANI_DIALNUM FROM `foo.disposition`
        WHERE
                Skill_name like 'Support%IB%'
                AND parse_date('%m/%d/%Y', Start_Date) > '2024-01-01'
        union all
        select Contact_Name FROM `foo.disposition`
        WHERE
                Skill_name like 'Support%IB%'
        AND parse_date('%m/%d/%Y', Start_Date) > '2024-01-01'
) t                                                                                                                                                                                                              
WHERE
        ANI_DIALNUM NOT IN (select `Point of Contact` from `foo.poc`)
        AND NOT ANI_DIALNUM = '8.884157149E9'

GROUP BY ANI_DIALNUM
ORDER BY Dialed_Frequency desc

修改后的查询（满足24小时周期重置+超3次呼叫统计） #

WITH combined_calls AS (
  -- 合并两个字段的电话号码，并保留呼叫时间
  SELECT 
    ANI_DIALNUM AS phone_number,
    -- 若Start_Date仅含日期，可改为 parse_date('%m/%d/%Y', Start_Date) + TIME(0,0,0)
    parse_datetime('%m/%d/%Y %H:%M:%S', Start_Date) AS call_datetime
  FROM `foo.disposition`
  WHERE
    Skill_name LIKE 'Support%IB%'
    AND parse_date('%m/%d/%Y', Start_Date) > '2024-01-01'
  UNION ALL
  SELECT 
    Contact_Name AS phone_number,
    parse_datetime('%m/%d/%Y %H:%M:%S', Start_Date) AS call_datetime
  FROM `foo.disposition`
  WHERE
    Skill_name LIKE 'Support%IB%'
    AND parse_date('%m/%d/%Y', Start_Date) > '2024-01-01'
),
filtered_calls AS (
  -- 过滤掉指定排除号码
  SELECT 
    phone_number,
    call_datetime
  FROM combined_calls
  WHERE
    phone_number NOT IN (SELECT `Point of Contact` FROM `foo.poc`)
    AND phone_number != '8.884157149E9'
),
sessionized_calls AS (
  -- 按号码分组，生成会话ID：两次呼叫间隔超24小时则重置会话
  SELECT
    phone_number,
    call_datetime,
    SUM(CASE 
          WHEN TIMESTAMP_DIFF(call_datetime, LAG(call_datetime) OVER (PARTITION BY phone_number ORDER BY call_datetime), HOUR) > 24 
          THEN 1 
          ELSE 0 
        END) OVER (PARTITION BY phone_number ORDER BY call_datetime) AS session_id
  FROM filtered_calls
),
session_counts AS (
  -- 统计每个会话内的呼叫次数
  SELECT
    phone_number,
    session_id,
    MIN(call_datetime) AS session_start_time,
    MAX(call_datetime) AS session_end_time,
    COUNT(*) AS total_calls
  FROM sessionized_calls
  GROUP BY phone_number, session_id
)
-- 筛选出会话内呼叫次数超过3次的结果
SELECT
  phone_number,
  session_id,
  session_start_time,
  session_end_time,
  total_calls
FROM session_counts
WHERE total_calls > 3
ORDER BY phone_number, session_start_time DESC

关键逻辑说明 #

1. 会话分组：通过LAG窗口函数获取同一号码的上一次呼叫时间，计算时间差。若间隔超过24小时，生成新的会话ID，实现“周期在匹配时重置”的要求。
2. 会话统计：按号码和会话ID分组，统计每个24小时周期（会话）内的呼叫次数，以及会话的起止时间。
3. 结果筛选：最终只保留呼叫次数超过3次的会话记录。

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