mysql函數實例-統計月登陸用戶數 統計月使用用戶數,僅作備忘。
Java代碼 CREATE FUNCTION statics_user_login_month() RETURNS int(11) COMMENT '月使用用戶數\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; #統計上月的食客登錄用戶 DECLARE cur1 CURSOR FOR SELECT id, 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 report_user_login_month_detail(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'); #統計上月登錄用戶數 SELECT COUNT(device) INTO _use_count FROM visitor_user WHERE DATE_FORMAT(off_time, '%Y-%m') = _month_time; INSERT INTO report_user_login_month(id, month_time, use_count, type_client) VALUES(UUID(), _month_time, _use_count, 0); RETURN 1; END