재귀문과 Oracle 날짜 타입 정리

https://school.programmers.co.kr/learn/courses/30/lessons/59413?language=oracle

1. 재귀 쿼리 쓰는 법 정리

재귀 쿼리는 데이터 생성이나 계층적 데이터 조회할 때 많이 쓰인다. 이하에서는 초보적인 단계에서의 재귀문 활용에 대해 살펴보자...
어려운 재귀문은 진짜 많이 어렵기에... 해당 버전은 나중에 HackerRank 문제 재정리할때 다시 정리하겠습니다...
특히 DBMS마다 방식이 달라서 헷갈릴 수 있는데, 잘 구분해서 알아둘 것!

1.1 MySQL & PostgreSQL

• 문법: WITH RECURSIVE를 사용
• 예제:

  WITH RECURSIVE TBL AS (
      SELECT 0 AS HOUR
      UNION ALL
      SELECT HOUR + 1
      FROM TBL
      WHERE HOUR < 23
  )
  SELECT * FROM TBL;

• 기본 구조: 앵커 쿼리(초기값)와 재귀 쿼리(이전 결과 기반)로 구성됨.

1.2 SQL Server

• 문법: WITH만 사용 (RECURSIVE 키워드는 없음)
• 예제:

  WITH TBL AS (
      SELECT 0 AS HOUR
      UNION ALL
      SELECT HOUR + 1
      FROM TBL
      WHERE HOUR < 23
  )
  SELECT * FROM TBL
  OPTION (MAXRECURSION 100);

• 기본 구조: 앵커 쿼리와 재귀 쿼리 구조 같으며, 기본 재귀 깊이가 100회로 제한돼 있어서 OPTION (MAXRECURSION n) 옵션으로 필요시 변경할 것.

1.3 Oracle

• 문법: Oracle은 재귀 CTE 지원 없이 CONNECT BY 방식을 사용
• 예제:

  SELECT LEVEL - 1 AS HOUR
  FROM DUAL
  CONNECT BY LEVEL <= 24;

• 기본 구조: LEVEL 가상 컬럼을 활용해서 계층적 데이터 또는 반복 데이터를 쉽게 생성할 수 있음.

---

2. Oracle에서 TIMESTAMP 변환이 필요한 이유

갑자기 이게 왜 나왔는고 하니... 최상단에 쓴 주소에 들어가면 데이터 테이블이 아래와 같은 구조인데,

EXTRACT(HOUR FROM DATETIME) 을 하면 에러가 뜸... 왜냐하면 Oracle에는 다른 DBMS에서 쓰이는 DATETIME 타입이 없고, 위에 있는 사진은 다른 DBMS에서 표기된 바에 따른 것으로 판단됨.
Oracle의 경우 날짜관련 DATA TYPE은 DATE와 TIMESTAMP라는 두 가지 타입이 있는데,
각기 날짜 및 시간 정보를 추출할 때에 가능한 TYPE이 다름.

• DATE 타입: 날짜와 시간을 저장하지만, 밀리초 이하의 정밀도나 시간대 정보는 없어서, 일부 상황에서 정보가 손실될 수 있음.
  연 월 일에 대한 EXTRACT 함수 사용은 가능하지만
  시 분 초 단위에 대한 EXTRACT 함수 사용은 불가

• TIMESTAMP 타입: DATE보다 정밀한 시간 정보(밀리초 이하, 시간대 등)를 저장할 수 있으며, 연 월 일 뿐만 아니라, 시 분 초 에 대한 EXTRACT 함수 사용이 가능함.

즉 이번 에러에서 경험한 문제는, DATETIME 타입(다른 DBMS에서 사용하는 형식)을 그대로 쓰면 Oracle이 제대로 인식하지 못해서 EXTRACT가 안되고 이걸 CAST 해줘야 하는데, 시를 EXTRACT 할거라면 TIMESTAMP 타입으로 변환해줘야 가능함.

예제

SELECT EXTRACT(HOUR FROM CAST(your_datetime_column AS TIMESTAMP)) AS HOUR
FROM your_table;

또는

SELECT EXTRACT(HOUR FROM TO_TIMESTAMP(your_datetime_column, 'YYYY-MM-DD HH24:MI:SS')) AS HOUR
FROM your_table;

• 정리:
  DATETIME으로 저장된 데이터를 TIMESTAMP로 변환해야 시간 정보의 정밀도와 정확도가 보장됨
  따라서 EXTRACT 함수를 사용하는 경우 대상 칼럼의 TYPE을 잘 확인하여 적절히 변환한 후에 추출할 것!

---

3. 총정리

• 재귀 쿼리는 DBMS에 따라 작성 방식이 달라지므로, 사용하는 DBMS에 맞는 문법을 잘 숙지하자!
• Oracle에서 TIMESTAMP 변환은 DATETIME 타입이 없기 때문에 필수적인 과정임.
  (정확히 말하자면 시간 관련 DATA TYPE은 DATE 와 TIMESTAMP 두가지임)
  CAST나 TO_TIMESTAMP를 활용해서 정확한 시간 정보를 추출할 것!