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
时间: 2024-03-23 16:41:25 浏览: 98
这是一个MySQL查询语句,它的作用是查询当前时间到前两小时的话单数据,然后统计同一个被叫号码总共被拨打的次数,如果超过5次,则认为异常,最后返回拨打号码和被叫号码。
具体的查询逻辑如下:
1. 查询话单表中,通话开始时间在当前时间到前两小时之间的记录。
```sql
WHERE call_start_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
```
2. 排除掉拨打号码以0开头的记录。
```sql
AND caller_number NOT LIKE '0%'
```
3. 对查询结果按照被叫号码和通话开始时间进行排序,并使用MySQL的变量和IF函数,统计每个被叫号码被拨打的次数。
```sql
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
```
4. 最后将统计结果作为子查询,筛选出被拨打次数超过5次的记录,并返回拨打号码和被叫号码。
```sql
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
```
注意:这个SQL查询语句只实现了被拨打次数超过5次的监控规则,还需要结合其他查询和处理,才能实现完整的异常监控。
阅读全文