mysql - 如何计算“每个item_id从首次访问到购买的平均时间”

从第一次就诊到购买每件物品的平均时间？
我有两张桌子，
event和trans。

CREATE TABLE `event` (
  `event_name` varchar(40) DEFAULT NULL,
  `user_id` varchar(40) DEFAULT NULL,
  `time` timestamp
);

CREATE TABLE `trans` (
  `item_id` varchar(40) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `user_id` varchar(40) DEFAULT NULL,
  `time` timestamp
)

假定事件表的值（多个用户访问）：

|visit |a1 |2016-09-14 22:48:14 |
|visit |a2 |2016-09-14 22:48:28 |
|visit |a3 |2016-09-14 22:48:45 |
|visit |a1 |2016-09-15 15:10:39 |
|visit |a2 |2016-09-15 15:11:08 |
|visit |a1 |2016-09-15 15:12:34 |

对于trans table

|i1 |1 |100 |a1 |2016-09-15 15:12:22 |
|i2 |2 |100 |a2 |2016-09-15 15:13:17 |
|j1 |1 |140 |a1 |2016-09-15 16:12:22 |
|j4 |3 |150 |a3 |2016-09-15 16:13:17 |

我编写了一个查询来查找用户首次访问和首次购买之间的平均时间。

SELECT AVG(timestampdiff(second, e.mintime, t.mintime))
FROM (SELECT user_id, min(time) AS mintime
      FROM event e
      GROUP BY user_id
     ) e JOIN
     (SELECT user_id, min(time) AS mintime
      FROM trans t
      GROUP BY user_id
     ) t
     ON e.user_id = t.user_id;

但现在我想找出从第一次访问到购买每件物品的平均时间。
对于上述数据，我如何编写查询来查找。。。
从第一次就诊到购买每件物品的平均时间。

最佳答案

这个怎么样？这是我能想到的最接近的东西。

SELECT *, timestampdiff(second, e.`time`, t.`time`) as difference
FROM  `event` e
left join trans as t ON t.user_id = e.user_id  -- same use
    AND YEAR(e.`time`) =  YEAR(t.`time`) -- same year
    AND MONTH(e.`time`) =  MONTH(t.`time`) -- same month
    AND DAY(e.`time`) =  DAY(t.`time`) -- same day
    AND  t.`time` between e.`time` AND ADDTIME( e.`time`,"0:10:00") -- ten minutes RANGE
order by  e.user_id, YEAR(e.`time`), MONTH(e.`time`), DAY(e.`time`), e.`time`
;

我不知道哪个是父表。尝试切换左联接表声明并重新修改范围条件。