database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 30848 게시물 읽기
No. 30848
1500만건 데이터에 대한 일자별 UV 추출 건
작성자
박병준(pppcom7)
작성일
2015-06-22 23:45
조회수
8,307

오랜만에 커뮤니티를 찾아와 도움을 못 되고 도움 받고자 글을 올리게 되었습니다.

# 테이블 스키마 (API_LOG)

 

 

 

 

  channel_id varchar(10) DEFAULT NULL COMMENT 채널아이디,

  reg_dt timestamp NOT NULL DEFAULT 0000-00-00 00:00:00 COMMENT 등록일시,

  model_name varchar(100) DEFAULT NULL COMMENT 모델명,

  serial_no varchar(100) DEFAULT NULL COMMENT 시리얼 번호,

  ...

위와 같이 컬럼들이 존재하며 현재 데이터는 1500만건을 넘었습니다. 

데이터는 2013-12-06 부터 현재까지 생성되어 있습니다.

 

필요한 결과 값은 일자별 (reg_dt)  UV (Unique View) 통계를 서비스 오픈일 부터 당일까지 추출해 달라는 요구 사항힙니다.

Unique 기준은 CONCAT(serial_no, model_name) 와 같이 문자열을 합쳐 Unique해야 합니다.

해서 제가 쿼리를 생성한 쿼리가 다음와 같습니다.

 

select '2014-01-01' as stat_dt, count(distinct(concat(serial_no, model_name))) as stat_uv from API_LOG where reg_dt <= '2014-01-02'; 

 

이런 식으로 일자별 UV값을 구해 임시 테이블에 insert하도록 구현했습니다.

문제는 reg_dt에 index 설정이 되어 있으나 데이터 row수가 1500만건이나 되니 2014년으로 넘어오면서 

하루 쿼리 수행 시간이 50초를 넘고 있습니다.

 

일자별 UV를 구해야 하기 때문에 2013-12-06 데이터 생성 처음부터 증가한 일자별로 매번 쿼리를 날려야 하는데 

점점 속도가 저하되는 현상이 있습니다. 

이런 경우 어떤 식으로 쿼리를 해야 하는지 ? 또는 다른 방법이 있는지 도움 부탁 드립니다.

 

 

 

이 글에 대한 댓글이 총 3건 있습니다.

다들 아시는 방법이겠지만,  일단 몇가지 방법이 생각나서 적어봅니다.

 

1, <= 로 계산하는 것이 아니라, = 로 계산하여 일별 UV 값을 임시 테이블에 적재하고, 

    해당 시점까지의 UV는 임시 테이블에서 <= 로 SUM을 하시는 편이 빠를 것 같습니다. 

    예를 들면, 

   insert into temp_table 

  select '2014-01-01' as stat_dt, count(distinct(concat(serial_no, model_name))) as stat_uv   from API_LOG where reg_dt = '2014-01-02';

....

 

select '2014-01-01', sum(stat_uv) from temp_table where stat_dt <= '2014-0102;

 

2. concat(serial_no,model_name)이 사용되고 있는 부분을, 별도의 column으로 빼서,

     index를 생성하는 것도 방법일 것 같습니다. 

 

 

아싸가오리님이 2015-06-23 13:08에 작성한 댓글입니다. Edit

댓글을 올려 주셔서 감사합니다. 

하지만 말씀하신 방식으로 할 경우 허수가 발생합니다. 

일자별 UV를 구해서 DB에 넣어 놓고 기간별 UV를 구할 때 
일자별 UV를 SUM할 경우 아래와 같은 허수가 발생합니다.

 

2015-01-01 :  A 고객 방문 ( UV : 1)

2015-01-02 : A 고객, B고객 방문 (UV : 2)

2015-01-03 : A 고객, C고객 방문 (UV : 2)

 

이런식으로 일자별 UV를 DB에 넣어 놓은 상태에서 

2015-01-01 ~ 2015-01-03까지의 Unique View를 추출하라고 하면 

댓글의 방식으로 하면 SUM(UV) 하기 때문에 UV : 5 라는 값이 나오지만 

실제로 3일 간 Unique View는 UV : 3 이 나와야 합니다. 

고객 A, B, C 3명 뿐이였기 때문입니다.

박병준(pppcom7)님이 2015-06-24 09:18에 작성한 댓글입니다.

최초 접속 일자를 별도 테이블로 관리하는 방법이 있습니다.
 - 최초접속일자(serial_no, model_name, first_reg_dt)


최초 1회 갱신 작업
INSERT INTO 최초접속일자
SELECT serial_no
     , model_name
     , MIN(reg_dt) AS first_reg_dt
  FROM api_log a
 GROUP BY serial_no, model_name
;


매일 새벽 전일 접속한 로그중 최초 접속건만 이 테이블에 Insert 해주고요
INSERT INTO 최초접속일자
SELECT serial_no
     , model_name
     , '2014-01-01' AS first_reg_dt
  FROM api_log a
 WHERE reg_dt = '2014-01-01'
   AND NOT EXISTS (SELECT 1
                     FROM 최초접속일자
                    WHERE serial_no  = serial_no
                      AND model_name = model_name
                   )
;


전일까지의 누적 통계는 다음과 같이
SELECT '2014-01-01' AS stat_dt
     , COUNT(*)     AS stat_uv
  FROM 최초접속일자
 WHERE first_reg_dt < '2014-01-02'
;

마농(manon94)님이 2015-06-25 09:14에 작성한 댓글입니다.
이 댓글은 2015-06-25 09:18에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
30851증가 문자열 가져오기 [1]
loop
2015-06-29
7263
30850순차 데이터 검증 [1]
검증
2015-06-29
7190
30849Mysql 상용 라이센스 구매 관련... [1]
박경일
2015-06-29
7294
308481500만건 데이터에 대한 일자별 UV 추출 건 [3]
박병준
2015-06-22
8307
30846mysql++.h 를 이용한 리눅스 환경에서 c++ 과 mysql 연동...
질문자
2015-06-22
7986
30845성적 상위 % 를 한번에 구할수는 없나요?
이기자
2015-06-17
7491
30844두개의 table group_concat 하기 [1]
김양훈
2015-06-11
7423
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.022초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다