回复邮件的平均时间 [英] Average Time to Reply to Message

问题描述

可以使用以下列计算回复邮件的平均时间：

  id | ref |客户端| admin |日期| message 
  

• id 是唯一的邮件号码


• ref 是邮件参考号码，不是唯一的（搜索ref，显示对话）


• client 是客户端的ID，如果是客户端消息， >
  
• admin 是admin的ID，如果是管理员信息，否则为0如果不是客户端

datetime 作为消息的时间设置 $


• $ b
  $ b

    1 | 1 | 1 | 0 | 2011-11-07 01:00:00 | ABC 
   2 | 1 | 1 | 0 | 2011-11-07 01:01:00 | DEF 
   3 | 1 | 0 | 1 | 2011-11-07 01:05:00 | abc 
   4 | 2 | 2 | 0 | 2011-11-07 01:10:00 | 123 
   5 | 3 | 1 | 0 | 2011-11-07 01:11:00 | abc 
   6 | 2 | 0 | 1 | 2011-11-07 01:20:00 | a 
   7 | 3 | 0 | 2 | 2011-11-07 02:11:00 | b 
    

  理想情况下寻找客户端消息和管理消息之间的平均时间段，来自同一客户端的2个客户端消息（即，管理员在客户端添加第二个消息之前没有回复第一个消息）。

  
  
  

  （1）= 5分钟，（2）= 10分钟，（3）= 60分钟...平均= 25分钟（1500秒 - 高兴得到工作用秒）

  
  
  

  我不知道如何开始工作....我希望有人可以帮助：S

  解决方案

  p>你的问题很好，但留有解释的空间。这是一个解释：

    SELECT avg（TIMESTAMPDIFF（SECOND，c.c_date，a.a_date）AS avg_time_to_response 
   FROM（
   SELECT ref，min（date）AS c_date 
   FROM tbl 
   WHERE client> 0 
   GROUP BY 1 
  ）c 
   JOIN b $ b SELECT ref，min（date）AS a_date 
   FROM tbl 
   WHERE admin> 0 
   GROUP BY 1 
  ）a USING（ref）
   WHERE a .a_date> c.c_date; 
    

  提供第一个客户发布和

  
  
  

  管理员启动的主题会将结果与负的持续时间混淆，所以我排除了这些。

  
  
  

  读取 manual here 关于 TIMESTAMPDIFF（） 。
  
  _{Thanx to @MrJ和@Vincent指出时间戳的减法错误！}

  
  
  

  code> GROUP BY 1

  
  
  

  我在此处引用手动：

  
  

  在ORDER BY和GROUP
  BY子句中使用列名称，列别名或列位置引用。
  列位置是整数，以1开头：

  
  

  强调我。所以我选择的第一列（ ref ）。只是一个符号的快捷方式。

  
  

  Is it possible to calculate the average time to reply to a message just with the following columns:

  id | ref | client | admin | date | message
  

  • id is the unique message number
  • ref is the message reference number, which is not unique (searching for ref, ordering by date will show a conversation)
  • client is ID of client, if it is a client message, else 0 if not a client
  • admin is ID of admin, if it is an admin message, else 0 if not a client
  • date is set up using datetime being the time of the message
  • message being the message sent

  Example Data:

  1  | 1   | 1      | 0     | 2011-11-07 01:00:00 | ABC
  2  | 1   | 1      | 0     | 2011-11-07 01:01:00 | DEF
  3  | 1   | 0      | 1     | 2011-11-07 01:05:00 | abc
  4  | 2   | 2      | 0     | 2011-11-07 01:10:00 | 123
  5  | 3   | 1      | 0     | 2011-11-07 01:11:00 | abc
  6  | 2   | 0      | 1     | 2011-11-07 01:20:00 | a
  7  | 3   | 0      | 2     | 2011-11-07 02:11:00 | b
  

  Ideally looking for the average time period between a client message and an admin message, though if there are 2 client messages from the same client (ie. admin didn't reply to first message before client added their 2nd message) with the same ref.

  From example, time for (1) = 5 minutes, (2) = 10 minutes, (3) = 60 minutes ... average = 25 minutes (1500 seconds - happy to get work with seconds)

  I'm not sure how to even begin working on this.... I do hope someone can help :S

  解决方案

  Your question is well formulated but leaves room for interpretation. This is one interpretation:

  SELECT avg(TIMESTAMPDIFF(SECOND, c.c_date, a.a_date) AS avg_time_to_response
  FROM   (
      SELECT ref, min(date) AS c_date
      FROM   tbl
      WHERE  client > 0
      GROUP  BY 1
      ) c
  JOIN  (
      SELECT ref, min(date) AS a_date
      FROM   tbl
      WHERE  admin > 0
      GROUP  BY 1
      ) a USING (ref)
  WHERE a.a_date > c.c_date;
  

  Gives you the average time that passes between the first client posting and the first admin posting per thread (message reference number).

  Unanswered messages are ignored. Threads started by Admins would confuse the result with negative durations, so I excluded those. Only the first response time per thread goes into the result. Additional postings on the same thread are ignored here.

  Read the manual here about TIMESTAMPDIFF().
  _{Thanx to @MrJ and @Vincent for pointing out the mistake with the subtraction of timestamps!}

  Concerning GROUP BY 1

  I quote the manual here:

  Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1:

  Emphasis mine. So I group on the first column that is selected (ref in both cases). Just a notational shortcut.

  这篇关于回复邮件的平均时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！