mysql - 在特定日期用sql/PHP计算两个不同行/列中两个数字的平均值、方差和标准差

Question

我有一个具有以下结构的数据库:

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 的 DATE-Functions，但我无法完成它...有人可以帮助我吗？还是我必须编写一段 PHP 代码来处理这个问题？

Answer 1

是否像将日期添加到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

如果存储为时间戳，请参阅 Marty 的评论。