728x90

1) 데이터의 정확성
   - 데이터를 일자만 저장할 것인가? 시분초밀리초까지 저장할 것인가?

2) 데이터의 범위
   - 1900년 이전이나 2079년 이후의 데이터도 저장할 것인가?

Datetime 데이터형식과 Char(8) 형식>

1) Datetime 데이터형식의 장점

 - 우선 Datetime 형식은 날짜시간을 저장하기 위해 특화된 데이터형식입니다.

 - 잘못된 날짜형식(예를 들어, 20070840 과 같은 데이터)는 입력할 수 없도록 자동 체크해 줍니다.

 - 또한, 다양한 날짜시간관련함수(dateadd, datediff, datepart, year, month, day 등)를 지원합니다.

 - 그러므로, 날짜시간형 데이터에는 DateTime을 사용하시는 것이 바람직합니다.

2) Char(8) 데이터형식을 선호하는 이유

 - datetime 형식은 정확하게 원하는 날짜를 선택하기 위해서는 정확한 데이터형식의 이해를 기반으로 쿼리를 작성해야 합니다. 예를 들어, 오늘날짜의 데이터를 조회하기 위해서는 where 날짜 between '2007-08-06 00:00:00.000' and '2007-08-06 23:59:59.997'로 명확하게 지정하거나, where 날짜 >= '2007-08-06' and 날짜 < '2007-08-07'로 지정해 주어야 합니다.

 - 날짜의 오른쪽 한계값을 '2007-08-06 23:59:59.999'로 잘못 지정하는 경우, '2007-08-07 00:00:00.000'로 반올림되게 되어 잘못된 결과가 반환되는 원인이 될 수 있습니다.

 - 그 외에도 데이터의 가공 등에 잘못된 오해로 인한 사용자 실수를 방지하기 위해 비교적 실수할 가능성이 적은 char(8) 형식을 선호하였습니다.

 - 하지만, 분명히 datetime 데이터형식을 이해하고 사용한다면, 위의 datetime의 장점과 같이, 동일한 저장공간으로도 더 정확한 데이터를 관리할 수 있고, 잘못된 데이터입력에 대한 유효성검사도 내장하고 있기 때문에, datetime 데이터형식을 사용하는 것이 바람직하다고 할 수 있습니다.

==> 결국, 날짜시간 데이터에는 datetime 데이터형식을 사용하는 것이 바람직하나,  비즈니스 요구사항에 따라 또는 필요에 따라 char(8)로 대체하여 사용할 수도 있습니다.

Datetime 데이터형과 Smalldatetime 데이터형)

 - Datetime은 8 바이트로 저장되고 smalldatetime은 4 바이트로 저장됩니다.

 - datetime 형식은 1753년 1월 1일에서 9999년 12월 31일까지 1/300초(3.33밀리초 또는 0.00333 초)의 정확성을 가진 날짜 및 시간 데이터입니다.

 - 1smalldatetime 형식은 900년 1월 1일에서 2079년 6월 6일까지 분 단위의 정확성을 가진 날짜 및 시간 데이터입니다. 29.998 초 이하의 근사치 분으로 버림되며 29.999 초 이상의 값은 근사치 분으로 반올림됩니다.

==> 그러므로 날짜의 데이터범위와 정확성을 근거로 datetime과 smalldatetime을 선택하시면 됩니다.

위의 내역에 관련해서는 우선 온라인 설명서의 각 데이터 형식의 설명 부분을 읽어보시면 도움이 되시리라 판단됩니다. 그외에도 inside SQL Server 2005 T-SQL Programming 1 장에 보면 참고하실만한 내용이 언급됩니다.

출처 : DBGuide

[출처] DateTime ? Char(8) or (10)|작성자 우리자나

'데이터베이스 > SQL Server' 카테고리의 다른 글

