MySQL 创建存储过程

mysql统计留存率

mysql关于用户留几日存率的统计,怎么做啊?
本来已经做好的,但是老板要改需求。
假如说从12号开始。
12号的次日留存率为0,三日留存为0,七日留存为0.
13号的数据一出来,12号的次日留存就改为x%。
14号的数据一出来,12号的三日留存就改为x%。
18号的数据一出来,12号的七日留存就改为x%。

MySQL 创建存储过程
“pr_add” 是个简单的 MySQL 存储过程,这个存储过程有两个 int
类型的输入参数

 

我用的mysql数据库。这存储过程该怎么写啊?头疼。目前只写出来插入的了,也不知道对不对,修改的不知道怎么说,求大侠拔刀相助啊!!
BEGIN

“a”、“b”,返回这两个参数的和。

Sql代码  

declare numareaId int;
declare currentareaId int;

drop procedure if exists pr_add;

begin  

if !exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where
TABLE_SCHEMA=’sgsi’ and TABLE_NAME=’statistics_day_player’) then
CREATE TABLE statistics_day_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY ,
areaId INT NOT NULL default 0,
newPlayer INT NOT NULL default 0,
createTime DATETIME,
oneDayPlayer double NOT NULL default 0,
twoDayPlayer double NOT NULL default 0,
threeDayPlayer double NOT NULL default 0,
fourDayPlayer double NOT NULL default 0,
fiveDayPlayer double NOT NULL default 0,
sixDayPlayer double NOT NULL default 0,
sevenDayPlayer double NOT NULL default 0
);
end if;

— 计算两个数之和

declare i int;  

SELECT COUNT,MIN INTO @a,@b FROM option_area_info;

create procedure pr_add
(
   a int,
   b int
)
begin
   declare c int;

declare numareaId int(10);  

SET numareaId=@a;
SET currentareaId=@b;

   if a is null then
      set a = 0;
   end if;

declare currentareaId int(10);  

loop1:WHILE numareaId>0 DO

   if b is null then
      set b = 0;
   end if;

SELECT COUNT(areaId),MIN(areaId) INTO @a,@b FROM option_area_info;  

SET @AID = currentareaId;
SELECT count(distinct playerId) INTO @newplayer FROM log_login where
registerTime=current_date() and areaId=@AID;

   set c = a + b;

  

INSERT INTO statistics_day_player
(areaId,newPlayer,createTime,oneDayPlayer,twoDayPlayer,threeDayPlayer,fourDayPlayer,fiveDayPlayer,sixDayPlayer,sevenDayPlayer)
VALUES
(currentareaId,
@newplayer,
(select ((select count(distinct playerId) from log_login where
registerTime=current_date() and loginTime>=date_add,interval 1 day)
and loginTime(select ((select count(distinct playerId) from log_login
where registerTime=current_date() and loginTime>=date_add,interval
2 day) and loginTime(select ((select count(distinct playerId) from
log_login where registerTime=current_date() and
loginTime>=date_add,interval 3 day) and loginTime(select ((select
count(distinct playerId) from log_login where
registerTime=current_date() and loginTime>=date_add,interval 4 day)
and loginTime(select ((select count(distinct playerId) from log_login
where registerTime=current_date() and loginTime>=date_add,interval
5 day) and loginTime(select ((select count(distinct playerId) from
log_login where registerTime=current_date() and
loginTime>=date_add,interval 6 day) and loginTime(select ((select
count(distinct playerId) from log_login where
registerTime=current_date() and loginTime>=date_add,interval 7 day)
and loginTime<date_add(curdate,interval 8 day) and
areaId=@AID)/(select count(distinct playerId) from log_login where
registerTime=current_date() and areaId=@AID))*100)
);

   select c as sum;

SET
[email protected];
 

SET numareaId=numareaId-1;
SET currentareaId=currentareaId+1;

   /*
   return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
  /
end;
二、调用 MySQL 存储过程
call pr_add(10, 20);
执行 MySQL 存储过程,存储过程参数为 MySQL 用户变量。

SET
[email protected];
 

END WHILE loop1;

set @a = 10;
set @b = 20;

  

END

call pr_add(@a, @b);
三、MySQL 存储过程特点
创建 MySQL 存储过程的简单语法为:

loop1:WHILE numareaId>0 DO  

create procedure 存储过程名字()
(
   [in|out|inout] 参数 datatype
)
begin
   MySQL 语句;
end;
MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”,则默认为“in”。

SET @AID = currentareaId;  

习惯上,对于是“in” 的参数,我们都不会显式指定。

 

  1. MySQL 存储过程名字后面的“()”是必须的,即使没有一个参数,也需要“()”

  2. MySQL 存储过程参数,不能在参数名称前加“@”,如:“@a int”。下面的创建存

SET i=1;  

储过程语法在 MySQL 中是错误的(在 SQL Server 中是正确的)。 MySQL
存储过程中

while i<8 do  

的变量,不需要在变量名字前加“@”,虽然 MySQL 客户端用户变量要加个“@”。

if exists(select * from statistics_player_l where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day) and
[email protected])
then  

create procedure pr_add
(
   @a int,- 错误
   b int   – 正确
)

update statistics_player_l set oneDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 1
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 2 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

  1. MySQL 存储过程的参数不能指定默认值。

  2. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server
    存储过

update statistics_player_l set twoDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 2
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 3 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

程必须加 “as” 关键字。

update statistics_player_l set threeDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 3
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 4 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

create procedure pr_add
(
   a int,
   b int
)
as             – 错误,MySQL 不需要 “as”
begin
   mysql statement …;
end;

update statistics_player_l set fourDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 4
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 5 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

  1. 如果 MySQL 存储过程中包含多条 MySQL 语句,则需要 begin end 关键字。

update statistics_player_l set fiveDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 5
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 6 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

create procedure pr_add
(
   a int,
   b int
)
begin
   mysql statement 1 …;
   mysql statement 2 …;
end;

update statistics_player_l set sixDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 6
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 7 day) and
[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

  1. MySQL 存储过程中的每条语句的末尾,都要加上分号 “;”

update statistics_player_l set sevenDayPlayer=(select ((select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day) and
loginTime>=date_add(date_sub(curdate(),interval i day),interval 7
day) and loginTime<date_add(date_sub(curdate(),interval i
day),interval 8 day) and
areaId[email protected])/(select
count(distinct playerId) from log_login where
registerTime=date_sub(curdate(),interval i day)))*100) where
createTime>=date_sub(curdate(),interval i day) and
createTime<date_sub(curdate(),interval i-1 day);  

   …

  

   declare c int;

end if;  

   if a is null then
      set a = 0;
   end if;

SET i=i+1;  

   …
end;

end while;  

  1. MySQL 存储过程中的注释。

  

   /*
     这是个
     多行 MySQL 注释。
  /

SET numareaId=numareaId-1;  

   declare c int;    – 这是单行 MySQL 注释 (注意- 后至少要有一个空格)

SET currentareaId=currentareaId+1;  

   if a is null then 这也是个单行 MySQL 注释
      set a = 0;
   end if;

  

   …
end;

END WHILE loop1;  

  1. 不能在 MySQL 存储过程中使用 “return” 关键字。

  

   set c = a + b;

end  

   select c as sum;

Sql代码 begin declare i int;
declare numareaId int(10); declare currentareaId int(10); SELECT
COUNT(areaId),MIN(areaId) INTO @a,@b FROM option_area_info;
SET…

   /*
   return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
  /
end;

  1. 调用 MySQL
    存储过程时候,需要在过程名字后面加“()”,即使没有一个参数,也