Calculate average, variance and standard deviation

我有以下结构的数据库：

rowid       ID                  startTimestamp   endTimestamp   subject
1           00:50:c2:63:10:1a   ...1000          ...1090        entrance
2           00:50:c2:63:10:1a   ...1100          ...1270        entrance
3           00:50:c2:63:10:1a   ...1300          ...1310        door1
4           00:50:c2:63:10:1a   ...1370          ...1400        entrance
.
.
.

有了这个SQL查询我可以按主题和ID进行排序的结束时间和一行的开始时间和下面一排，之间的平均差异，他们的最小值，最大值，方差和标准差：

SELECT ID,AVG(diff) AS average,
   AVG(diff*diff) - AVG(diff)*AVG(diff) AS variance,
   SQRT(AVG(diff*diff) - AVG(diff)*AVG(diff)) AS stdev,
   MIN(diff) AS minTime,
   MAX(diff) AS maxTime

FROM
(SELECT t1.id, t1.endTimestamp,
        min(t2.startTimeStamp) - t1.endTimestamp AS diff
FROM table1 t1
INNER JOIN table1 t2
ON t2.ID = t1.ID AND t2.subject = t1.subject
AND t2.startTimestamp > t1.startTimestamp  -- consider only later startTimestamps
WHERE t1.subject = 'entrance'
GROUP BY t1.id, t1.endTimestamp) AS diffs
GROUP BY ID

这工作得很好，如果我只有在同一天几行用更少的时间差异，你可以看到它在这个sqlfiddle：

http://sqlfiddle.com/#!2/6de73/1

但是，当我有不同的只是一天额外的数据，我得到错误值：

http://sqlfiddle.com/#!2/920b6/1

因此，我要计算平均，最小值，最大值，方差，对于每一天的标准偏差。

我知道有MySQL的日期函数，但我不能把它做......有人能帮帮我吗？还是我写一段PHP代码，可以处理呢？

它是那么容易，因为添加日期到group by 。下面是语法应该在MySQL和SQLite的工作，立足于结束时间的日期和假设的结束时间存储为日期时间：

SELECT ID, thedate, AVG(diff) AS average,
   AVG(diff*diff) - AVG(diff)*AVG(diff) AS variance,
   SQRT(AVG(diff*diff) - AVG(diff)*AVG(diff)) AS stdev,
   MIN(diff) AS minTime,
   MAX(diff) AS maxTime
FROM (SELECT t1.id, t1.endTimestamp, DATE(endtimestamp) as thedate,
             min(t2.startTimeStamp) - t1.endTimestamp AS diff
      FROM table1 t1 INNER JOIN
           table1 t2
           ON t2.ID = t1.ID AND t2.subject = t1.subject AND
              t2.startTimestamp > t1.startTimestamp  -- consider only later startTimestamps
     WHERE t1.subject = 'entrance'
     GROUP BY t1.id, t1.endTimestamp
    ) AS diffs
GROUP BY ID, thedate

如果存储为一个时间戳，看到马蒂的评论。