DELIMITER $$
USE `jshoper3x`$$
DROP PROCEDURE IF EXISTS `pro_s_jshop_active_data_count`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_s_jshop_active_data_count`()
BEGIN
DECLARE jshopNumber VARCHAR(36) DEFAULT NULL;
DECLARE maintainerJobnumber VARCHAR(20) DEFAULT NULL;
DECLARE jshopState INT DEFAULT 0;
DECLARE jshopName VARCHAR(100) DEFAULT NULL;
DECLARE dayActive DOUBLE(10,2) DEFAULT 0.0;
/*Login,Publish,Update计数器*/
DECLARE loginCount INT DEFAULT 0;
DECLARE publishCount INT DEFAULT 0;
DECLARE updateCount INT DEFAULT 0;
DECLARE contenttype VARCHAR(20) DEFAULT NULL;
DECLARE finish INT DEFAULT 0;
DECLARE membernumber VARCHAR(36) DEFAULT NULL;
DECLARE visiturl VARCHAR(255) DEFAULT NULL;
DECLARE actiontype VARCHAR(20) DEFAULT NULL;
DECLARE rs CURSOR FOR SELECT DISTINCT l.user_number,l.url FROM s_log_data_info AS l WHERE l.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish=1;
OPEN rs;
myloop:LOOP
FETCH rs INTO membernumber,visiturl;
IF finish =1 THEN
LEAVE myloop;
END IF;
/*比对当前的日志表中的url是否在url映射表中并获取对应的操作类型*/
SELECT sui.action_type FROM s_urlmapping_info AS sui WHERE uri IN(SELECT SUBSTRING_INDEX(visiturl,'?',1)) INTO actiontype;
IF(actiontype='login') THEN
/*如果是login类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员login动作的记录数*/
SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1))
AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO loginCount;
END IF;
IF(actiontype='publish') THEN
/*如果是publish类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员publish动作的记录数*/
SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1))
AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO publishCount;
END IF;
IF(actiontype='update') THEN
/*如果是update类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员update动作的记录数*/
SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1))
AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO updateCount;
END IF;
/*获取当前会员管理的店铺唯一编号*/
SELECT ma.jshop_number FROM jshop_account AS ma WHERE ma.member_number=membernumber INTO jshopNumber;
/*获取当前会员的维护人*/
SELECT DISTINCT cr.maintainer_jobnumber FROM customer_relationship AS cr WHERE cr.jshop_number=jshopNumber INTO maintainerJobnumber;
/*获取当前用户的店铺的探点类型*/
SELECT td.state FROM tandian AS td WHERE td.jshop_number=jshopNumber INTO jshopState;
/*获取当前会员的店铺名称*/
SELECT m.jshop_name FROM jshop AS m WHERE m.number=jshopNumber INTO jshopName;
IF(jshopState=2) THEN
SET dayActive=(loginCount*0.7)+(publishCount*0.3);
END IF;
IF(jshopState=3) THEN
SET dayActive=(loginCount*0.7)+(publishCount*0.2)+(updateCount*0.1);
END IF;
IF(jshopState=4) THEN
SET dayActive=(loginCount*0.7)+(publishCount*0.2)+(updateCount*0.1);
END IF;
INSERT INTO s_active_data_info(`jshop_number`,`maintainer_jobnumber`,`tandian_state`,`jshop_name`,`day_active`,`gmt_create`)
VALUES(jshopNumber,maintainerJobnumber,jshopState,jshopName,dayActive,now());
END LOOP myLoop;
CLOSE rs;
END$$
DELIMITER ;
call pro_s_jshop_active_data_count;
只是一段代码而已