mysql日期之间的平均间隔
mysql日期之间的平均间隔
提问于 2018-06-09 17:25:52
我有一个表客户，它有一个列“约会”
------------------------------
Clentname | appointmentday |
------------------------------
Jonna | January 22, 2018 |
Jonna | August 31, 2017 |
Jonna | June 27, 2017 |我想找出日期之间的平均间隔，
我在这里找到了一个解决方案，但不起作用。
$intervals = array();
foreach ($lifeSpanArray as $key) {
$newTimeAdd = new DateTime($key["timeAdded"]);
$newTimeRead = new DateTime($key["timeRead"]);
$interval = $newTimeAdd->diff($newTimeRead);
$intervals[] = $interval->days;//get days
}
if(!empty($intervals))
{
$average = average($intervals);
}
function average($arr)
{
return array_sum($arr)/count($arr);
}回答 1
Stack Overflow用户
发布于 2018-06-09 17:36:29
一条鱼：
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(client_id INT NOT NULL
,appointment_date DATE NOT NULL
,PRIMARY KEY(client_id,appointment_date)
);
INSERT INTO my_table VALUES
(1,'2018-01-22'),
(1,'2017-08-31'),
(1,'2017-06-27');
SELECT AVG(diff)
FROM
( SELECT x.*
, MIN(y.appointment_date) next
, DATEDIFF(MIN(y.appointment_date),x.appointment_date) diff
FROM my_table x
JOIN my_table y
ON y.client_id = x.client_id
AND y.appointment_date > x.appointment_date
GROUP
BY x.client_id
, x.appointment_date) n;
+-----------+
| AVG(diff) |
+-----------+
| 104.5000 |
+-----------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50772778
复制相关文章
相似问题