假设下表:
id date
----------------
1 2010-12-12
2 2010-12-13
3 2010-12-18
4 2010-12-22
5 2010-12-23
如何仅使用MySQL查询来查找这些日期之间的平均时间间隔?
例如,此表上的计算将为
(
( 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
发布于 2010-11-30 18:17:29
直观地说,您所问的应该等于第一个日期和最后一个日期之间的间隔,再除以日期数减1。
让我更彻底地解释一下。假设日期是一条线上的点(+
是存在的日期,-
是缺少的日期,第一个日期是12号,为了说明起见,我将最后一个日期更改为12号):
++----+---+-+
现在,你真正想做的是,在这些行之间均匀地间隔你的日期,并找出它们之间的长度:
+--+--+--+--+
为此,您只需取最后一天和第一天之间的天数,在本例中为24 - 12 = 12,然后将其除以必须空出的间隔数,在本例中为4:12 / 4 = 3
。
使用MySQL查询
SELECT DATEDIFF(MAX(dt), MIN(dt)) / (COUNT(dt) - 1) FROM a;
这适用于此表(使用您的值,它将返回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');
发布于 2010-11-30 17:44:34
如果If是均匀递增的,没有间隔,请在id+1上将表连接到自身:
SELECT d.id, d.date, n.date, datediff(d.date, n.date)
FROM dates d
JOIN dates n ON(n.id = d.id + 1)
然后根据需要进行分组和平均。
如果ids不一致,请先执行内部查询以分配有序的ids。
我猜您还需要添加子查询来获得总行数。
或
创建一个聚合函数,用于跟踪上一个日期、运行的sum和count。您仍然需要从子查询中进行选择,以强制按日期排序(实际上,我不确定MySQL中是否保证了这一点)。
仔细想想,这是一种更好的方式。
和更简单的
只是注意到Vegard的解决方案要好得多。
发布于 2010-11-30 18:29:36
以下查询返回正确的结果
SELECT AVG(
DATEDIFF(i.date, (SELECT MAX(date)
FROM intervals WHERE date < i.date)
)
)
FROM intervals i
但是它运行的是一个依赖子查询,如果没有索引,并且有大量的行,那么这样的查询效率可能非常低。
https://stackoverflow.com/questions/4312019
复制相似问题