一、创建函数/存储过程：
CREATE DEFINER=admin@172.16.%.% PROCEDURE pro_statis_wcoin_income()
BEGIN
-- 统计前，先删除
DELETE FROM user_wcoin_income_statis WHERE business_day = FROM_UNIXTIME(UNIX_TIMESTAMP(date_add(CURDATE(), interval -1 day)), '%Y-%m-%d');
-- 开始统计
INSERT INTO user_wcoin_income_statis (
uid,
business_day,
sum_wcoin,
ctime,
utime
) SELECT
a.uid,
FROM_UNIXTIME(a.ctime / 1000, '%Y-%m-%d') business_day,
sum(a.amount),
UNIX_TIMESTAMP() * 1000,
UNIX_TIMESTAMP() * 1000
FROM
user_wcoin_record a
WHERE
a.flow_type = 1
AND a.ctime >= UNIX_TIMESTAMP(date_add(CURDATE(), interval -1 day)) * 1000
AND a.ctime < UNIX_TIMESTAMP(CURDATE()) * 1000
GROUP BY
a.uid,
FROM_UNIXTIME(a.ctime / 1000, '%Y-%m-%d');
COMMIT;
END
二、创建事件：
CREATE DEFINER=admin@172.16.%.% EVENT Event_statis_wcoin_income ON SCHEDULE EVERY 1 DAY STARTS '2018-12-28 02:00:00' ON COMPLETION PRESERVE ENABLE COMMENT '定时每天统计用户的wcoin' DO call pro_statis_wcoin_income()
总结： 每天凌晨2点跑一次存储过程。
