쿼리연습 (겹치는 날짜 검사하기)

개요

• 새로운 데이터를 추가할 때, 날짜별로 겹치는 데이터가 있는지 밸리데이션 하는 경우가 종종 있다.
• 이상하게도 나는 날짜가 겹치는지 체크하는 로직을 생각할 때 헷갈리는 경우가 많아서 이번에 그림으로 정리하고 넘어가려고 한다.

 

테이블

create table employee
(
    employee_id   bigint 
            primary key,
    employee_name varchar(255) not null,
    begin_date    varchar(8) not null,
    end_date      varchar(8) not null
);

 

데이터

INSERT INTO employee (employee_id, employee_name, begin_date , end_date)
VALUES (1, 'Alice', '20220101' , '20231231')

 

문제

같은 사원명에 대해서 날짜가 겹치는 데이터가 있는지 검사하는 쿼리를 작성하라

 

풀이

• 정답은 생각보다 매우 간단하다. 정답은 아래와 같다.

    SELECT 1
    FROM employee
    WHERE employee_name = 'Alice' AND
          begin_date <= :newEndDate AND
          end_date >= :newBeginDate
    LIMIT 1

• 이제 위 쿼리로 겹치는 부분을 전부 체크할 수 있는지 확인해 보자
• 날짜가 겹치는 부분은 크게 아래와 같이 4가지 이다.

 

기존 데이터의 좌측으로 겹치는 경우

• 초록색이 기존에 디비에 저장된 데이터의 날짜 정보이고, 빨간색이 새로 추가될 데이터의 날짜 정보다.
• 위 경우에 begin_date <= :newEndDate AND end_date >= :newBeginDate 로 검사할 수 있다.

 

기존 데이터의 우측으로 겹치는 경우

• 우측으로 겹치는 경우에도 begin_date <= :newEndDate AND end_date >= :newBeginDate 로 검사할 수 있다.

 

기존 데이터의 안쪽으로 겹치는 경우

• 안쪽으로 겹치는 경우에도 begin_date <= :newEndDate AND end_date >= :newBeginDate 로 검사할 수 있다.

 

기존 데이터의 바깥쪽으로 겹치는 경우

• 바같쪽으로 겹치는 경우에도 begin_date <= :newEndDate AND end_date >= :newBeginDate 로 검사할 수 있다.

 

이제 QueryDsl로 쿼리를 구현해보자

• QueryDsl 을 사용할때, 존재여부를 체크할 때에는 count 쿼리보다 exist 쿼리를 유도하는게 중요하다.
  • count 쿼리는 결과를 모두 찾을 때까지 검사하는 반면, exist는 한건이 발견되는 순간 탐색을 멈추기 때문에 훨씬 빠르다.
• 도입부에서 언급한 정답이 바로 exist 와 같은 결과이다. 쿼리는 아래와 같다.

    SELECT 1
    FROM employee
    WHERE employee_name = 'Alice' AND
          begin_date <= :newEndDate AND
          end_date >= :newBeginDate
    LIMIT 1

• 아래와 같이 QueryDsl로 구현할 수 있다.

    public boolean isDuplicated(String employeeName, String beginDate, String endDate) {
        QEmployee employee = QEmployee.employee;

        Integer fetchFirst = queryFactory
                .selectOne()
                .from(employee)
                .where(
                        employee.employeeName.eq(employeeName),
                        employee.beginDate.loe(endDate),
                        employee.endDate.goe(beginDate)
                ).fetchFirst();

        return fetchFirst != null;

    }

• 실제로 날라간 쿼리는 아래와 같다.