Find the maximum number of consecutive entries

Question

  id ||  week_id ||  user_id ||  catch_id (0,1,2)
 1 ||  2 ||  6 ||  0
 1 ||  3 ||  6 ||  1
 1 ||  4 ||  6 ||  1
 1 ||  5 ||  6 ||  1
 1 ||  6 ||  6 ||  0
 1 ||  7 ||  6 ||  0
 1 ||  8 ||  6 ||  2
 1 ||  9 ||  6 ||  0
 1 ||  10 ||  6 ||  0
 1 ||  11 ||  6 ||  1

I need to find out the maximum consecutive week catch = 1 and the maximum consecutive week catch = 0 for each user (find all). I hope I let myself know.

In the table above

max sequential catch = 1 for user 6 3 (weeks 3,4,5)

max consecutive weeks catch = 0 for user 6 2 (week 6,7 and / or week 9,10)

How do I get around. Can i do this in pure sql. A php solution is also welcome.

Answer 1

This should work for a SQL solution. Although it will only ever give you one week_id for the catch_id in question. I do not know what is called your table, so I called it consecutive in the answer below:

 drop table if exists consecutive; create table consecutive (id int,week_id int,user_id int,catch_id int); insert into consecutive values (1,2,6,0); insert into consecutive values (1,3,6,1); insert into consecutive values (1,4,6,1); insert into consecutive values (1,5,6,1); insert into consecutive values (1,6,6,0); insert into consecutive values (1,7,6,0); insert into consecutive values (1,8,6,2); insert into consecutive values (1,9,6,0); insert into consecutive values (1,10,6,0); insert into consecutive values (1,11,6,1); select w,count(*) as max_consecutive_weeks from ( select case when @cur_catch_id != catch_id then @cur_week_id := week_id else @cur_week_id end as w, @cur_catch_id := catch_id as catchChange, c.* from consecutive c cross join (select @cur_catch_id := -1,@cur_week_id := -1) t where user_id = 6 order by week_id asc ) t where catch_id = 1 group by w order by max_consecutive_weeks desc,w asc limit 1; 

You can use the same query to get maximum consecutive weeks with catch_id = 0 by changing where catch_id = 1 to where catch_id = 0 .

Good luck

Answer 2

If PHP is ok, I would do it directly:

• Extract all elements having catch = x (x will be 0 or 1, depending on what you want to calculate) in ASC order for week_id
• Iterate through the elements:
  • Check if there is a space in week_id
  • Update maximum

Answer 3

Write a request and get an array called week = catch format data (the key is the week and the catch is the value)

 $streak = array(); $WeekId = 0; $prev = 0; $count = 1; foreach ($data as $week => $catch) { if($week == ++$WeekId && $prev == $catch) { $count ++; $WeekId = $week; } else { if($prev !== 0) { $streak[$prev][$count] = $count; } $WeekId = $week; $count = 1; $prev = $catch; } } $streak[$prev][$count] = $count; 

Now calculate max () for each line of $ [0] and $ [1]

Answer 4

I have not tried the code, but it should work; may need a little tweaking.

Use SQL and get all rows sorted by week_id

 $currentcatch = ''; $run = 0; $results = array(); while($record) { if ($record['catch_id'] == $currentcatch) { $run++; } else { if (!empty($currentcatch)) { if (empty($results[$currentcatch]) { $results[$currentcatch] = $run; } else { if ($results[$currentcatch] < $run) { $results[$currentcatch] = $run; } } } $run = 1; $currentcatch = $record['catch_id']; } } print_r($results); 

Answer 5

Here is a PHP solution. I tested it on my lamp and it should work.

 /* steps: 1.sort the week_ids 2.iterate the sorted week_ids and try to get all possible max numbers of consecutive records 3.show the greatest one. */ $numstr = array(4,2,5,6,7,1); //sample week_ids,should be fetched from db by catch_id sort($numstr); $int_max = 1; $maxs_array = array(); for($i=0;$i<sizeof($numstr);$i++) { $k = $i; while($numstr[$k]) { if($numstr[$k+1] && $numstr[$k+1] == $numstr[$k]+1) //duplicate week_ids not considered yet { $int_max ++; } else { array_push($maxs_array,$int_max); $int_max = 1; continue 2; } $k++; } } sort($maxs_array); echo array_pop($maxs_array); //output 4