我有一个具有以下结构的sqlite数据库：
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 . . .
我在这里准备了一个sqlfiddle：http://sqlfiddle.com/#!2/fe8c6/2
使用此sql-Query,我可以获得endTime和一行与下一行之间的startTime之间的平均差异,按主题和ID排序：
SELECT
id,( MAX(endtimestamp) - MIN(startTimestamp)
- SUM(endtimestamp-startTimestamp)
) / (COUNT(*)-1) AS averageDifference
FROM
table1
WHERE ID = '00:50:c2:63:10:1a'
AND subject = 'entrance'
GROUP BY id;
我的问题：要计算平均值是没有问题的,这就是查询.但我怎么能
得到这个值的标准差和方差？
解决方法
首先通过将表连接到自身并按ID分组来找到感兴趣的时间差,然后找到平均值,方差为V(x)= E(x ^ 2) – (E(x))^ 2,标准差为sqrt (V)给出
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
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
