MySQL：记录间的平均间隔 [英] MySQL: Average interval between records

问题描述

假设这个表：

id    date
----------------
1     2010-12-12
2     2010-12-13
3     2010-12-18
4     2010-12-22
5     2010-12-23

如何查找这些日期之间的平均间隔，仅使用MySQL查询？

例如，此表上的计算将为

For instance, the calculation on this table will be

  (
    ( 2010-12-13 - 2010-12-12 )
  + ( 2010-12-18 - 2010-12-13 )
  + ( 2010-12-22 - 2010-12-18 )
  + ( 2010-12-23 - 2010-12-22 )
  ) / 4
----------------------------------
= ( 1 DAY + 5 DAY + 4 DAY + 1 DAY ) / 4
= 2.75 DAY

推荐答案

直观地，你所要求的应该等于第一个和最后一个日期除以日期数减1。

Intuitively, what you are asking should be equivalent to the interval between the first and last dates, divided by the number of dates minus 1.

让我更全面地解释。想象一下，日期是一行上的点（ + 是存在的日期， - 是缺少日期，第一个日期是12日，我把最后一个日期改为12月24日为了说明的目的）：

Let me explain more thoroughly. Imagine the dates are points on a line (+ are dates present, - are dates missing, the first date is the 12th, and I changed the last date to Dec 24th for illustration purposes):

++----+---+-+

现在，你真正想要做的是将这些行之间的日期平衡，并找到它们之间的时间长短：

Now, what you really want to do, is evenly space your dates out between these lines, and find how long it is between each of them:

+--+--+--+--+

为了做到这一点，你只需要在最后一天和第一天之间的天数，在这种情况下是24 - 12 = 12，并将其除以您必须空出的间隔数，在这种情况下为4： 12/4 = 3 。

To do that, you simply take the number of days between the last and first days, in this case 24 - 12 = 12, and divide it by the number of intervals you have to space out, in this case 4: 12 / 4 = 3.

使用MySQL查询

SELECT DATEDIFF(MAX(dt), MIN(dt)) / (COUNT(dt) - 1) FROM a;

这个工作在这个表（你的值返回2.75）：

This works on this table (with your values it returns 2.75):

CREATE TABLE IF NOT EXISTS `a` (
  `dt` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `a` (`dt`) VALUES
('2010-12-12'),
('2010-12-13'),
('2010-12-18'),
('2010-12-22'),
('2010-12-24');

这篇关于MySQL：记录间的平均间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！