筛选一段时间内给同一手机号三分钟内重复发送信息的次数

Question

筛选一段时间内给同一手机号X分钟内重复发送信息的次数

例：筛选2022年12月7日，给同一手机号在3分钟内重复发送信息的次数
数据现状
手机号 发送时间
手机号1 202212061320
手机号1 202212071320
手机号1 202212071322
手机号1 202212071325
手机号1 202212071330
手机号2 202212071324
手机号2 202212071321
手机号3 202212071325
手机号3 202212071326

期望结果 7

Answer 1

亲测有效

select 
tel_num ,count(*) num
from (
select 
a.tel_num,a.send_time,TimeStampDiff(minute,a.send_time,b.send_time) cz
from (
select tel_num,
cast(concat_ws(' ',
concat_ws('-',substr(send_time,1,4),substr(send_time,5,2),substr(send_time,7,2)),
concat_ws(':',substr(send_time,9,2),substr(send_time,11,2),'00')
) as datetime) send_time
from test.csdn ) a
join 
(
select tel_num,
cast(concat_ws(' ',
concat_ws('-',substr(send_time,1,4),substr(send_time,5,2),substr(send_time,7,2)),
concat_ws(':',substr(send_time,9,2),substr(send_time,11,2),'00')
) as datetime) send_time
from test.csdn ) b 
on a.tel_num = b.tel_num 
) a where cz >0 and cz <=3
and cast(send_time as date) = '2022-12-07'
group by tel_num