0

I have a SQLite database that I'm trying to use data from, basically there are multiple sensors writing to the database. And I need to join one row to the proceeding row to calculate the value difference for that time period. But the only catch is the ROWID field in the database can't be used to join on anymore since there are more sensors beginning to write to the database.

In SQL Server it would be easy to use Row_Number and partition by sensor. I found this topic: How to use ROW_NUMBER in sqlite and implemented the suggestion:

select id, value , 
       (select count(*) from data b where a.id >= b.id and b.value='yes') as cnt 
from data a where  a.value='yes';

It works but is very slow. Is there anything simple I'm missing? I've tried to join on the time difference possibly, create a view. Just at wits end! Thanks for any ideas!

Here is sample data:

ROWID - SensorID - Time -     Value
1        2         1-1-2015     245
2        3         1-1-2015     4456
3        1         1-1-2015     52
4        2         2-1-2015     325
5        1         2-1-2015     76
6        3         2-1-2015     5154

I just need to join row 6 with row 2 and row 3 with row 5 and so forth based on the sensorID.

Community
  • 1
  • 1
BSmith
  • 33
  • 4

1 Answers1

0

The subquery can be sped up with an index with the correct structure. In this case, the column with the equality comparison must come first, and the one with unequality, second:

CREATE INDEX xxx ON MyTable(SensorID, Time);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for this! I created the index: `CREATE INDEX xxx ON SensorResults (SensorID, ROWID);` And am using this query, but it's still pretty slow. Is there a way to force it to use the index? `select rowid, sensord, time, value, (select count(*) from sensorresults b where a.rowid >=b.rowid and b.sensorid=2) as cnt from sensorresults a where a.sensorid=2` – BSmith Jul 15 '15 at 15:29
  • Alright thanks, It's doing a search for the initial query using the index, and then a search for the subquery using that index. So I'm not sure how much better it will get, bummer. – BSmith Jul 15 '15 at 19:10