summary_daily

最新推荐文章于 2022-08-27 18:02:22 发布

Coollyj

最新推荐文章于 2022-08-27 18:02:22 发布

阅读量119

点赞数

本文链接：https://blog.csdn.net/Coollyj/article/details/84696673

版权


/*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  THEN
				IF maxDate = '' OR maxDate IS NULL THEN
					SELECT  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; 
			ELSE
				IF maxDate = '' OR maxDate IS NULL THEN
					SELECT  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 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  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(); */