mysql - 如何从子选择查询中行之间的时间戳获取平均时间？

Question

此查询返回每天第一次和最后一次调用提交到数据库的日期。现在我想获得在那个时间段内提交的每个调用之间的平均持续时间。但它总是返回 null？？？

为了让它变得非常简单:计算这个的正常方法是将这两个时间之间的小时数除以调用次数。如何在 MYSQL 中执行此操作？

请帮忙!

SELECT DATE_FORMAT(last_call, '%d') AS day, MIN(last_call) AS checkin,
    MAX(last_call) AS checkout, TIMEDIFF(MAX(last_call), MIN(last_call)) AS work_time
    ,(SELECT b.last_call - a.last_call / COUNT(calls.call_id) 
      FROM calls AS a 
      JOIN calls AS b ON b.last_call = (a.last_call + 1) 
     ) AS intv
FROM calls  
GROUP BY EXTRACT( DAY FROM last_call )

Answer 1

谁可能需要这个:

SELECT TIME_TO_SEC(TIMEDIFF(MAX(cl1.last_call), MIN(cl1.last_call))) / COUNT(cl1.call_id)         
   FROM calls AS cl1
   WHERE EXTRACT(DAY FROM cl1.last_call ) = EXTRACT(DAY FROM calls.last_call )
    ) AS 'intvl'     
FROM calls 
WHERE MONTH(last_call) = MONTH(NOW()) LIMIT 1