mysql関数のインスタンス-統計日新規ユーザー
1651 ワード
新しいユーザー統計は、メモとしてのみ使用されます.
CREATE FUNCTION `statics_user_new`() RETURNS int(11)
COMMENT ' '
BEGIN
#Routine body goes here...
DECLARE stopFlag INT DEFAULT 0 ;
DECLARE _shop_id VARCHAR(11) DEFAULT NULL; # id
DECLARE _device VARCHAR(50) DEFAULT NULL; #
DECLARE _a_token CHAR(64) DEFAULT NULL; #
DECLARE _s_token VARCHAR(64) DEFAULT NULL; #
DECLARE _counts INT DEFAULT 0;
#
DECLARE cur1 CURSOR FOR SELECT id, device, a_token, s_token FROM visitor_user
where DATE_FORMAT(create_time, '%Y-%m-%d') = 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;
#
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