使用sqlite / PHP获得两个不同行/列中两个数字的方差和标准差

发布时间：2020-12-12 18:57:55 所属栏目：百科来源：网络整理

导读：我有一个具有以下结构的SQLite数据库： rowid ID startTimestamp endTimestamp subject1 00:50:c2:63:10:1a 1000 1090 entrance2 00:50:c2:63:10:1a 1100 1270 entrance3 00:50:c2:63:10:1a 1300 1310 door14 00:50:c2:63:10:1a 1370 1400 entrance... 我在

我有一个具有以下结构的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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!