배열을 테이블(row)로 변환 where in에 사용하기 적합  (0) 2008.04.29
퀴즈 문제  (0) 2008.04.29
세로 컬럼들을 가로 컬럼으로 모으기.... pivot 쿼리  (0) 2008.04.29
컬럼명 변경하기  (0) 2008.04.29
Table 변수 vs 임시 테이블 (Ver. 2000)  (0) 2008.04.29
728x90
[ 간단한 기본지식 ]
#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 필드 가지고 놀기 |작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

잠금 정보를 보다 쉽게...sp_lock2  (0) 2008.04.29
[mssql]환상의 CROSS JOIN  (0) 2008.04.29
[mssql]Top N 쿼리의 숨겨진 기능  (0) 2008.04.29
[mssql]sp_lock... 그 뒤에 숨은 더 많은 정보들  (0) 2008.04.29
[mssql]SQL에서 블록킹과 데드락을 처리하기 위해서 어떻게 해야 되나요?  (0) 2008.04.29
728x90

검색 조건에 특정 기간(datetime)을 주어 조회할 경우 잠깐! 생각해 보자~!


예를 들어 2007-01-01 ~ 2007-01-10의 기간동안  가입한 회원 목록을 조회해야 한다면~
BETWEEN이나.. >= <= 를 이용하여... [ EX1 ]과 같은 방법으로 많이 한다~!


간단히 생각하믄... 맞는결과가 나올듯 하지만... 그렇지 않다.
검색필드가  datetime형식이므로  [년-월-일]은 [년-월-일 00시 00분 000초]인 셈이다.
그래서 [ EX1 ]의 방법을 이용할 경우 2007-01-10의 데이터도 조회되길 원하였으나 결과는 그렇지 못할 것이다.


그래서..[ EX2 ]처럼... 조회하였다...  훔... 허접해. 보인닷!~~~ 그러나 결과는.. 굿~!


그럼... [EX3]은??  마지막날에 +1일을 더하여 마지막날의 데이터도 포함할 수 있도록 하는것이다~..
그중에.. 이게 젤루 맘에 든다...
물론.. 2007-01-11 00:00:000 인 데이터가 존재한다면... 이건 에러다 -_-;; 
하루를 더한것에서 -1 millisecond를 빼? ㅋㅋ (생략하겠음~ -_-;;)


마지막으로.. [EX4]는 필드값을 [YYYY-MM-DD]형식으로 변형하여 조회할 수도 있겠다~

그러나 값을 변형하여.. 보여주는것도 그렇고.. 인덱스도 안걸어 놓겠지만.. 만약 있다면 인덱스 걸리지도 않을꺼구... 맘에.. 안든다..


암튼 방법이야 많겠지만... [ EX1 ]처럼... 틀린 검색 조건을 주어서는 안된다는걸.. 말하고 싶을 뿐이닷~


-- [ EX1 ]
SELECT *
FROM dbo.MEMBER
WHERE 등록일시 BETWEEN '2007-01-01' AND '2007-01-10'


-- [ EX2 ]
SELECT *
FROM dbo.MEMBER
WHERE 등록일시 BETWEEN '2007-01-01' AND '2007-01-10 59:59:999'


-- [ EX3]
SELECT *
FROM dbo.MEMBER
WHERE 등록일시 BETWEEN '2007-01-01' AND DATEADD(DAY, 1, '2007-01-10')


-- [ EX4]
SELECT *
FROM dbo.MEMBER
WHERE CONVERT(VARCHAR(10), 등록일시, 121) BETWEEN '2007-01-01' AND '2007-01-10'

[출처] [MSSQL] 특정 기간(datetime형식)을 주어 조회할 경우 주의!|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
[MSSQL2005] LIKE 검색시 [ ] 와일드카드사용하기  (0) 2008.04.28
[MSSQL2005] 순위함수 (ROW_NUMBER, RANK, DENSE_RANK, NTILE)  (0) 2008.04.28
DB별 제약 조건을 쿼리로 뽑기  (0) 2008.04.28

+ Recent posts

티스토리툴바