在没有其他行干扰%(mod)操作符的情况下快速选择数据

Question

我有一个记录历史结果到文件的数据库。

将结果记录到由

创建的表中

CREATE TABLE Data (TimeID INTEGER NOT NULL REFERENCES StartTimes, 
                   TimeOffset REAL,
                   SensorID INTEGER NOT NULL REFERENCES Sensors,
                   Value REAL);

我有一个超过(TimeID, TimeOffset)的索引，并且可以根据需要创建更多的索引——我的INSERT性能目前很好，希望能保持下去。

我读取数据，并使用

将数据放在图表上

SELECT SensorID, TimeOffset, Value from Data 
WHERE ((TimeID = %d AND TimeOffset BETWEEN %f AND %f) AND 
(%s));

其中%s被字符串替换为"(SensorID=1 and ROWID%5=0) or (SensorID=2 and ROWID%5000=0)"，%d是准备好的语句中的一个常数值，%f与图的极限相对应。

我的问题是，如果我有两个传感器记录一个相等的速率，每个单点，如果我的值使用的模数变成了例如2，那么我从一个传感器得到所有的数据，而没有从另一个(我想?)。

我试过使用

SELECT COUNT(lesser.TimeOffset) as NewID, D.TimeOffset from Data as D
LEFT OUTER JOIN Data as lesser on D.rowid > lesser.rowid and D.TimeID=%d and D.SensorID=1
GROUP BY D.TimeOffset;

但是在命令行上(使用TimeID的示例值)，命令行处理了很长时间(可能我完全写错了语句)。

我如何使用这些类型的约束(TimeID=?之间的时间偏移?和?)和快速抓取的SensorID集合，不受其他测量的写作影响?

理想情况下，我希望能够使用MIN(x)和MAX(x)在给定的时间偏移范围内找到最高和最低的值，但似乎我没有办法快速做到这一点(我可以解决-我是新的SQL(ite)虽然)。因此，我选择了%操作符。

编辑-示例表内容:

SELECT * from Data LIMIT 10;
TimeID|TimeOffset|SensorID|Value
1|0.0|1|0.464069664478302
1|0.0|2|0.0
1|0.00100000004749745|2|0.00251327152363956
1|0.0020000000949949|2|0.00502652721479535
1|0.00300000002607703|2|0.00753975100815296
1|0.00400000018998981|2|0.010052926838398
1|0.00499999988824129|2|0.0125660402700305
1|0.00600000005215406|2|0.0150790736079216
1|0.00700000021606684|2|0.0175920110195875
1|0.00800000037997961|2|0.0201048385351896

使用我的合成数据(用于测试实现)，我有一个0和1之间的随机数作为1Hz的传感器#1，以及1kHz的正弦波作为#2。(这些不是一次写一点，但我仍然觉得我可以用rowid%x来射击自己的脚)

TimeOffset测量从数据采集开始的时间，而TimeID引用StartTimes表(YmDHMS等)中的StartTime。

理由

由于屏幕上的像素通常比在相关时间范围内可放置的点的数量少得多，因此只选择一些点来减少我试图从数据库中读取的数据量是明智的。

使用%操作符可以得到均匀间隔的点，但在某些情况下很容易失去数据的形状。

使用最小/最大抽取可以防止这种情况，但要使用它，你需要找到每个时间跨度对应的最小值和最大值，例如像素的宽度。如果我不在查询中这样做，那么我必须从数据库中读取图边界之间的所有数据，其中我可能只绘制了一些小(低于1%)的部分。

Answer 1

您可以尝试使用子查询来计算数据采集系列中条目的索引，但是这样会很慢。

过滤掉不需要的行最有效的方法可能是在你的程序中这样做，也就是说，以正确的顺序查询所有的行，然后只遍历所有的行，除了每个n-th。(这需要排序，但ORDER BY TimeID, TimeOffset不需要任何开销，因为结果已经根据索引排序了。)

您可以通过使用覆盖索引进一步改进查询，但是对于这个表，聚集索引(在前三列上使用PK)会更好。此外，使列的顺序为TimeID, SensorID, TimeOffset，因为在索引中只有最右边使用的列才能优化不等式。