[프로그래머스 코딩 테스트 연습 SQL] #2. RECURSIVE WITH 문 ( Recursive CTE)

* SQL 아마추어가 작성한 내용이라 오류가 있을 수 있습니다. 이외에도 오타, 저작권 문제 등 문제가 있을 시 댓글로 알려주세요.

 바로 수정하겠습니다. 

* 유/무료 서비스 이름이 등장합니다만, 광고 또는 홍보 목적이 아니며 금전적 지원을 받은 사실이 없음을 밝힙니다.

* 깊은 개념적 이해보다 문제의 예시 답안 코드를 이해하기 위한 수준의 내용을 다루는 점 유의 바랍니다. 

* 본 포스팅 작성 시 참고한 소스는 페이지 가장 하단의 References 에 하이퍼링크를 이용하여 명시했습니다. 

 

 

이번에는 프로그래머스 코딩테스트 SQL 연습 문제 중 < 입양 시각 구하기 (2) > 라는 문제를 풀어보려고 한다. 일단 바로 문제로 go! 하자.

---

두 번째 정리 RECURSIVE WITH Clause (RECURSIVE CTE)

문제: 코딩테스트 연습 - 모든 문제 -  MySQL 필터링 적용 - 입양 시각 구하기 (2)

본문 링크: programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(2)

문제를 요약하자면, 아래와 같이 보호소에서 입양을 보낸 동물의 정보가 담긴 ANIMAL_OUTS 라는 테이블이 있을 때 해당 테이블에서 몇 시에 입양이 가장 활발히 일어나는지 조회할 수 있는 쿼리를 짜는 것이다. 

 

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

* ANIMAL_ID : 동물의 아이디

* ANIMAL_TYPE : 생물 종

* DATETIME : 입양일

* NAME : 이름

* SEX_UPON_OUTCOME : 입양 당시 성별 및 중성화 여부

 

이 때,  쿼리 실행 화면은 우측 스크린 캡처와 같이 0시 ~ 23시까지 각 시간대 별 입양 발생 횟수를 모두 보여주어야 한다. 

 

출처: 본문 링크 캡처

 

얼핏 보았을 때는 DATETIME 컬럼에 HOUR() 함수를 적용하고, ANIMAL_OUTS 테이블 내에 각 시간대가 몇 번 존재하는지 세어주면 (COUNT()) 되는 것이 아닌가 했는데, 그게 아니었다. 

테이블에 기록된 입양 발생 시간은 7시 ~ 19시까지 밖에 없었다. (어떻게 보면 당연한 것이, 보통 유기 동물 보호소가 24시간 운영되지는 않을 것 같기 때문이다.) 그러니까 테이블 상에 아예 존재하지 않는 0시 ~ 6시와 20시 ~ 23시까지의 값을 화면에 띄워주는 쿼리를 짜는 것이 문제의 핵심이다. 

 

---

문제를 풀기 위해 계속 생각했으나 내가 배운 것들을 활용하여 풀 수 있는 문제가 아닌 것 같았고, 결국 다른 분들이 공유해 주신 코드를 보기로 했다...그 중 변근호님이 공유해주신 코드를 참고하여 아래와 같이 쿼리를 짰고, 통과는 했다. 

 

 

WITH RECURSIVE HOUR_ROWS AS (
    SELECT 0 AS NUM
    UNION
    SELECT NUM + 1 FROM HOUR_ROWS 
    WHERE NUM < 23
)
SELECT HOUR_ROWS.NUM HOUR, COUNT(HOUR(ANI_OUTS.DATETIME)) COUNT
FROM HOUR_ROWS
LEFT JOIN ANIMAL_OUTS AS ANI_OUTS ON HOUR_ROWS.NUM = HOUR(ANI_OUTS.DATETIME)
GROUP BY HOUR_ROWS.NUM;

 

 

일단 통과는 했지만, WITH RECURSIVE 구문은 내게 생소한 개념이었기에 간단하게라도 짚고 넘어가려고 한다. (바로 이것이 본 포스팅을 쓴 이유이다. 서론이 아주 길었다) 

 

---

1. CTE (Common Table Expression) 이란 무엇인가?

 

위 문제처럼 데이터 테이블 상에는 존재하지 않지만, 결과 조회를 위해 필요한 경우 또는 해당 결과가 다른 쿼리를 짤 때 자주 쓰일 것으로 예상되는 경우에 활용이 가능하도록 만든 임시 테이블 (temporary result sets) 이라고 할 수 있다. 따라서 재사용성이 있으며 쿼리 문의 가독성을 높이는 장점이 있다. 

CTE 를 정의하는 방법은 WITH 문을 이용하는 것이다. 형태는 아래와 같다.

 

 

WITH TABLENAME AS (
....
)

 

 

