使用sql / PHP在特定日期计算两个不同行/列的两个数字的平均值，方差和标准偏差 [英] Calculate average, variance and standard deviation of two numbers in two different rows/columns with sql / PHP on specific dates

问题描述

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

I have a Database with the following structure:

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查询，我可以得到endTime和startTime之间的平均差异，按主题和ID排序的以下行，其最小，最大，差异和标准偏差：

With this SQL-Query i can get the average differences between the endTime and the startTime between one row and the following row, sorted by subject and ID, with their min,max,variance and standard deviation:

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

如果我在同一天只有几行，时间较少不同ces，你可以在这个sqlfiddle中看到它：

This works fine, if i have only a few rows on the same day with less time differences, you can see it in this sqlfiddle:

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

但是当我在另外一天有其他数据时，我得到错误值：

But when i have additional data on just a different day, i get bad values:

http：// sqlfiddle .com /＃！2 / 920b6 / 1

因此，我想计算每一天的平均，最小，最大，差异，标准偏差。

Therefore, I want to calculate the avg, min, max, variance, standard deviation for each single day.

我知道有MySQL的DATE函数，但我无法完成...有人可以帮助我吗？或者我必须编写一段可以处理这个的PHP代码？

I know that there are the DATE-Functions of MySQL but i couldn't get it done...can somebody help me? Or do i have to write a piece of PHP code that could handle this?

推荐答案

是否将日期添加到 group by 。这里是应用于MySQL和SQLite的语法，基于日期在结束时间，并假设结束时间存储为datetime：

Is it as easy as adding the date into the group by. Here is syntax that should work in both MySQL and SQLite, basing the date on the end time and assuming the end time is stored as a datetime:

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的评论。

If stored as a time stamp, see Marty's comment.

这篇关于使用sql / PHP在特定日期计算两个不同行/列的两个数字的平均值，方差和标准偏差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！