欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-07-14 17:21:21
...

根据项目报表需求,需要统计通过手机app登录系统的用户的日存留率。统计功能比较简单,仅作日后参考,欢迎经验丰富的朋友指点。

 

1.数据库表

手机用户的信息保存在一张visitor_user的表中,其中包含了用户使用手机app软件时,可以获取的数据,包括: 商家id、手机设备号、下线时间等信息。

 

2.功能分析与设计

数据统计报表一般都采用DB的存储过程或函数进行统计,并将数据保存到数据库表中,提供前台应用查询和展示。对于实时性的报表需求,建议在非业务库上进行统计。一般主库对外提供业务服务,通过复制等机制将业务数据存储到专门的数据库,或者专门的物理节点上,这样就有效的避免了报表统计功能对正常业务的影响。

 

3.实现

1.首先,从用户日统计表中将前天的登录用户统计到存留明细表中。

2.然后,从用户日统计表中将昨天的登录用户统计出来,并将前天、昨天都登录的用户的存留状态设置为1。

3.然后,从存留明细表中统计昨天用户的存留数、存留率,并保存到存留表中。

 

执行统计采用了mysql的事件机制,定时触发调用统计函数,进行数据统计。

每天凌晨4点钟,开始统计昨天的日存留率。

 

event:


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

表结构:

1.report_user_login_day_detail  (日登陆用户流水表)

 

CREATE TABLE `report_user_login_day_detail` (
  `id` varchar(36) NOT NULL COMMENT '主键(参照:select UUID())',
  `shop_id` int(11) DEFAULT NULL COMMENT '餐厅id',
  `day_time` date DEFAULT NULL COMMENT '日期(例如:2013-07-10)',
  `device` varchar(50) DEFAULT NULL COMMENT '设备号',
  `a_token` char(64) DEFAULT NULL COMMENT '用于与苹果服务器会话',
  `off_time` datetime DEFAULT NULL,
  `s_token` varchar(64) DEFAULT NULL COMMENT '与点菜网服务器会话',
  `login_times` int(11) DEFAULT NULL COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

2. report_user_remain_day_detail (日存留用户流水表)

 

CREATE TABLE `report_user_remain_day_detail` (
  `id` varchar(36) NOT NULL COMMENT '主键(参照:select UUID())',
  `shop_id` int(11) DEFAULT NULL COMMENT '餐厅id',
  `day_time` date DEFAULT NULL COMMENT '统计日期(2013-07-12)',
  `device` varchar(50) DEFAULT NULL COMMENT '设备号',
  `a_token` char(64) DEFAULT '-1' COMMENT '用于与苹果服务器会话',
  `off_time` datetime DEFAULT NULL,
  `s_token` varchar(64) DEFAULT NULL COMMENT '与点菜网服务器会话',
  `status_remain` tinyint(1) DEFAULT NULL COMMENT '是否日留存用户(0:非日留存;1:日留存)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 
 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