접속로그 통계 (신규, 중복 동시 처리)

접속로그 통계 구현을 위해서 로그를 쌓고 있는 테이블에서 데이터를 가공해야 한다.

접속로그 테이블에는 접속실패, 접속성공 등 모든 접속 시도 데이터를 저장해야 한다.

CREATE TABLE `rb_accessLog` (
  `uid` int(11) NOT NULL,
  `ipaddr` varchar(20) NOT NULL,
  `access_date` datetime NOT NULL COMMENT '접속시간',
  `userID` varchar(60) NOT NULL,
  `userNM` varchar(20) DEFAULT NULL,
  `OS` varchar(60) DEFAULT NULL,
  `browser` varchar(30) DEFAULT NULL,
  `hit` int(11) NOT NULL DEFAULT 0,
  `success` int(1) NOT NULL DEFAULT 1 COMMENT '성공실패',
  `date` char(8) NOT NULL,
  `time` varchar(10) NOT NULL,
  `YM` char(6) NOT NULL,
  `MD` char(4) NOT NULL,
  `year` char(4) NOT NULL,
  `month` char(2) NOT NULL,
  `day` char(2) NOT NULL,
  `route` int(1) NOT NULL DEFAULT 0 COMMENT '접속루트',
  `errCode` int(2) NOT NULL DEFAULT 0 COMMENT '에러코드',
  `display` tinyint(2) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
ALTER TABLE `rb_accessLog`
  ADD PRIMARY KEY (`uid`) USING BTREE,
  ADD KEY `date` (`date`),
  ADD KEY `YM` (`YM`),
  ADD KEY `MD` (`MD`);
 
 
ALTER TABLE `rb_accessLog`
  MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
 

 

 

하지만 접속통계는 로그인 성공에 대한 것만 일자별로 사용자 기준으로 접속하는 테이블을 구현하면 될 것이다.

CREATE TABLE `rb_accessLog_tmp` (
  `uid` int(11) NOT NULL,
  `date` char(8) NOT NULL,
  `userID` varchar(60) NOT NULL,
  `userNM` varchar(20) DEFAULT NULL,
  `YM` char(6) NOT NULL,
  `MD` char(4) NOT NULL,
  `year` char(4) NOT NULL,
  `month` char(2) NOT NULL,
  `day` char(2) NOT NULL,
  `hit` int(11) NOT NULL DEFAULT 0,
  `display` tinyint(2) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
ALTER TABLE `rb_accessLog_tmp`
  ADD PRIMARY KEY (`uid`) USING BTREE,
  ADD UNIQUE KEY `date_userID` (`date`,`userID`);
 
ALTER TABLE `rb_accessLog_tmp`
  MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

 

 

데이터를 추출하여 넣은 SQL 쿼리문은 다음과 같다.

INSERT INTO rb_accessLog_tmp (date, userID, userNM, YM, MD, year, month, day,hit) 
(select date, userID, userNM, YM, MD, year, month, day, count(*) as hit 
from rb_accessLog where success=1 group by date, userID) 
ON DUPLICATE KEY UPDATE date = VALUES(date), userID = VALUES(userID), hit=VALUES(hit)

없는 자료는 Insert 하고 중복된 자료는 특정 칼럼만 업데이트한다.

달라질 칼럼은 hit 칼럼밖에 없을 것이다.

 

중복체크하는 중요한 Key는 date_userID 인덱스 설정이다.

 

이 테이블에서 다시 일별 사용자수 접속 통계, 접속수 통계를 구분하는 테이블을 만들어보자.

CREATE TABLE rb_access_Stats (
  uid int(11) NOT NULL,
  date char(8) NOT NULL,
  YM char(6) NOT NULL,
  day char(2) NOT NULL,
  IDCnt int(11) NOT NULL DEFAULT 0,
  dailyCnt int(11) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
ALTER TABLE rb_access_Stats
  ADD PRIMARY KEY (uid),
  ADD UNIQUE KEY date (date) USING BTREE;
 
ALTER TABLE rb_access_Stats
  MODIFY uid int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

 

UNIQUE KEY 설정은 필수 사항!!!

# 일일 통계 생성 
INSERT INTO rb_access_Stats (date,YM,day,IDCnt,dailyCnt) 
(select date, YM, day ,count(distinct userID) as IDCnt,sum(hit) as dailyCnt from rb_accessLog_tmp group by date)
ON DUPLICATE KEY UPDATE date = VALUES(date), IDCnt = VALUES(IDCnt), dailyCnt = VALUES(dailyCnt)