Actually it is slower than I thought, taking some 90 secs for only 7000 records. Will if any compound index can speed this up.
RBS On Tue, Nov 17, 2015 at 12:09 AM, Igor Tandetnik <igor at tandetnik.org> wrote: > On 11/16/2015 6:37 PM, Bart Smissaert wrote: > >> Now, what I need is for every row the count of preceding rows where >> the ID is the same as the ID of the current row, the date is less than the >> date of the current row and the value is >= 10 and <= 20. If the value is >> outside >> this range then it should stop the count for that current row. If the >> value >> of the current row is outside this 10 to 20 range than the result is 0 and >> there >> is no need to count preceding rows. >> > > Personally, I'd do it in application code. It can be done in single pass. > If you insist on pure SQL solution, then something like this: > > select ID, DATE, VALUE, > (select count(*) from TEST prev > where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE > > coalesce((select max(DATE) from TEST boundary > where boundary.ID = T.ID and boundary.DATE <= T.DATE > and not(boundary.VALUE between 10 and 20)), '') > ) as COUNT_PRECEDING_IN_RANGE > from TEST T; > > Performance will likely be, shall we say, less than stellar. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
