Find the maximum continuous lane and current lane from date and time

Question

I have the following specific user data - Temperature in the table -

time_stamp
2015-07-19 10:52:00
2015-07-18 10:49:00
2015-07-12 10:43:00
2015-06-08 12:32:00
2015-06-07 11:33:00
2015-06-06 10:05:00
2015-06-05 04:17:00
2015-04-14 04:11:00
2014-04-02 23:19:00

So the output for the request should be - Maximum streak = 4, Current streak = 2

Maximum lane = 4 because of this -

2015-06-08 12:32:00
2015-06-07 11:33:00
2015-06-06 10:05:00
2015-06-05 04:17:00

And the current lane is 2 because of them (suppose today is the date 2015-07-19) -

2015-07-19 10:52:00
2015-07-18 10:49:00

EDIT: I want a simple SQL query for MYSQL

Answer 1

The general approach with gap requests and islands is to mark each line with their rank in the data and their rank in the full list of dates. All clusters will have the same difference.

: , . , MySQL . MySQL.

select user_id, max(time_stamp), count(*)
from (
    select
        t.user_id, t.time_stamp,
        (
            select count(*)
            from T as t2
            where t2.user_id = t.user_id and t2.time_stamp <= t.time_stamp
        ) as rnk,
        number of days from t.time_stamp to current_date as days
    from T as t

) as data
group by usr_id, days - rnk