[mssql]Group By 와 DateTime 필드 가지고 놀기

[ 간단한 기본지식 ]
#1. 대상필드에 인덱스가 걸려있을경우 일정조건을 만족하면 쿼리 어널라이저가 인덱스를 이용할 수 있다.
#2. 구문에서 대상필드에 변형을 일으키게 되면 무조건 인덱스를 이용하지 않는다.
#3. DateTime형은 1/1000 단위로 시간을 기록한다.

[ 문제 ]
이번에 사이트 이용정보중 이번달 로그인 정보의 일별통계를 나타내는 일이 주어졌다. 로그인 정보 테이블의 필드는
다음과 같다.

Table : Login_Log
L_time    DateTime   8   NOT NULL    :  로그인 시간
G_ID       int             4   NOT NULL   :  로그인 그룹 ID
clustered index : L_time desc , g_id asc

여기서 아주 쉽게 일별 통계를 내는 방법은 다음과 같다.

SELECT left(convert(varchar,l_time,120),10) as l_time, count
(*) as hit
FROM Login_log
WHERE l_time between '2004-10-01' and '2004-11-01'
GROUP BY left(convert(varchar,l_time,120),10)
ORDER BY l_time desc

하지만 이렇게 했을경우 l_time 필드에 변형을 주었기 때문에 group_by시에 인덱스를 무조건 이용할 수 없다. (1번째
문제점)
하지만 WHERE정릐 경우는 l_time 필드에 변형이 없으므로 인덱스를 이용하게 된다.
그리고 이런 逆컥막?그냥 group by를 이용했을 경우 이용자가 없는 날의 경우 0이라고 출력되지 않고 아예 그날은 출
력되지 않는다.
(GROUP BY는 값이 있는것의 집합을 출력하죠? ^^;)(2번째 문제점)


자.. 여기서 2번째 문제점을 해결하는 방법은 GROUP BY ALL 이라는 구문을 이용해 보았습니다. GROUP
BY ALL의 경우 없는값의 경우 0이라고 표시해 주는 구문입니다. 하지만 이경우 모든 경우를 다 검사한다는 의미를 가
지고 있기 때문에 1번의 table full scan이 발생하게 됩니다.
데이터가 적은 경우라면 문제가 되지 않겠지만 대용량의 경우 문제가 발생할 수 있습니다.
예를들어 제가 테스트하는 사이트의 경우 1일 로그인 로그가 4000~5000 가량 쌓이게 됩니다. 100일이면 40만~50만, 1
년이면 100만을 훌쩍 뛰어넘게 됩니다. 이런 경우에 table full scan은 치명적인 요소로 작용하게 됩니다.

자.. 이제 2가지의 문제점이 발생했고 2가지 모두 해결하지 못한 상태입니다.

1번째 경우를 해결하기 위해서는 다음과 같은 방법을 생각해 보았습니다.
l_time 필드는 l_date, l_time 두개의 필드로 나누고, 다음과 같이 정렬한다.

l_date   char   8   NOT NULL
l_time   char   6   NOT NULL
l_date는 20041001 과 같은 형태로 날짜값을 넣는다. (yyyymmdd)
l_time은 235411 와 같은 형태로 시간값을 넣는다.(hhmmss)

이렇게 날짜값을 가지고 있을 경우 group by 때

SELECT l_date, count(*) as hit
FROM Login_log
WHERE l_date between '20041001' and '20041031'
GROUP BY l_date
ORDER BY l_date desc

이렇게 나타낼 경우 날짜값은 인덱스를 타게된다. 이로써 1번 문제가 해결되었다.

이제 2번문제가 남았다. 2번 문제를 해결하기 위해서는 쿼리 이외에 다른 방법을 생각해 보았다.

먼저 다음과 같은 테이블을 생성하였다.

Table : D_Date
d_date    char  8   NOT NULL
index : d_date

이제 이 테이블에 2004년부터 2020년까지의 날자값을 입력하였다.
입력에는 다음과 같은 Stored Procedure를 이용하였다.

--시작일부터 일정일동안 날짜 하루하루 추가하기
DECLARE @i int
DECLARE @sd datetime
SET @i = 1
set @sd = '2004-01-01'

WHILE (@i < 7000)
BEGIN
INSERT INTO D_DATE (d_Date) values (left(Convert(char,@sd,120),10))
set @sd = dateadd(day,1,@sd)
set @i = @i + 1
END

이제 이 녀석을 이용해 group by한 집합과 Left Join을 시키면 group by값이 없을경우 0이라는 값을 넣은 결과를 뽑아
낼 수 있다.
쿼리를 작성한다면 다음과 같은 형태가 될것이다.

SELECT X.d_date, ISNULL(Y.hit,0) as hit FROM
d_date X left join
(SELECT l_date, count(*) as hit
FROM Login_log
WHERE l_date between '20041001' and '20041031'
GROUP BY l_date
) Y
ON X.d_date=Y.l_date
ORDER BY X.d_date desc

[출처] [mssql]Group By 와 DateTime 필드 가지고 놀기 |작성자 필립박