<TIL> 2023-12-27

내일배움캠프 본 캠프 7일차

 

이미 SQLD는 합격한 상태이지만 내일배움캠프 수료 조건에 지급된 전 강의 수강이 포함되어 있어서

이론 내용 복습도 하고 SQL도 좀 더 심도있게 배울 겸 지급된 SQLD 강의를 들었다. 

 

이전에 들었던 지급 강의인 엑셀보다 쉽고 빠른 SQL은 MySQL로 코드를 짜서 조금 익숙해졌다. 

그런데 코드테스트를 풀 때 Oracle도 같이 풀다보니 매번 MySQL에서 잘 풀어놓고

Oracle에서는 오류가 나서 한참 고민하는 상황이 반복적으로 일어나고 있다. 

 

SQLD는 Oracle위주로 시험이 구성되어 있으니

SQL 쿼리 파트로 넘어가면 Oracle 쿼리를 짜는데 좀 도움이 되지 않을까 기대하고 있다.

---

• 오늘 학습한 것
  1. 스파르타에서 제공해주는 SQLD 자격증 대비반 강의 2주치 수강
  2. SQL 코드카타
  3. 데이터베이스 관련 아티클 검색

---

아래는 SQL 코드카타를 하다가 배운 내용을 정리해본다. 

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

CTE(Common Table Expression)

위 링크의 입양 시각 구하기(2) 문제를 풀다가 처음보는 문제의 패턴에 난관에 봉착하였다. 

문제에서는 어떤 시간에 가장 입양이 많이 일어나는지를 그룹지어서 COUNT()로 집계하면 되는 언뜻 보면 단순한 문제처럼 보였다. 

 

그런데 문제는 테이블에는 존재하지 않는 시간에 대해서도 0으로 집계표시가 되어야한다는 것이다. 따라서 문제를 풀기 위해서는 필요한 테이블을 일시적으로 설정해야 하는 것이다.

 

CTE를 만드는 방법은 크게 4가지가 있다. 

1. 서브쿼리로 구현하기
2. CREATE TEMPORARY TABLE을 통한 테이블 생성
3. VIEW 구문을 통한 가상의 테이블 생성
4. WITH 절을 이용한 임시 테이블의 생성

그 중 서브쿼리는 코드가 난잡해진다는 단점이 있고, CREATE TEMPORARY TABLE의 경우 코드테스트에서 권한 문제로 생성이 불가할 수도 있기 때문에 주로 WITH절을 이용한 임시테이블을 만든다고 한다.

WITH

WITH TEMP1 AS(
SELECT * 
FROM MY_TABLE)
SELECT * FROM TEMP1

 

WITH절은 위와 같이 WITH로 시작하여 가상 테이블로 만들 테이블 이름을 명명한 뒤 AS 뒤 소괄호에 SQL문을 작성한다 

그리고 메인쿼리에서 가공한 테이블을 불러오는 방식으로 사용할 수 있다.

 

이렇게 알게 된 WITH절을 이용해 문제를 풀려고 하는데 한 가지 해결되지 않은 문제가 남아있었다.

0시부터 23시까지 시간 그룹이 존재하기 때문에 총 24개의 ROW를 가진 시간 칼럼을 가진 테이블을 생성해야하는데 

코드로 이를 짜려고 하니 줄이 길어지는 것이다.

 

그래서 재귀 쿼리를 짜는 방법을 이용하였다.

바로 WITH RECURSIVE이다(MySQL로 문제를 풀었다).

WITH RECURSIVE CTE(HOUR) AS
(
  SELECT 0 #시작 숫자
  UNION ALL
  SELECT HOUR+1 FROM CTE WHERE HOUR < 23 # 종료숫자
)

위 코드를 실행하면 0부터 23까지의 값을 가지는 HOUR컬럼을 가진 CTE 테이블이 생성된다.

 

