扫描当前时间到前两小时的话单数据。扫描范围内的数据,满足以下规则,即认为异常。 1)、时间范围内连续拨打同一号码监控 在扫描时间范围内,同一个国际被叫号码连续拨打5(可自行配置)次以上。 2)、时间范围内单次拨打电话通话时间过长监控 在扫描时间范围内,国际拨打的一通电话通话时长超过15分钟(可自行配置)分钟以上。 3)、时间范围内拨打同一个号码次数监控 在扫描时间范围内,同一个被叫国际号码总共被拨打的次数5次(可自行配置)以上。 4)、时间范围内拨打国际长途次数监控 在扫描时间范围内,号码总共拨打国际长途的次数10次(可自行配置)以上。mysql实现
时间: 2024-03-19 12:42:50 浏览: 177
可以使用MySQL的查询语句来实现对话单数据的扫描和监控,下面是一个可能的实现方案:
```
SELECT caller_number, called_number, call_start_time, call_end_time, call_duration
FROM call_records
WHERE call_start_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND (
-- 规则1:连续拨打同一号码监控
(caller_number, called_number) IN (
SELECT caller_number, called_number
FROM (
SELECT caller_number, called_number, call_start_time,
IF(called_number=@prev_called_number, @cnt:=@cnt+1, @cnt:=1) AS cnt,
@prev_called_number:=called_number
FROM call_records, (SELECT @prev_called_number:=NULL, @cnt:=1) AS vars
WHERE call_start_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND caller_number NOT LIKE '0%'
ORDER BY called_number, call_start_time
) AS t
WHERE cnt >= 5
)
OR
-- 规则2:单次拨打电话通话时间过长监控
(call_duration >= 900)
OR
-- 规则3:拨打同一个号码次数监控
(called_number IN (
SELECT called_number
FROM call_records
WHERE call_start_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND caller_number NOT LIKE '0%'
GROUP BY called_number
HAVING COUNT(DISTINCT caller_number) >= 5
))
OR
-- 规则4:拨打国际长途次数监控
(caller_number NOT LIKE '0%' AND LENGTH(caller_number) > 5 AND LENGTH(caller_number) < 16 AND LENGTH(called_number) > 5 AND LENGTH(called_number) < 16
AND (SELECT COUNT(*) FROM call_records WHERE caller_number=cr.caller_number AND called_number=cr.called_number AND call_start_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR)) >= 10)
)
```
这个查询语句会返回扫描时间范围内满足监控规则的通话记录,其中包含主叫号码、被叫号码、通话开始时间、通话结束时间和通话时长等信息。你可以根据实际需要对查询语句进行修改和优化。
阅读全文