--创建定时器
CREATE EVENT event_statistics
--设置为每天触发
ON SCHEDULE EVERY 1 DAY
--起始时间
STARTS '2015-01-29 02:00:00'ON COMPLETION PRESERVE DO
--调用存储过程
CALL statistics();
create procedure statistics()
begin
declare done boolean default 0;
declare o varchar(50);
declare oldloginuser int;
declare allloginuser int;
declare newloginuser int;
declare alluser int;
declare installuser int;
declare authinstalluser int;
declare noauthuser int;
declare monthuser int;
declare weekuser int;
declare thrice int;
declare thricestr varchar(50);
declare fifth int;
declare tentimes int;
declare done_1 boolean default 0;
declare app_name varchar(100);
declare cursorforstation cursor
for
select no from t_wifi_station;
declare continue handler for sqlstate '02000' set done=1;
open cursorforstation;
repeat
if o!="" then
select count(distinct userid) into oldloginuser from t_user_login_log where
stationno = o and date(logintime) < date_sub(curdate(),interval 1 day) and userid
in
(select userid from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day));
select count(distinct userid) into allloginuser from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day);
set newloginuser = allloginuser - oldloginuser;
select count(1) into allloginuser from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day);
select count(1) into alluser from t_wifi_user where
stationno = o and date(installtime) <= date_sub(curdate(),interval 1 day);
select count(1) into installuser from t_wifi_user where stationno = o
and date(installtime) = date_sub(curdate(),interval 1 day);
select count(1) into authinstalluser from t_wifi_user where stationno = o
and status =1 and date(installtime) = date_sub(curdate(),interval 1 day);
set noauthuser = installuser - authinstalluser;
select count(1) into monthuser from t_user_login_log where
date(logintime) < date_sub(curdate(),interval 1 month) and userid
in
(select userid from t_user_login_log where stationno = o
and date(logintime) = date_sub(curdate(),interval 1 day));
select count(1) into weekuser from t_user_login_log where date(logintime) < date_sub(curdate(),interval 1 week) and userid
in
(select userid from t_user_login_log where stationno = o
and date(logintime) = date_sub(curdate(),interval 1 day));
insert into t_statistics(staionno,loginnum,olduser,newuser,usertotal,install,authnum,noauthnum,monthuser,weekuser,gettime)
values (o,allloginuser,oldloginuser,newloginuser,alluser,installuser,authinstalluser,noauthuser,monthuser,weekuser,date_sub(curdate(),interval 1 day));
select count(*) into thrice from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)
group by userid having count(1) >=3) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,thrice,date_sub(curdate(),interval 1 day));
select count(*) into fifth from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)--日期等于昨天的数据
group by userid having count(1) >=5) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,fifth,date_sub(curdate(),interval 1 day));
select count(*) into tentimes from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)
group by userid having count(1) >=10) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,tentimes,date_sub(curdate(),interval 1 day));--昨天
end if;
fetch cursorforstation into o;
until done
end repeat;
close cursorforstation;
call appstapro();
end
create procedure appstapro()
begin
declare done_1 boolean default 0;
declare app_name varchar(100);
declare app_count int;
declare app_statistics cursor
for
select appname,count(1) coun from t_wifi_userapp
group by appname order by count(1) desc limit 0,30;
declare continue handler for sqlstate '02000' set done_1=1;
delete from t_applist;
open app_statistics;
repeat
if app_name!="" then
insert into t_applist(name,downnum,gettime)
values(app_name,app_count,date_sub(curdate(),interval 1 day)); --昨天的日期
end if;
fetch app_statistics into app_name,app_count;
until done_1
end repeat;
close app_statistics;
end
show events;
查看定时器状态
SHOW VARIABLES LIKE 'event_scheduler'
//开启定时器
SET GLOBAL event_scheduler = 1;