如何用SQL查询RDBMS中CDR表统计近12个完整月均通话时长TopN客户

最优实现方案思路 #

你的基础思路方向是对的，但可以做针对性优化，规避冗余计算、边界错误问题，整体步骤如下：

• 第一步：先做时间过滤，提前筛选过去12个完整月的通话记录，减少后续计算的数据量。这里要注意时间区间的边界逻辑，不要用当前日期减12个月的模糊匹配，直接算出准确的起止日期：
  起始日期是DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')，结束日期是DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'，刚好覆盖完整的12个自然月，不会包含当前月的不完整数据。
• 第二步：统一拼接计算单条通话时长，注意日期和时间拼接时的类型转换，避免跨天通话的时长计算错误。这里直接把日期和时间拼接成完整的timestamp类型再求差值，不需要拆分计算时分秒。
• 第三步：按customer_id和通话所属月份分组，计算每个客户每个月的总通话时长。
• 第四步：按customer_id分组，计算每个客户的平均月度通话时长：用12个月的总通话时长除以12即可，不需要单独存每个月的数值再求平均，减少中间步骤。如果需要排除客户某个月完全没有通话的情况，可以调整为除以客户实际有通话的月份数，根据你的业务需求调整即可。
• 第五步：按平均月度通话时长倒序排序，取Top N即可。

示例SQL（适配PostgreSQL语法，其他数据库只要调整时间函数即可） #

WITH filtered_calls AS (
    -- 第一步：先过滤符合时间范围的通话，减少后续计算量
    SELECT 
        customer_id,
        -- 拼接成完整的起止时间戳
        (call_start_day || ' ' || call_start_time)::TIMESTAMP AS call_start_ts,
        (call_end_day || ' ' || call_end_time)::TIMESTAMP AS call_end_ts,
        -- 提取通话所属月份，用于后续分组
        DATE_TRUNC('month', call_start_day) AS call_month
    FROM phonebook
    WHERE call_start_day >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
      AND call_start_day < DATE_TRUNC('month', CURRENT_DATE)
),
call_duration AS (
    -- 第二步：计算单条通话时长，单位为秒，可根据需要调整为分钟/小时
    SELECT 
        customer_id,
        call_month,
        EXTRACT(EPOCH FROM (call_end_ts - call_start_ts)) AS duration_sec
    FROM filtered_calls
    -- 过滤异常数据：结束时间早于开始时间的无效通话
    WHERE call_end_ts > call_start_ts
),
monthly_total AS (
    -- 第三步：计算每个客户每个月的总通话时长
    SELECT 
        customer_id,
        call_month,
        SUM(duration_sec) AS monthly_total_sec
    FROM call_duration
    GROUP BY customer_id, call_month
)
-- 第四步：计算平均月度通话时长，排序取Top N
SELECT 
    customer_id,
    -- 这里的12是固定12个完整月，如果要按实际有通话的月份算平均，改成COUNT(call_month)即可
    SUM(monthly_total_sec) / 12 AS avg_monthly_sec,
    SUM(monthly_total_sec) / 12 / 60 AS avg_monthly_min -- 转成分钟可以用这个字段
FROM monthly_total
GROUP BY customer_id
ORDER BY avg_monthly_sec DESC
LIMIT 10; -- 这里的10替换成你需要的N即可

方案优势 #

1. 提前做了数据过滤，比全表计算每条通话时长再过滤的性能高很多，尤其是数据量大的情况下优势非常明显
2. 统一用timestamp计算时长，自动处理跨天通话的场景，不会出现时长计算错误
3. 增加了无效通话的过滤逻辑，避免异常数据拉低统计结果的准确性
4. 可以灵活调整平均的计算规则：如果业务要求是「有通话的月份的平均值」，把除数改成COUNT(call_month)即可；如果要求是「过去12个月不管有没有通话都算分母」，就固定用12
5. 分层的CTE结构可读性高，后续如果要调整统计逻辑不用大改整个SQL

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