如何通过CDR高效计算每秒并发呼叫？优化存储过程性能

游标循环逐行更新的本质是行级串行操作，86400次循环会产生巨量磁盘IO和事务开销，这是性能低下的核心原因。改用集合运算+窗口函数的方式可一次性完成所有计算，性能能提升几个数量级。

核心思路 #

并发呼叫数的本质是：每个时间点的并发数 = 此前所有呼叫开始数 - 此前所有呼叫结束数（结束时间的下一秒才会减少并发）。通过以下步骤实现：

1. 从CDR表提取所有呼叫开始（+1）和结束下一秒（-1）的增量记录
2. 合并时间戳表的所有时间点，用窗口函数累计求和得到每个时间点的并发数
3. 批量更新时间戳表的cnt字段

具体SQL实现 #

WITH cdr_increments AS (
    -- 呼叫开始时间点，并发数+1
    SELECT starttime AS ts, 1 AS delta
    FROM testlist20230519_20
    UNION ALL
    -- 呼叫结束的下一秒，并发数-1（endtime时刻呼叫仍在进行）
    SELECT DATEADD(second, 1, endtime) AS ts, -1 AS delta
    FROM testlist20230519_20
),
time_agg AS (
    -- 按时间点汇总增量值
    SELECT ts, SUM(delta) AS total_delta
    FROM cdr_increments
    GROUP BY ts
),
concurrent_calc AS (
    -- 关联时间戳表，计算累计并发数
    SELECT 
        t.ts,
        -- 累计求和，处理无增量的时间点，同时避免负数（异常数据兼容）
        SUM(COALESCE(a.total_delta, 0)) OVER (ORDER BY t.ts) AS cnt
    FROM testtimestamp_10 t
    LEFT JOIN time_agg a ON t.ts = a.ts
)
-- 批量更新时间戳表的cnt字段
UPDATE testtimestamp_10 t
SET cnt = c.cnt
FROM concurrent_calc c
WHERE t.ts = c.ts;

额外性能优化建议 #

• 给testlist20230519_20的starttime和endtime字段添加普通索引，加速增量数据提取
• 确保testtimestamp_10的ts字段是主键或唯一索引，关联更新时可快速定位行
• 若CDR数据量极大，可按日期分区存储，减少单次扫描的数据范围

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