예약가능시간 조회 SQL query

개요

예약 시스템 개발건이 들어와서 고객과 업무협의를 했다.
요구사항 중 하나가 사용자 신청화면 달력에서 신청가능한 시간이 하나라도 있으면 ‘신청가능’ 버튼을 달력에 보여주고 모두 완료되었으면 ‘예약마감’이라고 보여달라는 것이였다.
해당 요구 사항은 다음과 같다.

요구사항

  • 오늘기준 내일부터 15일 이후까지 신청가능(토요일, 일요일 제외)
  • 신청가능 시간은 09:20분 ~ 17:40분 20분 단위 (09:20, 09:40, 10:00, …. 17:40)
  • 점심시간 제외 (12:00, 12:20, 12:40)
  • 목요일은 16:40 까지만 신청가능 (17:00, 17:20, 17:40 제외)
  • 관리자가 추가로 특정 신청 시간을 비활성화할 수 있음
  • 신청 시간당 한명만 신청가능

분석

20분 단위 신청가능 시간은 변동될 일이 없다고했고 시간을 관리자가 따로 입력하지않기 때문에
신청가능 시간을 디폴트로 생성하고 추가로 비활성화한 시간만 막는 방식으로 개발하기로했다.
그리고 달력에 ‘신청가능’ or ‘예약마감’ 을 표시하기위해 어떻게 해야할까 깊은 고민에 빠지게됐다

고민점심시간이랑… 목요일이 어쩌구.. 관리자가 추가를…

개발

  • 고민 끝에 날짜별로 신청가능한 시간만 DB query로 가져오기로 정했다.

먼저 기본 신청가능 시간테이블(BASE_TIME)과 관리자 비활성 시간을 담는 테이블(BLOCK_DATE)을 만든다.
DBMS는 Oracle이다.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE BLOCK_DATE (	
    "BLOCKDATE" VARCHAR2(8 BYTE), 
    "BLOCKTIME" VARCHAR2(5 BYTE)
);
CREATE TABLE BASE_TIME ("BASETIME" VARCHAR2(5 BYTE) NOT NULL ENABLE);
INSERT INTO BASETIME VALUES ('09:20');
INSERT INTO BASETIME VALUES ('09:40');
~~~
INSERT INTO BASETIME VALUES ('17:40');
COMMIT;

계층쿼리를 이용해 15일간의 날짜 테이블을 만든다. 목요일 조건을 검색하기위해 요일정보도 같이 셀렉트했다.

1
2
3
SELECT TO_CHAR(FIRST_DAY+LEVEL,'YYYYMMDD') AS DATES, TO_CHAR(FIRST_DAY+LEVEL,'D') AS DY 
FROM (SELECT to_date(?, 'YYYYMMDD') FIRST_DAY FROM DUAL) 
CONNECT BY FIRST_DAY+LEVEL <= FIRST_DAY + 15

?에 바인딩되는 값은 오늘 날짜다(YYYYMMDD)
‘20210122’를 넣으면 아래와 같이 결과가 나온다.

DATES DY
20210123 7
20210124 1
20210125 2
… …
20210206 7

날짜 테이블과 기본시간 테이블을 조인하고 점심시간과, 토,일요일 제외하고, 목요일 조건도 빼주자.
위에서 만든 날짜와 시간 테이블을 with절로 정의해서 조인했다.
그다음 신청테이블에서 이미 신청된 시간과 관리자가 비활성화한 시간을 가져와서 제외시키면된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH BASEDATE AS ( 
    SELECT TO_CHAR(FIRST_DAY+LEVEL,'YYYYMMDD') AS DATES, TO_CHAR(FIRST_DAY+LEVEL,'D') AS DY 
    FROM (SELECT to_date(?, 'YYYYMMDD') FIRST_DAY FROM DUAL) 
    CONNECT BY FIRST_DAY+LEVEL <= FIRST_DAY + 15 
), 
AVAILABLE_TIME AS ( 
    SELECT DATES, BASETIME AS TIMES  
    FROM BASEDATE, BASE_TIME 
    WHERE DY != 1 AND DY != 7 AND DY != 5 OR (DY = 5 and BASETIME NOT IN (?))
), 
BLOCK_TIME AS ( 
    SELECT reservation_date AS DATES, reservation_time AS TIMES FROM 신청테이블  
    WHERE reservation_date BETWEEN TO_DATE(?, 'YYYYMMDD') + 1 AND TO_DATE(?, 'YYYYMMDD') + 15 
    UNION 
    SELECT blockdate AS DATES, blocktime AS TIMES FROM BLOCK_DATE 
    WHERE  blockdate BETWEEN TO_DATE(?, 'YYYYMMDD') + 1 AND TO_DATE(?, 'YYYYMMDD') + 15 
) 
        
SELECT * FROM AVAILABLE_TIME  
MINUS  
SELECT * FROM BLOCK_TIME
  • AVAILABLE_TIME의 WHERE절

DY != 1 AND DY != 7 – 토요일, 일요일은 제외

DY != 5 OR (DY = 5 and BASETIME NOT IN (?)) – 목요일인 경우 추가시간 제외

? 에 바인딩되는 목요일 제외시간은 Java Class에 정의했다.

1
2
3
4
5
6
7
class BlocktimeUtil {
	private static final List<String> THURSDAY_BLOCK_TIME = Arrays.asList("17:00", "17:20", "17:40");
	
	public static String getThursDayBlockTimeStr() {
		return THURSDAY_BLOCK_TIME.stream().map(s -> "'" +s+ "'").collect(Collectors.joining(","));
	}
}

결과는….?? SUCCESS ㅎㅎㅎ

DATE TIMES
20210123 09:20
20210123 09:40
20210125 14:20
20210125 14:40
… …

더 생각해볼 것

해놓고 보니 아직 데이터베이스 중심의 사고방식에서 벗어나지 못한 것 같다.
JPA를 사용하면 비즈니스 로직을 JAVA에서 객체지향적으로 프로그래밍할 수 있을지 커스터마이징 해봐야겠다.