Q. 잠깐, 왜 가독성이 높을까? 🧐

A. 위 프로그래머스 문제에 대한 코드처럼, WITH RECURSIVE 절을 써서 미리 활용하고자 하는 임시 테이블을 정의해 놓으면 그 다음 단계의 쿼리가 매우 단순해진다. 그래서 가독성이 높다고 하는 것이다.

 

Q. 그러면 CTE 를 원하는 때에 원하는 만큼 사용해도 될까? 🧐

A. 그건 아니다. MySQL 8 과 PostgreSQL 에서는 CTE 실행 결과로 생성된 테이블을 cache 처럼 임시 저장한다고 한다. (맨 아래 References 3번 링크 참조) 따라서 이로 인해 오히려 쿼리 속도가 저하될 수도 있다고 한다. 추가 내용은 데이터베이스/데이터 엔지니어링 쪽으로 깊이 들어가는 이야기 같아서 본 포스팅에서는 생략한다. 

 

 

CTE 는 Non-recursive 타입과 Recursive 타입 두 종류로 나뉜다. Non-recursive 타입은 다른 연습 문제를 풀 때 써본 적이 있지만, Recursive 타입은 이번에 처음 보았기 때문에 이제부터는 Recursive 타입만을 다룰 것이다. 

 

 

2. 그럼 Recursive CTE 는 무엇인가?

Recursive CTE, 또는 재귀쿼리는 자기 자신을 참조하여 결과를 생성할 수 있다. 정확히 말하면, Recursive CTE 가 실행될 때 반복적으로 Recursive CTE 자신이 실행하는 결과를 호출하게 된다. 계층적 구조를 가진 데이터를 다룰 때 활용하기 좋다. 

 

Q. 반복적으로 자기 자신을 호출하는 쿼리라고? 그럼 무한대로 돌아가는 것 아닌가? 🧐

아니다. 특정 조건 (end condition) 을 만족하면 실행이 멈추기 때문에 쿼리가 무한대로 돌아가지 않는다! 일반적으로 Recursive CTE 를 작성하는 문법을 보면

 

 

WITH TABLENAME (COLUM1, COLUMN2, …) AS
(
   쿼리1 # 앵커 멤버 (Anchor member), 자기 자신을 참조하지 않음.
   UNION ALL # 중복 값을 삭제하여 결과를 반환하고 싶다면 UINON ALL 대신 UNION 사용!
   쿼리2 # 재귀 멤버 (Recursive member), 재퀴 쿼리 실행 결과를 참조
)
SELECT *
FROM TABLENAME # 재귀쿼리의 결과를 활용

 

 

위와 같은데, Recursive CTE 가 실행되면 앵커 멤버 쿼리가 가장 먼저 돌아가고 이때 파생된 열들은 UNION ALL 연산자를 통해 재귀 멤버 쿼리와 합쳐지게 되는 구조임을 알 수 있다. (이전 호출에서 반환할 결과가 없다면 Recursive CTE 는 종료된다.)

 

이제 다시 위의 프로그래머스 답안을 가져와서 이해한 바를 적용해 설명하면 다음과 같다. 

 

 

WITH RECURSIVE HOUR_ROWS AS (
    SELECT 0 AS NUM # 앵커 멤버 정의, 0 을 NUM 이란 이름으로 반환
    UNION
    SELECT NUM + 1 FROM HOUR_ROWS # 재귀 멤버 정의, 이전에 호출된 NUM 에 1을 더해줘!
    WHERE NUM < 23 # 종료 조건 (terminator): NUM 이 23이 되기 전에 재귀를 종료해!
)
# 결과는 0 ~ 23까지의 값이 나열된 컬럼 1줄 (컬럼명은 NUM)

 

 

---

 

자료를 찾고 이해하고 글을 작성하는 데에 걸린 시간에 비해 CTE 와 Recursive CTE 를 다룬 부분은 그리 길지 않아 보이지만 😢 포스팅을 쓰기 전 계획한 범위만큼은 다 포함했기에 이만 키보드에서 손을 떼 보려고 한다.

마지막으로 변근호님을 비롯하여 프로그래머스에 코드 공유해 주신 많은 분들, 그리고 해당 포스팅에서 언급된 CTE 개념에 대한 글을 작성하신 분들께 감사를 표하며 오늘의 SQL 문제 정리 포스팅을 마친다.

 

 

References: 2020.09.25 기준 

1. Introduction to Common Table Expressions

Common Table Expressions (Introduction to CTE's) - Essential SQL

2. Recursive CTE's Explained

3. SQL 로 CTE 를 표현하는 WITH 활용하기

4. 공동 테이블 식과 재귀 쿼리

5. MS SQL 재퀴쿼리 트리구조 쿼리 WITH CTE 

6. 재귀적 CTE