Mysql function instance-new user bitsCN.com on Statistics Day
Mysql function instance-new user on Statistics Day
SQL code CREATE FUNCTION 'statics _ user_new '() RETURNS int (11) comment' statistics newly added users 'In in # Routine body goes here... DECLARE stopFlag int default 0; DECLARE _ shop_id VARCHAR (11) default null; # restaurant id DECLARE _ device VARCHAR (50) default null; # mobile phone device number DECLARE _ a_token CHAR (64) default null; # session with the Apple server DECLARE _ s_token VARCHAR (64) default null; # session with the ordering server DECLARE _ counts int default 0; # query the newly created user DECLARE cur1 cursor for select id, device, a_token, s_token FROM visitor_user where DATE_FORMAT (create_time, '% Y-% m-% d') yesterday ') = DATE_SUB (DATE_FORMAT (now (), '% Y-% m-% d'), INTERVAL 1 DAY); 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_new_day_detail (id, shop_id, device, a_token, s_token) values (UUID (), _ shop_id, _ device, _ a_token, _ s_token); FETCH cur1 INTO _ shop_id, _ device, _ a_token, _ s_token; end while; CLOSE cur1; # COUNT the number of new users on the day select count (device) INTO _ counts FROM visitor_user where DATE_FORMAT (create_time, '% Y-% m-% d') = DATE_SUB (DATE_FORMAT (now (), '% Y-% m-% d'), INTERVAL 1 DAY); insert into report_user_new_day (id, day_time, new_counts, type_client) VALUES (UUID (), DATE_SUB (DATE_FORMAT (now (), '% Y-% m-% d'), INTERVAL 1 DAY), _ counts, 0); SET _ counts = 1; RETURN _ counts; END
BitsCN.com