CODEWITHSUNDEEP
androidsqlite
Mark
Mark

Reputation: 1400

SQLITE return only matching consecutive row using WHERE

My Android sqlite db has two fields plus _id and I am looking for an SQL query that includes this:

SELECT COUNT(*) from table WHERE datetime>? AND datetime<? AND value=1 ORDER BY datetime ASC

but would only return rows or count where value=1, consecutively for 4 minutes

datetime                value
...
2014-11-21 11:00:00     0
2014-11-21 11:01:00     0
2014-11-21 11:02:00     1
2014-11-21 11:03:00     1
2014-11-21 11:04:00     0
2014-11-21 12:05:00     0
2014-11-21 11:06:00     1
2014-11-21 11:07:00     1
2014-11-21 11:08:00     1
2014-11-21 11:09:00     1
2014-11-21 11:10:00     0
...

Using the example data above it should only returned a count of 4, 4 consecutive rows not all 6 where value=1:

2014-11-21 11:06:00     1
2014-11-21 11:07:00     1
2014-11-21 11:08:00     1
2014-11-21 11:09:00     1

Is this possible with SQL and just the two fields, and if so any clues as to how?

(And if it makes any difference my datetime data is being held as long milliseconds)

Upvotes: 0

Views: 368

Answers (1)

sberezin
sberezin

Reputation: 3296

I've spent some time pondering on cute way to do your task. I don't think there is efficient way to do it as a query.
So I think you should return to good old cursor and do it like this (and I believe it will be the fastest and the simplest way):

SELECT * from table WHERE value=1 ORDER BY datetime

And then run through fetched records (in pseudocode):

int count=0;
int intervalLen=0;

if (move to first) 
do {
  if(get(value)==1){
    if(intervalLen==4)
      count++;
    intervalLen++;

  } else 
    intervalLen=0;

} while (move to next)

Note that it counts uninterrupted intervals of value=1 not less than 4 records in a row.
If you have to consider exact time you should change it a bit

Upvotes: 1

Related Questions