mysql - 高级 SQL 选择查询

Question

week      cookie
1         a
1         b
1         c
1         d
2         a 
2         b
3         a
3         c
3         d

此表代表某人在特定一周访问网站。每个 cookie 代表一个人。每个条目代表某人在特定的一周内访问了该网站。例如，最后一个条目表示“d”在第 3 周来到该网站。

我想知道有多少(相同的)人在接下来的一周内继续回来，如果有一个开始周可以查看的话。

例如，如果我查看第 1 周。我会得到如下结果:

1 | 4
2 | 2
3 | 1

因为第 1 周有 4 个用户来了。他们中只有 2 个 (a,b) 在第 2 周回来了。他们中只有 1 个 (a) 在这 3 周内都来了。

我怎样才能做一个选择查询来找出答案？ table 会很大:可能有 100 周，所以我想找到正确的方法。

Answer 1

此查询使用变量来跟踪相邻周并计算它们是否连续:

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');

上述查询的输出 where week >= 1:

+--------------+----------+
| conseq_weeks | count(*) |
+--------------+----------+
|            1 |        4 |
|            2 |        2 |
|            3 |        1 |
+--------------+----------+

上述查询的输出 where week >= 2:

+--------------+----------+
| conseq_weeks | count(*) |
+--------------+----------+
|            1 |        2 |
|            2 |        1 |
+--------------+----------+

附注好问题，但有点问题