今天用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 ` 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()调用即可。
更多信息请查看IT技术专栏
