求ID对应DAYS字段的连续天数、连续次数、累计天数
#天数只有本月内,不跨月,不跨年
#连续天数取最后一次连续天数
#连续次数取连续3天算一次,每增加连续1天加1,如果连续天数为4天,连续次数就是1+1,连续天数为5天,连续次数就是1+2
#如果连续天数被打断,则连续打断,重新开始计算连续异常次数
求ID对应DAYS字段的连续天数、连续次数、累计天数
#天数只有本月内,不跨月,不跨年
#连续天数取最后一次连续天数
#连续次数取连续3天算一次,每增加连续1天加1,如果连续天数为4天,连续次数就是1+1,连续天数为5天,连续次数就是1+2
#如果连续天数被打断,则连续打断,重新开始计算连续异常次数
这个问题要分两步来考虑:
第一步:将日期字符串切分开,并和对应的ID另存为一张表。
第二步:从第一步得到表中,查询各ID的对应DAYS字段的连续天数、连续次数、累计天数。
原始表的数据如下图所示:
第一步:
1、建表,用于存放处理好的字期和对应ID
create table days_tb(
ID varchar(5),
DAYS date
);
2、定义存储过程,用于将字符串日期转化为行的形式,并和对应ID存入表中
delimiter //
create procedure sub_str(id varchar(5),days_item varchar(100))
begin
insert into days_tb(id,days) SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(days_item,',',help_topic_id+1),',',-1) AS num
FROM mysql.help_topic
WHERE help_topic_id < LENGTH(days_item)-LENGTH(REPLACE(days_item,',',''))+1;
end //
delimiter ;
3、定义存储过程,利用游标,将所有的记录都进行第2步的处理
delimiter //
create procedure days_pro()
begin
declare done int default 0;
declare days_item varchar(100);
declare id_item varchar(5);
declare days_cursor cursor for select ID,DAYS from item_tb;
declare continue handler for not FOUND set done = 1; /*done = true;亦可*/
open days_cursor;
repeat
fetch days_cursor into id_item,days_item;
if not done then
call sub_str(id_item,days_item);
end if;
until done end repeat;
close days_cursor;
end //
delimiter ;
处理完成后,此时原始表中的数据被转换到新表days_tb中,格式如下:
第二步:
从新表days_tb中查询各ID的对应DAYS字段的连续天数、连续次数、累计天数。
select t5.ID,t5.days1,if(t5.days1>=3,t5.days1-2,0) as con_count,t7.cumsum_days from(
select ID,start_date,count(count_day) as days1,sum(t4.count_day) as cum_days from(
select ID,
@start_day:=DAYS as start_date,
@count_day:=
(case
when (@ID:=ID and datediff(DAYS, @end_date)=1) then (@count_day+0)
when (@ID:=ID and datediff(DAYS, @end_date)<1) then (@count_day+0)
else (@count_day+1)
end) as count_day,
@ID:=ID,
@end_date:=DAYS
from (select ID,DAYS from days_tb order by ID) as t2,
(select @ID:='',@start_date:='',@end_date:='',@count_day:=0) as t3) as t4
group by ID,count_day) as t5,
(select ID,max(start_date) as date_max,sum(days1) as cumsum_days from(
select ID,start_date,count(count_day) as days1 from(
select ID,
@start_day:=DAYS as start_date,
@count_day:=
(case
when (@ID:=ID and datediff(DAYS, @end_date)=1) then (@count_day+0)
when (@ID:=ID and datediff(DAYS, @end_date)<1) then (@count_day+0)
else (@count_day+1)
end) as count_day,
@ID:=ID,
@end_date:=DAYS
from (select ID,DAYS from days_tb order by ID) as t2,
(select @ID:='',@start_date:='',@end_date:='',@count_day:=0) as t3) as t4
group by ID,count_day)as t6 group by ID)as t7
where t5.start_date=t7.date_max and t5.ID=t7.ID;
格式有点乱,大概就是这样。
查询结果如下: