MySQL技术内幕 SQL编程 58页
首先创建实验表
create table t(a int primary key);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(100);
insert into t values(101);
insert into t values(103);
insert into t values(104);
insert into t values(105);
commit;

实验目标是求数字的连续范围,以上面的数据为例,应该求得的连续范围为
1-3
100-101
103-105

第一步,给数据增加行号
select a,@a:=@a+1 rn from t,(select @a:=0) as a;

第二步,求得数据与行号的差值

select a,rn,a-rn diff from
(
select a,@a:=@a+1 rn from t,(select @a:=0) as a
) b;

第三步获得结果

select min(a) start_range,max(a) end_range
from
(
select a,rn,a-rn diff from
(
select a,@a:=@a+1 rn from t,(select @a:=0) as a
) b
) c group by diff;

用之前的签到实验说明这个连续范围的用法.
http://blog.itpub.net/29254281/viewspace-1379159/

award_chance_history是活动的签到表,userid记录用户的ID,createtime记录用户的签到时间.
实验求每个用户的连续签到时间.

create table award_chance_history
(
id int primary key auto_increment,
userid int,
createtime datetime
);
insert into award_chance_history(userid,createtime)
select ceil(rand()*10000),str_to_date('2014-12-15','%Y-%m-%d')+interval ceil(rand()*10000) minute from nums where id<30;

insert into award_chance_history(userid,createtime)
select userid,createtime + interval ceil(rand()*10) day from award_chance_history,nums
where nums.id <10 order by rand() limit 150;

第一步,获取用户每天的签到情况
获取用户签到时间和现在时间的差值,并且用distinct过滤用户在一天内多次签到的记录
select
distinct
userid,
date_format(createtime,'%Y-%m-%d') createtime,
datediff(createtime,now()) diff
from award_chance_history order by userid,createtime

第二步,模拟分析函数,每个用户都产生一个自己的序列
select
b.*,
@gid := @cgid,
@cgid := b.userid,
if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank,
b.diff-@rank flag from (
   select
   distinct
   userid,
   date_format(createtime,'%Y-%m-%d') createtime,
   datediff(createtime,now()) diff
   from award_chance_history order by userid,createtime
) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a

diff和rank序列相减,作为flag字段,flag相同说明用户是连续签到.
如下图的249用户,在16,17日连续签到,他们的flag都是-16.

第三步,根据分组得到连续签到的结果

select userid,min(c.createtime),max(c.createtime),count(*) from
(
select
b.*,
@gid := @cgid,
@cgid := b.userid,
if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank,
b.diff-@rank flag from (
select
distinct
userid,
date_format(createtime,'%Y-%m-%d') createtime,
datediff(createtime,now()) diff
from award_chance_history order by userid,createtime
) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a
) c group by userid,flag;

第四步,求每个用户最长的连续签到时间段
在第三步的基础上,再模拟一次分析函数

select * from (
select d.*,
@ggid := @cggid,
@cggid := d.userid,
if(@ggid = @cggid, @grank := @grank + 1, @grank := 1) grank
from
(
select userid,min(c.createtime) begin_date ,max(c.createtime) end_date,count(*) c from
(
select
b.*,
@gid := @cgid,
@cgid := b.userid,
if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank,
b.diff-@rank flag from (
select
distinct
userid,
date_format(createtime,'%Y-%m-%d') createtime,
datediff(createtime,now()) diff
from award_chance_history order by userid,createtime
) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a
) c group by userid,flag
order by userid,count(*) desc
) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e
)f
where grank=1;

ITPub博客

MySQL连续范围问题

最新文章