There is a need to select a subsequence of data from the table, based on the difference between the timestamp of neighboring elements. you need to select such a sequence, the difference between the timestamp elements of which does not exceed 5 minutes

Understand how to choose the difference

select record_timestamp - (select record_timestamp from short_summary as ss2 where (short_summary.record_timestamp - ss2.record_timestamp) / 60 <= 5 limit 1 ) as diff, * from short_summary

We get all the elements with a difference of 5 minutes. that is, as many subsequences as there are in the table. But since the sequence needs one, I need an operator similar to LIMIT, which would terminate the sample under a certain condition, for example, if the id is not adjacent.

  • one
    the sequence needs one. What criteria does it define? need an operator similar to LIMIT Pay attention to recursive CTE. - Akina
  • @Akina A sequence that includes all elements, starting with the last one, up to that element (not including) the difference between which and the previous one is more than 5 minutes. That is, conditionally, I need to terminate the sample when this condition is reached - iamthevoid
  • That is, from a record with a maximum record_timestamp in the direction of its reduction to the "first" gap for more than 5 minutes? Well, CTE will choose this, not really straining ... - Akina

2 answers 2

Schematically (I don’t want to install SQLite for the sake of syntax checking):

 WITH RECURSIVE cte(id, record_timestamp) AS ( SELECT id, record_timestamp FROM short_summary ORDER BY record_timestamp DESC LIMIT 1 UNION ALL SELECT s.id, s.record_timestamp FROM short_summary s, cte WHERE s.record_timestamp < cte.record_timestamp AND s.record_timestamp >= cte.record_timestamp - INTERVAL 5 MINUTE ORDER BY record_timestamp DESC LIMIT 1 ) SELECT * FROM cte;

If SQLite in the recursive part of the CTE uses the entire current set, and not just the recursion obtained in the previous step, replace the cte table in the recursive subquery with the subquery receiving the oldest record from it.

Ps. I do not know if SQLite supports the INTERVAL statement. If not, replace with a valid syntax expression.

  • Thanks, I'll try now. Already itself something like this, based on blog.expensify.com/2015/09/25/… - iamthevoid
  • @iamthevoid Yeah, the approach is the same ... but pay special attention to the explanation. Perhaps, in the recursive part, instead of the cte table, cte will cte to use something like (select record_timestamp from cte order by record_timestamp asc limit 1) as cte1 ... - Akina
  • Hmm, the question is a little off topic - but for some reason when I use order by and limit in a query before union all, the sample swears that you cannot use them BEFORE union all, only after. If I remove the union all and the selection after it, then it correctly returns one string. I don't understand what's the matter - iamthevoid
  • Perhaps some SQLite troubles. Try wrapping the initial query: SELECT * FROM (... LIMIT 1) . - Akina
  • got to the bottom of it, one problem, the last ORDER BY record_timestamp DESC LIMIT 1 it applies to union all, but not to the request, I had to remove and replace union all with union, then all the same results collapse into one and it turns out a great sign. - iamthevoid

Another option (did not check)

 SELECT * FROM short_summary ss1 JOIN (SELECT record_timestamp, (SELECT record_timestamp FROM short_summary ss2 WHERE ss2.record_timestamp > ss1.record_timestamp ORDER BY ss2.record_timestamp LIMIT 1) record_timestamp_next FROM short_summary ss1 WHERE (SELECT record_timestamp FROM short_summary ss2 WHERE ss2.record_timestamp > ss1.record_timestamp ORDER BY ss2.record_timestamp LIMIT 1) - record_timestamp /60 <= 5) ss ON ss1.record_timestamp = ss.record_timestamp or ss1.record_timestamp = ss.record_timestamp_next ORDER BY ss1.record_timestamp
  • I do not really want to delve into that, as I solved the problem with cte, but the query itself is killing the sql explorer tightly, and my laptop is starting to cool off) I think it should work, let me retry at leisure - iamthevoid