mysql 计算两次来电间隔时间

 

第一步：计算来电顺序排行

SELECT a.id,a.caller_number,a.start_time,IF (@pxydm=a.caller_number,@rank :=@rank+1,@rank :=1) AS rank, @pxydm :=a.caller_number AS callerNumber FROM (
SELECT id,caller_number,start_time FROM cc_service_history WHERE caller_number IS NOT NULL ORDER BY caller_number DESC ,start_time ASC) a

结果：

 

 

第二步：计算每通电话上次来电时间

SELECT 
t.id,
t1.id as last_id,
t.callerNumber,
t.start_time AS start_time,
t1.start_time AS last_start_time ,
t.rank
FROM 
(
SELECT a.id,a.caller_number,a.start_time,IF (@pxydm=a.caller_number,@rank :=@rank+1,@rank :=1) AS rank, @pxydm :=a.caller_number AS callerNumber FROM (
SELECT id,caller_number,start_time FROM cc_service_history WHERE caller_number IS NOT NULL ORDER BY caller_number DESC ,start_time ASC) a
) t 
LEFT JOIN 
(
SELECT a.id,a.caller_number,a.start_time,IF (@pxydm=a.caller_number,@rank :=@rank+1,@rank :=1) AS rank, @pxydm :=a.caller_number AS callerNumber FROM (
SELECT id,caller_number,start_time FROM cc_service_history WHERE caller_number IS NOT NULL ORDER BY caller_number DESC ,start_time ASC) a
) t1
 ON 
 (t.caller_number=t1.caller_number and t.rank= t1.rank+1)

结果：

 

 第三步：计算上次来电时间间隔

SELECT 
t.id,
t1.id as last_id,
t.callerNumber,
t.start_time AS start_time,
t1.start_time AS last_start_time ,
t.rank,
TIMESTAMPDIFF(second,t1.start_time,t.start_time) diff
FROM 
(
SELECT a.id,a.caller_number,a.start_time,IF (@pxydm=a.caller_number,@rank :=@rank+1,@rank :=1) AS rank, @pxydm :=a.caller_number AS callerNumber FROM (
SELECT id,caller_number,start_time FROM cc_service_history WHERE caller_number IS NOT NULL ORDER BY caller_number DESC ,start_time ASC) a
) t 
LEFT JOIN 
(
SELECT a.id,a.caller_number,a.start_time,IF (@pxydm=a.caller_number,@rank :=@rank+1,@rank :=1) AS rank, @pxydm :=a.caller_number AS callerNumber FROM (
SELECT id,caller_number,start_time FROM cc_service_history WHERE caller_number IS NOT NULL ORDER BY caller_number DESC ,start_time ASC) a
) t1
 ON 
 (t.caller_number=t1.caller_number and t.rank= t1.rank+1)

结果:

第三步中使用到了 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 函数 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差

unit 参数 支持以下：

second 秒
minute 分钟
hour 小时
day 天
week 周
month 月
quarter 季度
year 年