mysql - 如何查询多行的平均时差？

Question

我有下表，基本上是扫描日志。

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| location_id   | int(10) unsigned | YES  | MUL | NULL    |                |
| code          | varchar(255)     | YES  |     | NULL    |                |
| created       | datetime         | YES  |     | NULL    |                |
| last_modified | datetime         | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

这是一些示例数据的简单示例。 location_id 为1 为入口，location_id 为2 为导出。我想知道一个人(一个代码)从入口走到导出的平均时间。

+----+-------------+------+---------------------+---------------+
| id | location_id | code | created             | last_modified |
+----+-------------+------+---------------------+---------------+
| 1  | 1           | 0005 | 2014-10-03 10:01:56 | NULL          |
| 2  | 1           | 0006 | 2014-10-03 10:03:08 | NULL          |
| 3  | 2           | 0005 | 2014-10-03 10:10:16 | NULL          |
| 4  | 2           | 0006 | 2014-10-03 10:10:18 | NULL          |
+----+-------------+------+---------------------+---------------+ 

我不确定我需要为这个查询做什么样的连接。有什么想法吗？

Answer 1

我会用相关的子查询来做到这一点。对于带有“1”的每一行，您需要下一行具有相同的“代码”和“2”:

select t.*,
       (select t2.created
        from table t2
        where t2.code = t.code and
              t2.id > t.id and
              t2.location_id = 2 and
        order by t2.id desc
        limit 1
       ) as exitdte
from table t;

然后您可以使用 timestampdiff() 之类的方法来获取时间差异，并使用 avg() 来获取适当的平均值。

出于性能原因，您应该在 table(code, location, id, created) 上有一个索引。