SQL计算出总行之间的平均时间差 [英] SQL Work out the average time difference between total rows

问题描述

我搜索了SO，似乎找不到适合我的答案的问题.我有一张表，其中有近200万行，每行都有一个MySQL日期格式的字段.

I've searched around SO and can't seem to find a question with an answer that works fine for me. I have a table with almost 2 million rows in, and each row has a MySQL Date formatted field.

我想计算出插入行的频率(以秒为单位)，因此使用SQL查询计算出所有行的日期之间的平均差.

I'd like to work out (in seconds) how often a row was inserted, so work out the average difference between the dates of all the rows with a SQL query.

有什么想法吗?

-编辑-

这是我的桌子的样子

id, name, date (datetime), age, gender

推荐答案

如果您想知道插入行的频率(平均)，我认为不需要计算所有差异.您只需要总结相邻行之间的差异(基于时间戳)，然后将结果除以求和数即可.

If you want to know how often (on average) a row was inserted, I don't think you need to calculate all the differences. You only need to sum up the differences between adjacent rows (adjacent based on the timestamp) and divide the result by the number of the summands.

公式

((T1-T0) + (T2-T1) + … + (TN-TN-1)) / N

显然可以简化为

(TN-T0) / N

因此，查询将如下所示:

So, the query would be something like this:

SELECT TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / (COUNT(*) - 1)
FROM atable

确保行数大于1，否则您将得到除以零的错误.不过，如果您愿意，您可以通过一个简单的技巧来防止该错误:

Make sure the number of rows is more than 1, or you'll get the Division By Zero error. Still, if you like, you can prevent the error with a simple trick:

SELECT
  IFNULL(TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / NULLIF(COUNT(*) - 1, 0), 0)
FROM atable

现在，您可以安全地对只有一行的表运行查询.

Now you can safely run the query against a table with a single row.

这篇关于SQL计算出总行之间的平均时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！