今天用php開發(fā)網(wǎng)站，遇到一個需求。統(tǒng)計網(wǎng)站關(guān)鍵詞的google流量，計算本周某個關(guān)鍵詞對于上周的google流量的增長。goole流量統(tǒng)計是通過日志分析程序獲取。本打算差值計算也用php實現(xiàn)，但是一想還要循環(huán)查詢數(shù)據(jù)庫，會造成數(shù)據(jù)庫壓力，于是乎編寫了一個存儲過程。

首先數(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

存儲過程，實現(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 ;

其中存儲過程中的具體語法可以查看mysql的幫助文檔。

最后用call pro_week_stat()調(diào)用即可。

更多信息請查看IT技術(shù)專欄