MySQL：获取时间差异的平均值？ [英] MySQL: Get average of time differences?

问题描述

我有一个名为Sessions的表，其中包含两个datetime列：开始和结束。

I have a table called Sessions with two datetime columns: start and end.

对于每一天（YYYY-MM-DD），可以有许多不同的开始和结束时间（HH：ii：ss）。我需要找到这些开始和结束时间之间所有差异的日均值。

For each day (YYYY-MM-DD) there can be many different start and end times (HH:ii:ss). I need to find a daily average of all the differences between these start and end times.

几行的例子是：

start: 2010-04-10 12:30:00 end: 2010-04-10 12:30:50
start: 2010-04-10 13:20:00 end: 2010-04-10 13:21:00
start: 2010-04-10 14:10:00 end: 2010-04-10 14:15:00
start: 2010-04-10 15:45:00 end: 2010-04-10 15:45:05
start: 2010-05-10 09:12:00 end: 2010-05-10 09:13:12
...

2010-04-10的时差（以秒为单位）将是：

The time differences (in seconds) for 2010-04-10 would be:

50
60
300
5

2010-04-10的平均值为103.75秒。我想我的查询返回如下：

The average for 2010-04-10 would be 103.75 seconds. I would like my query to return something like:

day: 2010-04-10 ave: 103.75
day: 2010-05-10 ave: 72
...

我可以得到时差按开始日期分组，但我不知道如何获得平均值。我尝试使用AVG功能，但我认为它只能直接在列值（而不是另一个聚合函数的结果）上工作。

I can get the time difference grouped by start date but I'm not sure how to get the average. I tried using the AVG function but I think it only works directly on column values (rather than the result of another aggregate function).

这是我所拥有的： p>

This is what I have:

SELECT
    TIME_TO_SEC(TIMEDIFF(end,start)) AS timediff
FROM
    Sessions
GROUP BY
    DATE(start)

有没有办法获得每个开始日期组的timediff？我是新的聚合功能，所以也许我误会了一些东西。如果你知道一个替代解决方案，请分享。

Is there a way to get the average of timediff for each start date group? I'm new to aggregate functions so maybe I'm misunderstanding something. If you know of an alternate solution please share.

我可以随时使用它来临时计算平均值，但是我想知道是否有办法它在MySQL中，所以我可以避免运行一堆循环。

I could always do it ad hoc and compute the average manually in PHP but I'm wondering if there's a way to do it in MySQL so I can avoid running a bunch of loops.

谢谢。

推荐答案

SELECT  DATE(start) AS startdate, AVG(TIME_TO_SEC(TIMEDIFF(end,start))) AS timediff
FROM    Sessions
GROUP BY
        startdate

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