id ||week_id||user_id||catch_id(0,1,2(1||2||6||01||3||6||11||4||6||11||5||6||11||6||6|| 01||7||6||01||8||6||21||9||6||01||10||6||01||11||6||1
我需要找出每个用户的最大连续周catch = 1
和最大连续周catch = 0
(全部查找(。我希望我能说清楚。
在上表中
用户6的最大连续捕获量=1为3(第3、4、5周(
用户6的最大连续周捕获量=0为2(第6,7周和/或第9,10周(
我该怎么走。我可以在纯sql中做到这一点吗。php解决方案也很受欢迎
这应该适用于SQL解决方案。尽管它只会为有问题的catch_id提供一个week_id。我不知道你的表叫什么,所以我在下面的答案中称之为consecutive
:
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;
通过将where catch_id = 1
更改为where catch_id = 0
,您可以使用相同的查询来获取catch_id=0的最大连续周id。
祝你好运!
如果PHP还可以,我会直接执行:
- 按照week_id的ASC顺序检索catch=x(x为0或1,具体取决于您想要计算的内容(的所有项目
- 遍历项目:
- 检查week_id是否有间隙
- 更新最大值
编写一个查询并获得一个名为data的数组,格式为week=catch(key是周,catch是值(
$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;
现在计算每个$striad[0]和$Straad[1]
我还没有尝试过代码,但它应该可以工作;可能需要进行一些调整。
使用SQL并获取按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);
这里有一个PHP解决方案。我已经在我的灯上测试过了,它应该能工作。
/*
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