今天用php開發(fā)網(wǎng)站，遇到一個(gè)需求。統(tǒng)計(jì)網(wǎng)站關(guān)鍵詞的google流量，計(jì)算本周某個(gè)關(guān)鍵詞對于上周的google流量的增長。goole流量統(tǒng)計(jì)是通過日志分析程序獲取。本打算差值計(jì)算也用php實(shí)現(xiàn)，但是一想還要循環(huán)查詢數(shù)據(jù)庫，會(huì)造成數(shù)據(jù)庫壓力，于是乎編寫了一個(gè)存儲(chǔ)過程。
首先數(shù)據(jù)結(jié)構(gòu)：
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
存儲(chǔ)過程，實(shí)現(xiàn)了雙游標(biāo)的循環(huán)：
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 ;
其中存儲(chǔ)過程中的具體語法可以查看mysql的幫助文檔。
最后用call pro_week_stat()調(diào)用即可。