통계를 위한 DB 설계 0 7 12,150

by 커피홀릭 [DB 모델링/설계] 통계 설계 모델링 [2010.03.04 13:31:08]



안녕하세요.
제가 DB 설계는 초보라 많은 어려움이 있네요. 모델링 관련 책도 보고 여러군데 찾아보고 했지만 아직은 잘 감이 안옵니다.

먼저, 대략의 시나리오는 이렇습니다.
광고 관련 통계를 구해야함.
WEB_로그 라는 테이블은 아래와 같습니다.

WEB_로그
------------------------
로그번호 (key)
고객ID
접속자IP
업체코드
광고코드
PAGE_ID
PAGE레벨
URL
시간

위의 로그 테이블에서 아래와 같은 결과가 필요합니다.

가입자수, 노출수, 순노출수, 중복노출비율, 반응수, 순반응수, 중복반응비율

위 항목들의 결과값의 산정은:
가입자수:    고객ID의 unique한 합
노출수:   광고코드들의 합
순노출수:    노출수/가입자수
중복노출비율: (1 - 순노출수 / 노출수) x 100
반응수:   PAGE레벨이 1인 항목들의 합
순반응수:    반응수 / 가입자수
중복반응비율: (1 - 순반응수 / 반응수) x 100

위의 산정 수식들을 보시면 아시겠지만, 어떤 항목들은 이전의 결과값을 알아야 계산이 가능한 항목들로 이루어져있습니다.
그리고 이 로그는 1달~ 1년 정도 계속 쌓여서 상당한 양의 로그가 쌓일거구요.

제가 궁금한거는
1. DB 설계할때 이런 통계는 따로 요약 테이블을 만들어 프로시져나 트리거로 날짜별로 저장한다고들 하는데요. 저 같은 경우는 날짜별이 아닌 10분 단위까지의 통계들이 필요하고, 더구나 저 위의 통계들이 고객ID별, 광고업체별, 광고별 등의 그룹별 통계또한 필요한 상황입니다. 이럴때는 어떻게 DB를 설계해야하는지요. 좀 자세하게 설명을 해주시면 감사하겠습니다.

2. VIEW 를 사용할수도 있을까요? 있다면 어떻게 하는게 효율적일까요?

3. 이전의 항목이 있어야 다음 항목의 결과를 산출할수있는데 이 경우는 쿼리로 어떠케 해결할수 있을까요?
단순히 select count distinct 고객ID, count 광고코드, (count 광고코드)/(count distinct 고객ID) .......
이런식의 쿼리 말고 다른 방법이 있는지 궁금합니다.

4. 어떠한 방법도 좋습니다. 위 테이블 하나를 쿼리로만 통계를 내는 방식 말고 모델링 측면에서 좋은 방법이있는지 알고싶습니다. OLAP이나 DW 는 쓸 상황도 아니고, 어쩜 오라클로 안갈지도 모르겠네요.

경험이 있으신분 모두 작은 답글이라도 주시면 정말 도움이 될것같습니다.
감사합니다.
by feelie [2010.03.04 14:40:03]
궁금하시점에 대하여 짧은 지식으로 몇자 적습니다.
1. 요약 테이블
- 책에서 라고 말씀하셨는데 일반적으로, 그리고 가능하면 요약테이블 사용은
않하는것이 맞습니다. 정말 필요하다면 가장 자주 사용되는 최소단위의 요약테이블을 만든는 것입니다.
- 일, 월,분기,년 으로 통계를 추출할떄 일단위의 통계는 어느정도 가능할것이구요, 월,분기,년은 최소 단위 월로 요약 테이블을 만들고 나머지 통계는 월통계를 사용하면 데이터 일관성 유지에 유리할것입니다.
2,3,
내용으로 봐서는 3번 처럼 사용하는 쿼리가 맞을것 같구요, 만약 이런 통계 쿼리가
여러 곳에서 사용된다면 해당 Query를 View로 만들어서 개발의 효율성을 높일수 있습니다.
4.다른 모델링 방식에 대해서는( 하나의 테이블, 요약테이블) 다른 방법을 찾을수없을것 같네요.. 물론 업무적으로 확인은해봐야 겠지만요.
하지만 데이터의 양이 많다면 그건 논리 모델링에서 확인하실 내용보다 물리모델링에서 파티션을 고려하셔야 할듯합니다...

