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;

MySQL連續範圍問題

相關文章