Mysql function instance-count the number of monthly login users _ MySQL

Mysql function instance-count the number of monthly login users bitsCN.com

Mysql function instance-count the number of monthly login users

Java code

Create function statics_user_login_month () RETURNS int (11) COMMENT 'monthly user count/r/N' BEGIN # Routine body goes here... DECLARE stopFlag int default 0; DECLARE _ shop_id INT (11) DEFAULT 0; DECLARE _ device VARCHAR (50); DECLARE _ a_token CHAR (64) DEFAULT '-1 '; DECLARE _ s_token VARCHAR (64) default null; DECLARE _ use_count INT (11) DEFAULT 0; DECLARE _ month_time date; # count the log-on user DECLARE cur1 cursor for select id of the previous month, device, a_token, s_token FROM visitor_user WHERE DATE_FORMAT (off_time, '% Y-% M') = DATE_FORMAT (curdate ()-1),' % Y-% M '); declare continue handler for not found set stopFlag = 1; OPEN cur1; FETCH cur1 INTO _ shop_id, _ device, _ a_token, _ s_token; WHILE stopFlag = 0 do insert into partition (id, shop_id, month_time, device, a_token, s_token) VALUES (UUID (), _ shop_id, DATE_FORMAT (curdate ()-1), '% Y-% M '), _ device, _ a_token, _ s_token); FETCH cur1 INTO _ shop_id, _ device, _ a_token, _ s_token; end while; CLOSE cur1; set _ month_time = DATE_FORMAT (curdate ()-1), '% Y-% M'); # COUNT the number of login users in the previous month select count (device) INTO _ use_count FROM visitor_user WHERE DATE_FORMAT (off_time, '% Y-% M') = _ month_time; insert into values (id, month_time, use_count, type_client) VALUES (UUID (), _ month_time, _ use_count, 0); RETURN 1; END

BitsCN.com