summary_daily

编程技术  /  houtizong 发布于 3年前   133

/*call summary_daily('2015-01-08','2015-01-14');call summary_daily('','2015-01-14');CALL summary_daily();  call summary_daily('', '');call summary_daily('','2015-01-14');*/DROP PROCEDURE IF EXISTS summary_daily; DELIMITER //CREATE PROCEDURE summary_daily(   minDate varchar(20), /*  格式  2015-01-05  */   maxDate varchar(20)  /*  格式  2015-01-05  */)  BEGIN        /*DECLARE  minDate varchar(20) ;      DECLARE  maxDate varchar(20) ;*/      DECLARE  stkDate varchar(20) ;      SET  @FLAG = (SELECT date FROM yt_daily_sumary LIMIT 1,1) ;      IF  minDate = '' OR minDate IS NULL  THENIF maxDate = '' OR maxDate IS NULL THENSELECT  MAX(time) INTO  maxDate FROM yt_log_role_login_in;TRUNCATE  TABLE   yt.yt_daily_sumary;        ELSE          IF @FLAG IS NOT NULL OR @FLAG != '' THEN              SELECT  MAX(date) INTO  minDate FROM yt_daily_sumary;   ELSE             SELECT  MIN(time) INTO  minDate FROM yt_log_role_login_in;          END IF;        END IF; ELSEIF maxDate = '' OR maxDate IS NULL THENSELECT  MAX(time) INTO  maxDate  FROM yt_log_role_login_in;        END IF;      END IF;       /*SET stkDate = (SELECT DATE_FORMAT(A,'%Y-%m-%d') FROM  (SELECT  MIN(time)  AS  A  from yt_log_role_login_in) AS T);*/      SET stkDate = (SELECT DATE_FORMAT(minDate,'%Y-%m-%d'));      SET @diff = DATEDIFF(stkDate,maxDate);       WHILE @diff<=0 DO            /* 按服务器统计没小时的登录用户数*/             SET @chour = 0;               WHILE @chour<24 DO                IF  @chour<10 THEN                 SET @startTime = CONCAT(stkDate,' 0',CAST(@chour AS CHAR));                                    ELSE                   SET @startTime = CONCAT(stkDate,' ',CAST(@chour AS CHAR));               END IF;                        SET @nextHour=@chour+1;                             IF @nextHour<10 THEN                 SET @endTime = CONCAT(stkDate   ,' 0',CAST(@nextHour AS CHAR));                                    ELSE                   SET @endTime = CONCAT(stkDate,' ',CAST(@nextHour AS CHAR));               END IF;                              SET  @tmpDate = stkDate;               /*INSERT INTO yt_daily_sumary(server,date,key,value) value (SELECT server,@tmpDate,@chour,COUNT(id) FROM  yt_log_role_login_in  WHERE time>@startTime AND time>@endTime GROUP BY server);                */                REPLACE INTO yt.yt_daily_sumary (SELECT server,stkDate,@chour,COUNT(id) FROM  yt_log_role_login_in  WHERE time>@startTime AND time<=@endTime GROUP BY server);               SET  @chour = @chour+1;            END WHILE;            /* 按服务器统计每天的注册用户数  key */            SET @likeTime = CONCAT(stkDate,'%');             REPLACE INTO yt.yt_daily_sumary SELECT server,stkDate,25,COUNT(id) FROM  yt_log_role_create  WHERE time like @likeTime  GROUP BY server;             /* 按服务器统计每天的 登录用户数  key */            SET @likeTime = CONCAT(stkDate,'%');             REPLACE INTO yt.yt_daily_sumary SELECT server,stkDate,24,COUNT(id) FROM  yt_log_role_login_in  WHERE time like @likeTime  GROUP BY server;             /* 按服务器统计每天的滚服用户数  key  性能的消耗在此 需要优化 */            SET @likeTime = CONCAT(stkDate,'%');            create temporary table tmp_table(acount varchar(10) not null,server varchar(20) not null);              /* 滚服的 情况一 1*/            REPLACE INTO tmp_table  SELECT distinct b.acount,b.server FROM yt_log_role_login_in a inner join yt_log_role_create as b  on  a.acount=b.acount and b.server != a.server where b.time like @likeTime ;            /* 滚服的 情况二 2*/            REPLACE INTO tmp_table select acount, server from (  SELECT count(distinct a.server) as st, b.acount as acount ,b.server as serverFROM yt_log_role_login_in a inner join yt_log_role_create as b  on a.acount=b.acount and b.server=a.server  where b.time like @likeTime  group by b.acount ) as l where l.st>1  ;            REPLACE INTO yt.yt_daily_sumary SELECT server,stkDate,26,COUNT(DISTINCT acount) FROM  tmp_table group by server;            drop temporary table tmp_table;              SET @diff = DATEDIFF(stkDate,maxDate);            SET stkDate = (SELECT ADDDATE(stkDate,1));              /*SELECT stkDate, minDate,maxDate,@diff;*/      END WHILE; END; // DELIMITER;/*CALL summary_daily(); */