SQL: Finding the average number of days between runs for each runners

So if we have the following table given:

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

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

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, 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;

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

链接地址: http://www.djcxy.com/p/31824.html

上一篇: MS SQL查询来计算平均每月唯一访问者数

下一篇: SQL：查找每个跑步者的平均跑步天数