이렇게 만든 테이블에 기존에 원하는 테이블을 외부 조인하는 것을 통해서 값을 가지지 않는(NULL) 시간대에 대해서도 

출력이 되도록 코드를 작성하였다. 

 

이렇게 푼 정답은 아래 코드와 같다.

WITH RECURSIVE CTE(HOUR) AS
(
  SELECT 0
  UNION ALL
  SELECT HOUR+1 FROM CTE WHERE HOUR < 23
)
SELECT C.HOUR, 
       IFNULL(O.COUNT, 0) AS COUNT
FROM CTE C LEFT OUTER JOIN (SELECT EXTRACT(HOUR FROM DATETIME) AS HOUR,
                                   COUNT(*) COUNT 
                            FROM ANIMAL_OUTS 
                            GROUP BY 1) AS O ON C.HOUR = O.HOUR

---

오라클에서도 똑같이 동작하겠거니 생각해서 코드를 복사 붙여넣기 해서 오라클에서 에러가 나지 않도록 약간 수정하여 실행했는데 아니나 다를까 또 에러가 났다.

 

괄호가 다 닫히지 않았다던가, 그룹바이 절이 틀렸다던가, 구문이 빠졌다던가 이런 오류들을 계속 겪다가

결국 구글링을 시작했다.

 

구글링을 했을 때는 오라클에도 멀쩡히 WITH RECURSIVE 구문이 있는 것으로 나오는데

내가 뭘 잘못해서 오류가 났는지는 공부를 좀 더 해봐야 알 것 같다.

 

CONNECT BY

SELECT LEVEL-1 HOUR 
FROM DUAL 
CONNECT BY LEVEL-1 < 24

 

위에서 MySQL의 WITH RECURSIVE구문과 비슷하게 동작하는 구문을 찾아냈다. 

 

CONNECT BY는 계층형 조회를 위한 쿼리문으로 트리형태의 계층 구조를 위에서 아래로 탐색하거나 

역방향으로 탐색할 때 사용되는 구문이며  CONNECT BY를 이용해서 재귀호출이 가능하다.

 

LEVEL이라는 구문은 계층형 쿼리 조회 시 몇 번째 계층에 있는지를 표시하는 것으로 최상층이 1이다.

 

DUAL은 오라클에 존재하는 기본적인 테이블로 테이블리스트에는 포함되지 않는 SYS소유의 테이블이다.

DAUL테이블에는 DUMMY라는 VARCHAR2(1) 컬럼에  X라는데이터만 담겨있다.

즉 최소한의 자원으로만 구성된 임시 테이블이다.

이 테이블을 이용하면 SELECT으로 원하는 수식의 계산이나 내장함수를 테이블 없이 실행하는 것이 가능하다. 

 

CONNECT BY를 LEVEL-1 즉, 재귀호출하고자 하는 대상에 연결하면 그 연결된 부분이

LEVEL이 1씩 늘어나 2가 되고 이렇게 쭉쭉 원하는 부분까지 계속 LEVEL을 늘려서 24까지 레벨이 내려가면

0 ~ 23의 값을 가지는 테이블을 생성할 수 있다.

 

Oracle 정답 코드는 아래와 같다.

 

SELECT H.HOUR, 
       NVL(O.COUNT, 0) AS COUNT
FROM (SELECT LEVEL-1 HOUR 
      FROM DUAL 
      CONNECT BY LEVEL-1 < 24) H 
      LEFT OUTER JOIN (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, 
                              COUNT(*) AS COUNT
                       FROM ANIMAL_OUTS 
                       GROUP BY TO_CHAR(DATETIME, 'HH24')) O ON H.HOUR = O.HOUR
ORDER BY H.HOUR

 

 

---

• References
  • https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions <MySQL CTE>
  • https://gocoder.tistory.com/1369  <Oracle DUAL>
  • https://heavenlake.tistory.com/45 <Oracle CONNECT BY, LEVEL>