今天用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技术专栏