SQL 按第三个字段分组的两个日期之间的平均差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14895782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Average difference between two dates, grouped by a third field?
提问by Nick Foote
So say we have 3 fields, username, start_date, end_date
所以说我们有 3 个字段， username, start_date, end_date
Users start and stop multiple records, eg below bob has started and stopped two records.
用户启动和停止多条记录，例如下面的bob 已经启动和停止了两条记录。
bob 1/2/13 11:00 1/2/13 13:00
Hyman 1/2/13 15:00 1/2/13 18:00
bob 2/2/13 14:00 1/2/13 19:00
I need to know the average time taken (ie diff between start and end), in hours, for each user (ie group by user, not just for each row).
我需要知道每个用户的平均时间（即开始和结束之间的差异），以小时为单位（即按用户分组，而不仅仅是每一行）。
I can't quite get my head around how to do the diff, average AND group by? Any help?
我不太明白如何进行差异、平均和分组？有什么帮助吗？
回答by Bohemian
You don't specify the granularity you want for the diff. This does it in days:
您没有为差异指定所需的粒度。这在几天内完成：
select username, avg(end_date - start_date) as avg_days
from mytable
group by username
If you want the difference in seconds, use datediff():
如果您想要以秒为单位的差异，请使用datediff()：
select username, avg(datediff(ss, start_date, end_date)) as avg_seconds
...
datediff can measure the diff in any time unit up to years by varying the first parameter, which can be ss, mi, hh, dd, wk, mm or yy.
datediff 可以通过改变第一个参数（可以是 ss、mi、hh、dd、wk、mm 或 yy），以任何时间单位测量差异，最长可达年。
回答by Marc Fischer
SELECT username, AVG(TIMESTAMPDIFF(HOUR, start_date, end_date))
FROM table
GROUP BY username