by 커피홀릭 [2010.03.04 15:28:47]
feelie 님 답변 감사합니다.
궁금한게 또 있는데요. VIEW를 사용했을때 예를 들면,
create view 통계_view (가입자수, 노출수, 순노출수)
as select count distict 고객ID, count 광고코드, (count 광고코드)/(count distinct 고객ID) from WEB_로그;

이렇게 view를 만들었다고 하면, 실제 view를 쿼리할때마다 select의 조회가 매번 실행되는거죠? 그냥 쿼리로 했을때와 view를 만들어놓고 쿼리했을때와 속도차이는 차이가 있는지요?

by feelie [2010.03.04 15:43:47]
매번 실행되고, 하나의 쿼리내에서도 여러번 view를 사용하면 여러번 실행합니다.
그래서 서브쿼리 팩토링이란 걸 사용하기도 하지요(서부쿼리 팩토링을 확인해보세요)
속도는 거의 차이가 없지만 view사용이 너 늦다고 봐여죠.
실제로 뷰는 dictionary 에 저장되고 사용자가 view쿼리를 호출하면 그때 원래 엑세스쿼리로 변형해서 실행하니까요.
view를 만들면 개발의 효율성이라고 말씀드렸는데요
실제로 view를 만들때
create view 통계_view (가입자수, 노출수, 순노출수)
as
select count distict 고객ID,
count 광고코드,
(count 광고코드)/(count distinct 고객ID) 순노출수
from WEB_로그
로하면 view를 통해서 쿼리 작성시 보다 편리하겠죠..(일일이 count/count 이런 부분이 필요없으니까요)


by 커피홀릭 [2010.03.04 16:42:53]
fleelie 님의 답변 감사해요.
또 궁금한게 있는데요. 실제로 테이블 설계할때 위와 같은 경우에 주로 시간대별이나 기간별, 그리고 광고별로 조회가 많이 일어날텐데요.
쿼리의 속도 향상을 위해서 인덱스는 어느곳에 적용해야하나요?
그리고 날짜도 DATE 형식으로 하는게 좋은지 아님 다른 좋은 방법이 있는지 궁금합니다.

by 부쉬맨 [2010.03.04 18:07:20]
많이 일어나는것에 대해서 일단 index를 걸어주는게좋겟지요
( 시간대별이나 기간별, 그리고 광고별로 조회가 많이 일어날텐데요. )
라와같이 이게 주된 검색조건이 될수있으니깐요.
날짜형식은 스타일이지만 년월분시간분초 까지 보는사람들도있고요, 그냥 일 년월일까지 보는스타일이있습니다. 그건 선택하셔서 편하신데로 하시는게 좋을꺼같아요. 날짜는 왠만해서는 date형식이 좋겠죠

by feelie [2010.03.04 22:33:07]
인덱스 선정방법에는 업무적인 조회조건에 따라 달라집니다.
대용량데이터베이스에서 참고하면
인덱스 선정방법
1. 항상사용하는가?
2. 항상 = 로 사용되는가
3. 분포도가 좋은 컬럼우선
4. 정렬순서
5. 후보키
이런 방법으로 컬럼을 선정하고,컬럼의 순서를 정한다고 합니다.

시간을 표현하는 컬럼의 데이터 타입선정은 문자열과 date방식이 있는데요.
제가 설명하는것 보다
해당URL을 참고하시면 좋을듯합니다. 제가 자주 이용하는 곳이구요
http://scidb.tistory.com/entry/Varchar28-VS-Date-어느-것이-우월한가

by 커피홀릭 [2010.03.08 11:46:58]
freelie 님과 부쉬맨님 모두 감사합니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입