2021 04 16

날짜와 시간을 모두 포함하는 컬럼에서 날짜 범위를 올바르게 검색하는 방법

날짜 형식을 명확하게 기술해야 한다.

yyyy-mm-dd, yyyymmdd나 yyyy-mm-dd hh:mm:ss[.nnn]처럼 -> 국가마다 날짜 형식이 다르기 때문이다.

묵시적 날짜 변환 기능에 의존하지 말고, 명시적으로 날짜 변환 함수를 사용하는것이 좋다.

7/4일 하루의 에러 로그를 보고싶다. 쿼리를 짜보자.

WHERE L.logDate = CASE('7/4/2016' AS datetime) -- XXX  
WHERE L.logDate = CONVERT(datetime, '2016-07-04', 120)

하지만 위 쿼리의 실행 결과는 반환되는 결과가 없을것이다. LogDate 컬럼을 datetime 이라 날짜와 시간까지 갖고있는데,
시간을 입력하지 않으면 00:00:00으로 자동 변환되어 처리될것이기 때문이다..

그렇다고 해서 CASE(L.logDate AS date)처럼 해당 컬럼에서 시간 정보를 제거하면, 해당 컬럼에 인덱스가 있을때 인덱스를 사용할 수 없다.

시간문제를 해결한 쿼리

WHERE L.LogDate BETWEEN CONVERT(datetime, '2016-07-04, 120) AND  
L.LogDate CONVERT(datetime, '2016-07-04 23:59:59.999, 120)

하지만 이 쿼리에도 문제가 있다. SQL Server에서 datetime타입의 정확도는 3.33ms이다. 따라서 2016-07-04 23:59:59.999를

2016-07-05 00:00:00.000으로 반올림 해준다. 결국 잘못된 결과를 반환한다.

특정 일자의 로그메시지를 볼때 유용한 쿼리

WHERE L.LogDate >= CONVERT(datetime, '2016-07-04', 120)  
AND  
L.LogDate < CONVERT(datetime, '2016-07-05', 120)

끝으로, DATEADD 함수를 사용해서

데이터베이스 엔진이 인덱스를 사용하도록 사거블 쿼리를 작성하자.

DBMS엔진이 인덱스를 잘 활용하려면 쿼리의 서술논리절(WHERE, ORDER BY, GROUP BY, HAVING절)이 인덱스를 사용해야 하는데,

이를 사거블 이라는 용어로 표현한다. 쿼리가 사거블이 되지않는 이유를 이해하는것이 중요하다.

• 사거블 연산자 : =, >, <, >=, <=, BETWEEN, LIKE(%가 없는,), IS[NOT]NULL

• 사거블이지만, 성능 향상 목적으로는 사용하지 않는다.: <>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE
  인덱스를 사용하지 못할 때는 다음과 같다.

• WHERE절에서 연산하는 함수를 사용하는 쿼리 (but 인덱스 자체에서 동일한 함수가 포함되어있지 않은경우)

• WHERE절에서 필드에 대해 수치연산을 하는경우

• LIKE 에서 %를 사용하는 경우

아래의 쿼리는 EmpDOB에 인덱스가 추가되어 있음에도 넌사거블 쿼리이다. 왜냐 각 로우에서 함수를 호출하거든.

WHERE YEAR(EmpDOB) = 1950;

반면, 아래는 사거블 방식으로 동일한 데이터를 조회하는 쿼리이다.

WHERE EMPDOB >= CASE('1950-01-01' AS Date)  
AND EMPDOB < CASE('1951-01-01' AS Date);

NULL 허용 컬럼에서 특정 이름을 찾는 사거블 쿼리

WHERE EmpLastName = 'Viescas'  
OR EmpLastName IS NULL;

OR를 사용하면 EmpLastName 컬럼에 있는 인덱스를 사용하지 못할 수도 있다.

WHERE EmpLastName = 'Viescas'  
UNION ALL   
WHERE EmpLastName IS NULL

아래의 쿼리는 해당 필드에서 계산을 수행하므로 넌사거블 쿼리이다.

인덱스는, 사용되지 않으며, 모든 로우에서 수행한다.

WHERE EmpSalary \* 1.10 > 100000;

하지만 계산에 필드가 포함되어있지 않으면 사거블 쿼리가 된다.

WHERE EmpSalary > 100000/1.10;

LEFT 조인의 오른쪽데이터를 올바르게 걸러내자.

차집합 연산을 하려면 어떻게 해야할까..

예를들어서, 주문을 한번도 하지 않은 고객을 조회하려면? 전체 고객 집합에서 주문 고객 집합을 빼면 된다.

Customers LEFT OUTER JOIN Orders
-- Orders 테이블의 기본키에 NULL값 검사 조건을 달아야 한다.

설명하자면, LEFT OUTER JOIN을 하게되면 전체 고객의 로우를 가져오되, 주문 이력이 있는 회원은 주문번호를 가져오게 된다.

주문 이력이 없는 회원은 Orders 테이블의 기본키에 NULL값을 가지고 오겠지. 예를들어 order_id.

orders 테이블의 기본키에 NULL값을 가지는 로우가 바로 주문하지 않은 고객, 즉 전체 고객 집합에서 주문고객 집합의 결과를 뺀 차집합의 결과이다.

모든 고객과 특정 날짜의 일부 주문을 보여주는 잘못된 쿼리

SELECT c.CustomerId, o.orderNumber, o.OrderDate  
FROM Customers AS c  
LEFT JOIN Orders AS o  
ON c.CustomerId = o.CustomerId  
WHERE o.OrderDate BETWEEN CASE('2015-10-01' AS DASE)  
AND CASE('2015-12-31' AS DATE)

위 결과는 고객 일부가 빠져있을것이다. 왜냐 주문하지 않은 고객은, orderDate가 NULL로 채워질텐데, WHERE 절에서 NULL은 계산할 수 없는 값이기 때문이다.

결국 위 쿼리는 INNER JOIN을 사용한 결과와 같다.

뒤에 ISNULL을 넣거나, 필터링된 주문데이터와, 모든고객을 아우터조인으로결합하는게 올바른 방법이다.