mysql 事件统计_mysql函数实例-统计日存留率

3. report_user_remain_day (日存留用户统计表)

CREATE TABLE `report_user_remain_day` (

`id` varchar(36) NOT NULL COMMENT '主键(参照:select UUID())',

`day_time` date DEFAULT NULL COMMENT '日期(2013-07-12)',

`remain_count` int(11) DEFAULT '0' COMMENT '日存留数量',

`remain_percent_day` double(4,2) DEFAULT '0.00' COMMENT '日存留率',

`type_client` tinyint(1) DEFAULT '0' COMMENT '客户端类型(0:食客app;1:老板app)',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

存储函数:

BEGIN

#Routine body goes here...

DECLARE _yesterday DATE DEFAULT NULL;

DECLARE _before_yesterday DATE DEFAULT NULL;

DECLARE _is_success tinyint(1) DEFAULT 0;

select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 1 day) INTO _yesterday;

select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 2 day) INTO _before_yesterday;

#统计前天用户数,并将前天用户插入到report_user_remain_day_detail表中

SET _is_success = get_user_remain_day(_before_yesterday);

#查询report_user_login_day_detail,统计昨天的用户,并将前天和昨天都登录的用户状态设置为1

SET _is_success = update_user_status_remain(_yesterday);

#统计存留用户明细表,将统计数据存储到统计表report_user_remain_day

SET _is_success = insert_user_remain_day(_yesterday);

RETURN _is_success;

END

CREATE FUNCTION `get_user_remain_day`(`_day_time` date) RETURNS int(1)

BEGIN

#Routine body goes here...

DECLARE stopFlag INT DEFAULT 0 ;

DECLARE _device VARCHAR(50) DEFAULT NULL;

DECLARE _a_token CHAR(64) DEFAULT '-1';

DECLARE _day_time date;

DECLARE _shop_id INT(11) DEFAULT 0;

#查询当天的登录用户

DECLARE cur1 CURSOR FOR select shop_id, device, a_token from report_user_login_day_detail WHERE day_time = _day_time;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;

OPEN cur1;

FETCH cur1 INTO _shop_id, _device, _a_token;

WHILE stopFlag = 0 do

INSERT INTO report_user_remain_day_detail(id, shop_id, day_time, device, a_token)

values (UUID(), _shop_id, _day_time, _device, _a_token);

FETCH cur1 INTO _shop_id, _device, _a_token;

END WHILE;

CLOSE cur1;

RETURN 1;

END

CREATE FUNCTION `update_user_status_remain`(`_day_time` date) RETURNS tinyint(1)

BEGIN

#Routine body goes here...

DECLARE stopFlag INT DEFAULT 0 ;

DECLARE _device VARCHAR(50) DEFAULT NULL;

DECLARE _a_token CHAR(64) DEFAULT '-1';

#查询昨天的登录用户

DECLARE cur1 CURSOR FOR select device, a_token from report_user_login_day_detail

WHERE day_time = _day_time;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;

OPEN cur1;

FETCH cur1 INTO _device, _a_token;

WHILE stopFlag = 0 do

UPDATE report_user_remain_day_detail SET status_remain = 1, day_time = _day_time

WHERE device = _device;

FETCH cur1 INTO _device, _a_token;

END WHILE;

CLOSE cur1;

RETURN 1;

END

CREATE FUNCTION `insert_user_remain_day`(`_day_time` date) RETURNS tinyint(1)

BEGIN

#Routine body goes here...

DECLARE stopFlag INT DEFAULT 0 ;

DECLARE _status_remain TINYINT(1);

DECLARE _remain_count INT DEFAULT 0;

DECLARE _all_count INT DEFAULT 0;

DECLARE _temp_count INT DEFAULT 0;

DECLARE cur2 CURSOR FOR SELECT status_remain, count(device) FROM report_user_remain_day_detail

WHERE status_remain = 1 and day_time = _day_time GROUP BY status_remain;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;

OPEN cur2;

FETCH cur2 INTO _status_remain, _remain_count;

WHILE stopFlag = 0 do

IF _status_remain = 1 THEN

SET _temp_count = _remain_count;

SET _all_count = _all_count + _remain_count;

ELSE

SET _all_count = _all_count + _remain_count;

END IF;

INSERT INTO report_user_remain_day(id, day_time, remain_count, remain_percent_day, type_client)

VALUES(UUID(), _day_time, _temp_count, (_temp_count / _all_count), 0);

FETCH cur2 INTO _status_remain, _remain_count;

END WHILE;

CLOSE cur2;

RETURN 1;

END