With a compound index on ID and DATE performance is fine. Thanks again. RBS
On Tue, Nov 17, 2015 at 12:45 AM, Bart Smissaert <bart.smissaert at gmail.com> wrote: > 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 >> > >
