存储在数据库中的平均操作时间 [英] Average time of operations stored in the database

问题描述

我需要计算存储在数据库中的所有操作的平均时间.我存储操作的表如下所示:

I need to calculate the average time of all the operations stored in the database. The table I store operations in looks as follows:

 creation time       | operation_type | operation_id
 2017-01-03 11:14:25 | START          | 1
 2017-01-03 11:14:26 | START          | 2
 2017-01-03 11:14:28 | END            | 2
 2017-01-03 11:14:30 | END            | 1

在这种情况下，操作 1 用了 5 秒，操作 2 用了 2 秒完成.

In this case operation 1 took 5 seconds and operation 2 took 2 seconds to finish.

如何计算 MySQL 中这些操作的平均值?

How can I calculate the average of these operations in MySQL?

似乎 operation_id 不需要是唯一的 - 给定的操作可能会被执行多次，所以该表可能如下所示:

It seems that operation_id doesn't need to be unique - given operation may be executed several times, so the table might look as follows:

 creation time       | operation_type | operation_id
 2017-01-03 11:14:25 | START          | 1
 2017-01-03 11:14:26 | START          | 2
 2017-01-03 11:14:28 | END            | 2
 2017-01-03 11:14:30 | END            | 1
 2017-01-03 11:15:00 | START          | 1
 2017-01-03 11:15:10 | END            | 1

我应该在查询中添加什么来正确计算所有这些操作的平均时间?

What should I add in the query to properly calculate the average time of all these operations?

推荐答案

我不确定是否需要子查询...

I'm not sure that a subquery is necessary...

SELECT AVG(TIME_TO_SEC(y.creation_time)-TIME_TO_SEC(x.creation_time)) avg_diff
  FROM my_table x 
  JOIN my_table y 
    ON y.operation_id = x.operation_id 
   AND y.operation_type = 'end' 
 WHERE x.operation_type = 'start';

这篇关于存储在数据库中的平均操作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！