------------------------- 自动生成随机数据存储过程 ------------------------- drop PROCEDURE if exists genRand; delimiter // create PROCEDURE genRand(in rank int, in add_num int, in statTime char(10)) -- rank:随机最大邮件数, add_num:生成条数, startTime:统计数据日期 Begin declare mobile long; declare tmp int; set mobile = 13600000000; set tmp = 1; -- delete from ippush_stats_email; while tmp <= add_num do insert into ippush_stats_email values(mobile + tmp, 1, now(), statTime, random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank), random_int(rank) ); set tmp = tmp + 1; end while; end// delimiter ; call genRand(10, 10, '2010-10-6');
------------------------- 统计从time1到time2时段的 ------------------------- drop PROCEDURE if exists doStats; delimiter // create PROCEDURE doStats(in time1 char(10), in time2 char(10)) Begin declare _mobile char(11); declare _sums int; declare _stat_time date; declare _count int; declare _user_type int; declare fetchSeqOk int; declare fetchSeqCursor cursor for select mobile, count(*) as count, user_type, stat_time, max((clock_1 + clock_2 + clock_3+clock_4+ clock_5+clock_6+clock_7+clock_8+clock_9+ clock_10+clock_11+clock_12+clock_13+clock_14+clock_15+clock_16+clock_17+clock_18+clock_19+ clock_20+clock_21+clock_22+clock_23+clock_24)) as sums from ippush_stats_email where stat_time >= time1 and stat_time <= time2 group by mobile; declare CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = 0;
set fetchSeqOk = 1; open fetchSeqCursor; while fetchSeqOk = 1 do fetch fetchSeqCursor into _mobile, _count, _user_type, _stat_time, _sums; if _sums > 30 then call onelineStat(1, _user_type, time1, time2, _mobile); else call onelineStat(2, _user_type, time1, time2, _mobile); end if; end while; close fetchSeqCursor; end// delimiter ; call doStats('2010-10-01', '2010-11-05');
------------------------- 一条业务线的统计 -------------------------- drop PROCEDURE if exists onelineStat; delimiter // create PROCEDURE onelineStat(in type int, in inuser_type int, in time1 char(10), in time2 char(10), in inmobile char(11)) Begin if inuser_type = 1 then -- is mas user if type = 1 then -- is level 1 insert into ippush_strategy (select mobile, inuser_type, concat(max(clock_1)>=4, max(clock_2)>=4, max(clock_3)>=4, max(clock_4)>=4, max(clock_5)>=4, max(clock_6)>=4, max(clock_7)>=4, max(clock_8)>=4, '111111111', max(clock_19)>=4, max(clock_20)>=4, max(clock_21)>=4, max(clock_22)>=4, max(clock_23)>=4, max(clock_24)>=4), now(), time1, time2 from ippush_stats_email where mobile = inmobile and stat_time >= time1 and stat_time <= time2 and user_type = inuser_type group by mobile ) ; else insert into ippush_strategy ( select mobile, inuser_type, concat(max(clock_1)>=4, max(clock_2)>=4, max(clock_3)>=4, max(clock_4)>=4, max(clock_5)>=4, max(clock_6)>=4, max(clock_7)>=4, max(clock_8)>=4, max(clock_9)>=4, max(clock_10)>=4, max(clock_11)>=4, max(clock_12)>=4, max(clock_13)>=4, max(clock_14)>=4, max(clock_15)>=4, max(clock_16)>=4, max(clock_17)>=4, max(clock_18)>=4, max(clock_19)>=4, max(clock_20)>=4, max(clock_21)>=4, max(clock_22)>=4, max(clock_23)>=4, max(clock_24)>=4), now(), time1, time2 from ippush_stats_email where mobile = inmobile and stat_time >= time1 and stat_time <= time2 and user_type = inuser_type group by mobile ) ; end if; else -- is mig user if type = 1 then insert into ippush_strategy values(inmobile, inuser_type, '111111111111111111111111', now, time1, time2); end if; end if; end// delimiter ;
------------------ 调用 -------------------- call onelineStat(1, 1, '2010-10-01', '2010-11-05', '13600000001');
|