Mysql function instance-new user on Statistics Day-MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.