免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1022 | 回复: 0

最近写的一个MySQL存储过程 [复制链接]

论坛徽章:
0
发表于 2011-12-19 13:55 |显示全部楼层

前一阵子做一个统计功能,于是写了个存储过程,就是根据一定的算法,用基础数据来计算另一个表的数据.

下面是几个存储过程,从生成原始数据到计算结果,我在MySQL 5.0.18版本上运行通过,我就不贴表结构了,主要是让大家看看语法.贴这儿也是自己以后再写时可以参考.

------------------------- 自动生成随机数据存储过程 -------------------------
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');


您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP