CODEWITHSUNDEEP
phpmysql
aWebDeveloper
aWebDeveloper

Reputation: 38342

Find max no of consecutive records

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 find out the max consecutive week the catch = 1 and max consecutive week the catch = 0 for each user (find all). I hope i make myself clear.

In the above table

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

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

How do i go about. Can i do this in purely sql. A php solution is also welcome

Upvotes: 6

Views: 418

Answers (5)

aWebDeveloper
aWebDeveloper

Reputation: 38342

Write a query and get a array called data of format week=catch(key is the week and 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 the max() of each $streak[0] and $streak[1]

Upvotes: 1

Tom Mac
Tom Mac

Reputation: 9853

This should work for a SQL solution. Though it will only ever give you one week_id for the catch_id in question. I don't know what your table is called so I've 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 max consecutive week_ids with catch_id = 0 by changing where catch_id = 1 to where catch_id = 0.

Good luck!

Upvotes: 3

Young
Young

Reputation: 8356

Here is a PHP solution.I've 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

Upvotes: 0

Alec Smart
Alec Smart

Reputation: 95880

I've not tried the code, but it should work; a little tweaking maybe required.

Use SQL and get all the 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);

Upvotes: 0

duedl0r
duedl0r

Reputation: 9424

If PHP is ok, I would do it straight forward:

  • Retrieve all items having catch = x (x being 0 or 1, depending on what you want to calculate) in ASC order of week_id
  • Iterate through the items:
    • Check whether there is a gap in week_id
    • Update the maximum

Upvotes: 1

Related Questions