午夜国产狂喷潮在线观看|国产AⅤ精品一区二区久久|中文字幕AV中文字幕|国产看片高清在线

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

首先數(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ǔ)過(guò)程，實(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ǔ)過(guò)程中的具體語(yǔ)法可以查看mysql的幫助文檔。

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

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