今天用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()調用即可。
