mysql - 平均使用MySQL的天数

Question

我正在尝试获取用户关闭票证到打开“下一张”票证之间的平均天数。

下面是我的 MySQL 表的示例。您会注意到，Mike 创建了 3 个工单，相隔 9 天(7 月 1 日至 7 月 10 日)和 5 天(7 月 10 日至 7 月 15 日)，平均为 7 天。我似乎不知道如何查找最后解决的日期，有人有什么想法吗？这是我到目前为止所拥有的:

SELECT 
  Name, 
  Created, 
  Resolved, 
  avg(datediff("Last Ticket Resolved", created) AS last_tket_open 
FROM 
  MyTable 
WHERE 
  Name='Mike'


Name   Created  Resolved
----   -------  --------
Mike   July  1   July  1
Jill   July  2   July  3    
Mike   July 10   July 10
Harry  July 11   July 11
Mike   July 15   July 15

Answer 1

SELECT a.name, 
       AVG(DATEDIFF(a.resolved, b.created)) AS avgdays
FROM
(
   SELECT name, resolved, @val1:=@val1+1 AS rn
   FROM tbl
   CROSS JOIN (SELECT @val1:=0) val1_init
   WHERE name = 'Mike'
   ORDER BY resolved
) a
INNER JOIN
(
   SELECT created, @val2:=@val2+1 AS rn
   FROM tbl
   CROSS JOIN (SELECT @val2:=1) val2_init
   WHERE name = 'Mike'
   ORDER BY resolved
) b ON a.rn = b.rn

<小时/>

SQLFiddle Demo