如何基于CDR表计算分时段内通话有效时长的平均值？

核心需求是处理跨区间的通话，计算每个通话落在对应时间区间内的那部分时长，再按区间统计这些分段时长的平均值，以及涉及的通话数量。以下是基于PostgreSQL的实现方案：

实现步骤 #

1. 生成固定时间区间序列：根据通话记录的时间范围，生成5分钟间隔的区间起始点。
2. 推导通话完整时间范围：从通话的start_date和duration_ms计算出结束时间。
3. 计算通话与区间的重叠时长：匹配每个通话和每个区间，计算两者的交集时长，过滤无重叠的记录。
4. 按区间聚合统计：计算每个区间内的平均重叠时长，以及涉及的通话数量。

完整SQL代码 #

WITH time_ranges AS (
    -- 生成覆盖所有通话的5分钟间隔时间区间
    SELECT
        generate_series(
            -- 从最早通话的上一个5分钟整点开始
            date_trunc('minute', min(start_date)) - (date_part('minute', min(start_date))::int % 5) * interval '1 minute',
            -- 到最晚通话结束时间的下一个5分钟整点结束
            date_trunc('minute', max(start_date) + interval '1 millisecond' * duration_ms) + 
            (5 - date_part('minute', max(start_date) + interval '1 millisecond' * duration_ms)::int % 5) * interval '1 minute',
            interval '5 minutes'
        ) AS range_start
    FROM public.cdr
),
call_periods AS (
    -- 计算每个通话的结束时间
    SELECT
        call_id,
        start_date,
        start_date + interval '1 millisecond' * duration_ms AS end_date
    FROM public.cdr
),
overlap_calculations AS (
    -- 计算每个通话与每个区间的重叠时长（毫秒）
    SELECT
        tr.range_start,
        cp.call_id,
        GREATEST(
            0::bigint,
            (EXTRACT(EPOCH FROM LEAST(cp.end_date, tr.range_start + interval '5 minutes')) -
             EXTRACT(EPOCH FROM GREATEST(cp.start_date, tr.range_start))) * 1000
        ) AS overlap_duration_ms
    FROM time_ranges tr
    CROSS JOIN call_periods cp
    -- 过滤掉无重叠的通话-区间组合
    WHERE cp.start_date < tr.range_start + interval '5 minutes'
      AND cp.end_date > tr.range_start
)
-- 最终统计结果
SELECT
    range_start,
    ROUND(AVG(overlap_duration_ms)) AS avg_duration_ms,
    COUNT(DISTINCT call_id) AS number_of_calls_in_range
FROM overlap_calculations
GROUP BY range_start
ORDER BY range_start;

关键细节说明 #

• 时间区间适配：自动覆盖所有通话涉及的时间范围；如果需要固定起止区间，直接替换generate_series的前两个参数即可（例如'2023-05-15 15:00:00'::timestamp）。
• 重叠时长计算：用GREATEST取两个区间的最晚起始时间，LEAST取最早结束时间，两者差值转换为毫秒即为重叠时长。
• 通话数量统计：COUNT(DISTINCT call_id)确保同一个通话跨多个区间时，每个区间仅统计一次，匹配预期结果中的number_of_calls_in_range定义。

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