此查询使用变量来跟踪相邻周,如果它们是连续的,则计算出来:
set @start_week = 2, @week := 0, @conseq := 0, @cookie:='';
select conseq_weeks, count(*)
from (
select
cookie,
if (cookie != @cookie or week != @week + 1, @conseq := 0, @conseq := @conseq + 1) + 1 as conseq_weeks,
(cookie != @cookie and week <= @start_week) or (cookie = @cookie and week = @week + 1) as conseq,
@cookie := cookie as lastcookie,
@week := week as lastweek
from (select week, cookie from webhist where week >= @start_week order by 2, 1) x
) y
where conseq
group by 1;
这是第2周.再过一周,更改顶部的start_week变量.
这是测试:
create table webhist(week int, cookie char);
insert into webhist values (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'a'), (2, 'b'), (3, 'a'), (3, 'c'), (3, 'd');
输出上面的查询,其中周> = 1:
+--------------+----------+
| conseq_weeks | count(*) |
+--------------+----------+
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
+--------------+----------+
输出上面的查询,其中周> = 2:
+--------------+----------+
| conseq_weeks | count(*) |
+--------------+----------+
| 1 | 2 |
| 2 | 1 |
+--------------+----------+
附:好问题,但有点破球