SQL:查找每个跑步者两次跑步之间的平均天数 [英] SQL: Finding the average number of days between runs for each runners

问题描述

所以如果我们有下表:

runner  ran
Carol   2011-02-01
Alice   2011-02-01
Bob     2011-02-01
Carol   2011-02-02
Bob     2011-02-02
Bob     2011-02-03
Bob     2011-02-04
Carol   2011-02-07
Alice   2011-02-08

我如何编写查询(没有任何子查询)来查找每个跑步者在两次运行之间必须等待的平均天数(即 Carol 等了 1 天，然后 5 天，所以平均值是 3；Bob 每天跑步；Alice 等了7 天)?

How can I write a query (without any subquery) to find the average number of days each runner has to wait between runs (i.e, Carol waited 1 day, then 5, so average is 3; Bob runs everyday; Alice waited 7 days)?

我正在考虑对表本身进行连接，然后找到每个跑步者的最大值和最小值，减去它们并除以运行次数 - 1.但是我如何在没有任何子查询的情况下组合所有这些?

I was thinking about a join on the table itself, then finding the max and min for each runner, subtracting them and dividing by the number of runs - 1. But how do I combine all these without any subquery?

推荐答案

Sorin，公平地说，你已经有了答案 - (max-min)/(count-1) 确实是正确的无需详细说明运行相距多远.

Sorin, to be fair, you already have the answer - (max-min)/(count-1) is indeed correct without going into the specifics of how far apart the runs are.

select runner, datediff(max(ran),min(ran)) / (count(ran)-1)
from running
group by runner;

注意:MySQL 会将 X/0(对于一个跑步者只有一条记录的情况)变成 NULL，因为它不能被 0 整除.

Note: MySQL will turn X / 0 (for where there is only one record for a runner) into NULL because it is indivisable by 0.

这篇关于SQL:查找每个跑步者两次跑步之间的平均天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！