2.7-1 Dates

use-the-index-luke 저자 Markus Winand

---------------------------------------------

Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.

---------------------------------------------

You can upload a Korean translation of use-the-index-luke.com on your blog

Thank you from the bottom of my heart to author Makus Winand for allowing me.

 

These are translations that I use for studying by using a papago (google translate)

The translations may not be correct or there may be a typo.

I'd appreciate it if you could point it out in the comments.

---------------------------------------------

---------------------------------------------

use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고

허락해주신 Makus Winand 저자님께 진심으로 감사합니다.

 

이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서 

번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.

댓글로 지적해주시면 감사하겠습니다.

---------------------------------------------

 

https://use-the-index-luke.com/sql/where-clause/obfuscation/dates

ㄴ 날짜유형

대부분의 난독화에는 DATE 유형이 포함됩니다.

Oracle Database는 항상 시간 구성 요소를 포함하는 날짜 유형이 하나뿐 이므로 이 점에서 특히 취약합니다.

 

시간 구성 요소를 제거하기 위해 TRUNC 함수를 사용하는 것이 일반적인 관행이 되었습니다.

사실, 오라클 데이터베이스에 순수한 날짜 유형이 없기 때문에 시간을 제거하지 않고 자정으로 설정합니다.

검색의 시간 구성 요소를 무시하려면 비교대상 양쪽에다 TRUNC 함수를 사용하면 됩니다.

예를 들어 이 쿼리는 어제의 매출을 검색할 수 있습니다.

----------------------------------------------------------

SELECT ...
  FROM sales
 WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

----------------------------------------------------------

완전히 유효하고 정확한 문장이지만 SALE_DATE의 인덱스를 제대로 사용할 수 없습니다. TRUNC(SALE_DATE)는 SALE_DATE와 완전히 다릅니다.

함수는 데이터베이스에 대한 블랙박스 입니다.

 

이 문제에 대한 간단한 해결책이 있습니다.

바로 Function based Index 입니다.

----------------------------------------------------------

CREATE INDEX index_name
          ON sales (TRUNC(sale_date))

----------------------------------------------------------

그러나 항상 where 절에 TRUNC(sale_date)를 사용해야 합니다.

일관성 없이 사용할 경우, 때로는 TRUNC 없이 사용할 경우, 두개의 Index가 필요합니다!

 

이 문제는 다음 MySQL 쿼리에 표시된 것 처럼 더 긴 기간을 검색할 경우 순수한 날짜 유형을 가진 데이터베이스에서도 발생합니다.

----------------------------------------------------------

SELECT ...
  FROM sales
 WHERE DATE_FORMAT(sale_date, "%Y-%M")
     = DATE_FORMAT(now()    , "%Y-%M")

----------------------------------------------------------

쿼리는 연도와 월만 포함하는 날짜 형식을 사용합니다. 다시말하지만, 이는 이전과 동일한 문제가 있는 절대적으로 정확한 쿼리입니다.

그러나 위의 솔루션은 버전 5.7이전의 MySQL에는 적용되지 않습니다.

MySQL은 버전 5.7 이전의 함수 기반 인덱싱을 지원하지 않았기 때문입니다.

 

또 다른 방법은 명시적 범위 조건을 사용하는 것 입니다.

이것은 모든 데이터베이스에 적용되는 일반적인 솔루션입니다.

 

----------------------------------------------------------

SELECT ...

  FROM sales

 WHERE sale_date BETWEEN quarter_begin(?)

                     AND quarter_end(?)

----------------------------------------------------------

 

만약 여러분이 숙제를 했다면, 여러분은 아마도

42세의 모든 직원들에 대한 연습의 패턴을 인식할 것입니다.

 

SALE_DATE의 직선 인덱스는 이 쿼리를 최적화하기에 충분합니다. quarter_begin 및 quarter_end 함수는 경계 날짜를 계산합니다. 구간 연산자에는 항상 경계 값이 포함되므로 계산이 약간 복잡해질 수 있습니다.

따라서 SALE_DATE에 시간 구성 요소가 있는 경우

quarter_end 함수는 다음 분기의 첫 번째 날 직전에 타임 스탬프를 반환해야 합니다. 이 논리는 함수에 숨겨질 수 있습니다.

 

다음 예제에서는 다양한 데이터베이스에 대한

QUARTER_BEGIN and QUARTER_END 함수의 구현을 보여줍니다.

 

----------------------------------------------------------

CREATE FUNCTION quarter_begin(dt IN DATE)
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END

CREATE FUNCTION quarter_end(dt IN DATE)
RETURN DATE
AS
BEGIN
   -- the Oracle DATE type has seconds resolution
   -- subtract one second from the first
   -- day of the following quarter
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q')
        - (1/(24*60*60));
END

----------------------------------------------------------

다른 기간에도 유사한 보조 기능을 사용할 수  있습니다. 특히 (>=) 이상 및 (<) 미만의 조건을 사용하는 경우 대부분 위의 예제보다 덜 복잡합니다. 물론 당신이 원한다면 당신의 어플리케이션에서 경계 날짜를 계산할 수 있습니다.

----------------------------------------------------------

Tip

 

연속된 기간에 대한 쿼리를 명시적 범위 조건으로 씁니다. 단 하루라도 이 작업을  수행합니다.

(예: Oracle Database의 경우)

sale_date >= TRUNC(sysdate)
AND sale_date <  TRUNC(sysdate + INTERVAL '1' DAY)

----------------------------------------------------------

또 다른 일반적인 난독화는 다음 Postgre에서 모듯이 날짜를 문자열로 비교하는 것 입니다.

SQL 예제:

----------------------------------------------------------

SELECT ...
  FROM sales
 WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'

----------------------------------------------------------

문제는 역시 SALE_DATE를 변환하는 것 입니다.

이러한 조건은 종종 숫자와 문자열 이외의 다른 유형을 데이터베이스에 전달할 수 없다는 믿음으로 생성됩니다.

그러나 바인딩 매개 변수는 모든 데이터 유형을 지원합니다.

이는 예를 들어 java.util을 사용할 수 있음을 의미합니다.

개체를 바인딩 매개 변수로 날짜 지정합니다.

이는 바인딩 매개변수의 또 다른 이점입니다.

 

이 작업을 수행할 수 없는 경우 테이블 열 대신 검색어를 변환하면 됩니다.

----------------------------------------------------------

SELECT ...
  FROM sales
 WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')

----------------------------------------------------------

이 쿼리는 SALE_DATE의 직선 인덱스를 사용할 수 있습니다. 또한 입력 문자열을 한 번만 변환합니다. 이전 무구는 테이블에 저장된 모든 날짜를 변환해야 검색어와 비교할 수 있습니다.

 

바인딩 매개변수를 사용하거나 비교의 다른 쪽을 변환하여 변경하는 경우

SALE_DATE에 시간 구성 요소가 있으면

버그를 쉽게 발생시킬 수 있습니다.

이 경우 명시적 범위 조건을 사용해야합니다.

----------------------------------------------------------

SELECT ...
  FROM sales
 WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD')
   AND sale_date <  TO_DATE('1970-01-01', 'YYYY-MM-DD')
                  + INTERVAL '1' DAY

----------------------------------------------------------

 

날짜를 비교할 때는 항상 명시적 범위 조건을 사용하는 것이 좋습니다.