mysql - mysql日期之间的平均差距

我有表客户端，其中有一列“约会”

------------------------------
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);
}

最佳答案

一条鱼：

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 |
+-----------+

关于mysql - mysql日期之间的平均差距，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50772778/