접속자 통계

CREATE DEFINER=`definer`@`%` PROCEDURE `sys_statistics_active_user`()
BEGIN
 
    declare $daily_user_count int;
    declare $week_end_date date;
    declare $week_user_count int;
    declare $month_last_date date;
    declare $month_user_count int;
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- default exception handler
        ROLLBACK;
        SET @@SQL_SAFE_UPDATES=1;
        RESIGNAL;        
    END;
 
    #daily
    set $daily_user_count = (select count(*) from `user_public_data` where date(`login_time`) = CURRENT_DATE);
 
    insert into `statistics_daily_active_user`(`statistics_date`, `user_count`)
    values (CURRENT_DATE, $daily_user_count);
 
    #week
    set $week_end_date = DATE_FORMAT(DATE_SUB(current_date, INTERVAL (DAYOFWEEK(current_date)-7) DAY), '%Y-%m-%d');
 
    #week 마지막 날 갱신
    if($week_end_date = CURRENT_DATE) then
        begin
            set $week_user_count = (select * from `user_public_data` where date(`login_time`) >= DATE_FORMAT(DATE_SUB(current_date, INTERVAL (DAYOFWEEK(current_date)-1) DAY), '%Y-%m-%d')
                and date(`login_time`) <= DATE_FORMAT(DATE_SUB(current_date, INTERVAL (DAYOFWEEK(current_date)-7) DAY), '%Y-%m-%d'));
        
            insert into `statistics_week_active_user`(`statistics_start_week_date`, `statistics_end_week_date`, `user_count`)
            values (DATE_FORMAT(DATE_SUB(current_date, INTERVAL (DAYOFWEEK(current_date)-1) DAY), '%Y-%m-%d'), $week_end_date, $week_user_count);
        end;
    end if;
 
    #month
    set $month_last_date = last_day(CURRENT_DATE);
    
    if($month_last_date = CURRENT_DATE) then
        begin
            set $month_user_count = (select count(*) from `user_public_data` where DATE_FORMAT(`login_time`, '%Y-%m') = DATE_FORMAT(current_date, '%Y-%m'));
            
            insert into `statistics_month_active_user` (`statistics_month`, `user_count`)
            values (DATE_FORMAT(current_date, '%Y-%m'), $month_user_count);
        end;
    end if;
END

추가 이벤트 스케줄러

show events;
 
 
CREATE DEFINER = `definer`
EVENT statistics_active_user ON SCHEDULE EVERY 24 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 13 HOUR
COMMENT '통계용'
DO call sys_statistics_active_user();

23시50~59분 사이에서 시작하려고 현재 시간 + 13시간 뒤부터 시작하도록 설정

하루에 한번 돌도록 구현