今天用php开发网站，遇到一个需求。统计网站关键词的google流量，计算本周某个关键词对于上周的google流量的增长。goole流量统计是通过日志分析程序获
今天用php开发网站，遇到一个需求。统计网站关键词的google流量，计算本周某个关键词对于上周的google流量的增长。goole流量统计是通过日志分析程序获取。本打算差值计算也用php实现，但是一想还要循环查询数据库，会造成数据库压力，，于是乎编写了一个存储过程。
首先数据结构：
create table `mobile_keywords_weeklog` (
`id` int(11) not null auto_increment,
`topdate` date default null,
`keywords_id` int(11) default '0',
`s_pv` int(11) default '0',
`s_gv` int(11) default '0',
`s_bv` int(11) default '0',
`d_value` int(11) default '0' comment '与上一周的差值',
primary key (`id`),
key `topdate` (`topdate`),
key `keywords_id` (`keywords_id`),
key `s_pv` (`s_gv`)
) engine=myisam auto_increment=702 default charset=latin1
存储过程，实现了双游标的循环：
delimiter $$
use `brother_mobile`$$
drop procedure if exists `pro_week_stat`$$
create definer=`root`@`%` procedure `pro_week_stat`()
top:begin
declare done int default 0;
declare curr_week date;
declare last_week date;
declare a1,b1,c1 int;
declare a2,b2,c2 int;
declare d int;
declare is_update int default 0;
declare all_week cursor for select topdate from brother_mobile.mobile_keywords_weeklog group by topdate order by topdate desc limit 0,2;
declare cur1 cursor for select id,keywords_id,s_gv from brother_mobile.mobile_keywords_weeklog where topdate = curr_week;
declare cur2 cursor for select id,keywords_id,s_gv from brother_mobile.mobile_keywords_weeklog where topdate = last_week;
declare continue handler for sqlstate '02000' set done = 1;
open all_week;
fetch all_week into curr_week;
fetch all_week into last_week;
close all_week;
if ifnull(curr_week,'')='' or ifnull(last_week,'')='' then
leave top;
end if;
open cur1;
out_repeat:repeat
fetch cur1 into a1,b1,c1;
begin
declare done1 int default 0;
declare continue handler for sqlstate '02000' set done1 = 1;
open cur2;
inner_repeat:repeat
fetch cur2 into a2,b2,c2;
if not done then
set is_update=0;
if b1 = b2 then
set d = c1-c2;
set is_update = 1;
update brother_mobile.mobile_keywords_weeklog set d_value = d where id = a1;
leave inner_repeat;
end if;
end if;
until done1 end repeat inner_repeat;
close cur2;
end;
if is_update 1 then
update brother_mobile.mobile_keywords_weeklog set d_value = c1 where id = a1;
end if;
until done end repeat out_repeat;
close cur1;
end$$
delimiter ;
其中存储过程中的具体语法可以查看mysql的帮助文档。
最后用call pro_week_stat()调用即可。