Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1935203
  • 博文数量: 219
  • 博客积分: 8963
  • 博客等级: 中将
  • 技术积分: 2125
  • 用 户 组: 普通用户
  • 注册时间: 2005-10-19 12:48
个人简介

文章分类

全部博文(219)

文章存档

2021年(1)

2020年(3)

2015年(4)

2014年(5)

2012年(7)

2011年(37)

2010年(40)

2009年(22)

2008年(17)

2007年(48)

2006年(31)

2005年(4)

分类: Mysql/postgreSQL

2011-01-24 14:00:38

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

下面是几个存储过程,从生成原始数据到计算结果,我在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');


阅读(2636) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

douyaqiang1232015-05-21 17:33:24

写sql的能力是怎么练出来的。