mysql留存率除了存储过程_MySQL 使用存储过程实现留存率

DELIMITER //

USE `resourcemanage-dev`//

DROP PROCEDURE IF EXISTS `stat_remain_player`//

CREATE DEFINER=`root`@`localhost` PROCEDURE `stat_remain_player`()

BEGIN

-- 今天的日期

DECLARE today DATE DEFAULT CURDATE();

DECLARE yesterday DATE DEFAULT DATE_SUB(today, INTERVAL 1 DAY);

DECLARE days_ago_2 DATE DEFAULT DATE_SUB(today, INTERVAL 2 DAY);

DECLARE days_ago_3 DATE DEFAULT DATE_SUB(today, INTERVAL 3 DAY);

DECLARE days_ago_4 DATE DEFAULT DATE_SUB(today, INTERVAL 4 DAY);

DECLARE days_ago_6 DATE DEFAULT DATE_SUB(today, INTERVAL 6 DAY);

DECLARE days_ago_7 DATE DEFAULT DATE_SUB(today, INTERVAL 7 DAY);

DECLARE days_ago_13 DATE DEFAULT DATE_SUB(today, INTERVAL 13 DAY);

DECLARE days_ago_14 DATE DEFAULT DATE_SUB(today, INTERVAL 14 DAY);

DECLARE days_ago_29 DATE DEFAULT DATE_SUB(today, INTERVAL 29 DAY);

DECLARE days_ago_30 DATE DEFAULT DATE_SUB(today, INTERVAL 30 DAY);

-- 统计昨天DRU(就是昨天一天的注册人数)

INSERT INTO stat_remain(dru, stat_time, add_time) SELECT COUNT(uid) , DATE_SUB(CURDATE(), INTERVAL 1 DAY), NOW() FROM t_ar_role WHERE latelyLogin>'2000-01-01' AND DATE BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE();

-- 修改前天的2日留存

UPDATE stat_remain SET second_day = (

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_2 AND yesterday) AND (latelyLogin BETWEEN yesterday AND today))

/

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_2 AND yesterday))

) WHERE stat_time = days_ago_2;

-- 修改大前天的3日留存

UPDATE stat_remain SET third_day = (

SELECT(

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_3 AND days_ago_2) AND (latelyLogin BETWEEN yesterday AND today))

/

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_3 AND days_ago_2))

)

) WHERE stat_time = days_ago_3;

-- 7日留存

UPDATE stat_remain SET seventh_day = (

SELECT(

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_7 AND days_ago_6) AND (latelyLogin BETWEEN yesterday AND today))

/

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_7 AND days_ago_6))

)

) WHERE stat_time = days_ago_7;

-- 14日留存

UPDATE stat_remain SET fourteen_day = (

SELECT(

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_14 AND days_ago_13) AND (latelyLogin BETWEEN yesterday AND today))

/

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_14 AND days_ago_13))

)

) WHERE stat_time = days_ago_14;

-- 30日留存

UPDATE stat_remain SET thirtieth_day = (

SELECT(

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_30 AND days_ago_29) AND (latelyLogin BETWEEN yesterday AND today))

/

(SELECT COUNT(DISTINCT uid) FROM t_ar_role WHERE (DATE BETWEEN days_ago_30 AND days_ago_29))

)

) WHERE stat_time = days_ago_30;

END//

DELIMITER ;

调用留存率：

call stat_remain_player();

留存率的存取过程，