2024-04-23 [SQL PROGRAMMER 문제 풀이]

CASE (입양 시각 구하기(2))

[ANIMAL_OUTS] 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

테이블 구조는 다음과 같으며 ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각

동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

Table Structure

문제

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.

0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.

결과는 시간대 순으로 정렬해야 합니다.

 

여기서 제가 짚은 Point는 입양이 발생하지 않은 시간대도 포함되어야 한다는 점이며 다른 분들의 풀이를 보며 반복문을 활용 시간대 별로 그룹을 구성할 수 있는 별도의 테이블을 만드는 것이 필요하다 느껴,

재귀 쿼리(RECURSIVE)를 학습 후 문제 풀이를 진행했습니다.

 

RECURSIVE (재귀 쿼리)

기본 구조

• ‘WITH RECURSIVE’ 쿼리문을 작성하고 내부에 ‘UNION’ 을 통해 재귀를 구성하는 것이 포인트

재귀 쿼리 이해

• 재귀 공통 테이블 식(CTE)이라고도 하는 재귀 쿼리를 사용하면 동일한 테이블 내에서 부모-자식 관계가 있는 계층적 데이터 구조를 쿼리할 수 있습니다. 재귀 쿼리는 원하는 결과를 얻을 때까지 반복적으로 작성되는 임시 결과 집합인 재귀 CTE를 사용합니다.

WITH RECURSIVE CTE_COUNT
AS (
	# NON-RECURSIVE 문장으로 첫번째 루프에서만 실행
    SELECT 1 AS N
    UNION ALL
    
    # RECURSIVE 문장(읽어 올 때마다 행의 위치가 기억되어 다음 번 읽어 올 때 다음 행으로 이동)
    SELECT N + 1 AS NUM
    FROM CTE_COUNT # 바깥의 가상 테이블을 참조
    WHERE N < 3 # 반복문의 정지 조건
)

SELECT *
FROM CTE_COUNT

1. 메모리 상에 가상의 테이블을 저장한다.
2. 반드시 UNION 사용해야 한다.
3. 반드시 비반복문(NON-RECURSIVE)도 최소한 1개 요구된다. (처음 한번만 실행)
4. SubQuery에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요하다.
5. 반복되는 문장은 반드시 정지조건(Termination condition)이 요구된다.
6. 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용하다.

재귀 쿼리는 다음과 같은 구조로 이루어져 있는데, 이해하기 쉽게 추가적인 예시를 들자면

WITH RECURSIVE RC AS (
  SELECT 1 AS H
  UNION ALL
  
  SELECT H + 1 # 재귀
  FROM RC 
  WHERE H < 5 # 재귀 정지 조건
)

SELECT *
FROM RC

 

다음과 같이 재귀 쿼리를 작성 시 아래와 같이 출력하는 것을 확인할 수 있습니다.

풀이 및 해설 (WITH RECURSIVE)

WITH RECURSIVE RC AS (
    SELECT 0 AS HOUR # 0시부터 조회될 수 있도록
    UNION ALL
    
    SELECT HOUR+1 FROM RC 
    WHERE HOUR < 23 # 23시 이전까지 그룹 분류가 필요하기 때문에
)

SELECT *
FROM RC

1. 다음과 같이 재귀 쿼리 활용 [RC] 가상 테이블을 만들어 0시부터 23시까지 그룹이 분류될 수 있도록 코드를 작성해 보았고,

조회 시 아래와 같이 0 ~ 23시까지 출력되는 것을 확인할 수 있습니다.

 

 

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

SELECT RC.HOUR, COUNT(HOUR(A.DATETIME)) AS COUNT
FROM RC
LEFT JOIN ANIMAL_OUTS A ON RC.HOUR = HOUR(A.DATETIME)
GROUP BY RC.HOUR
ORDER BY 1

2. 위 생성한 [RC] 테이블과 [ANIMAL_OUTS] 테이블을 DATETIME 컬럼의 시간 부분을 공통 컬럼으로 활용하여

LEFT JOIN(ANIMAL_OUTS 테이블 내 입양이 없는 시간대의 데이터도 조회하기 위해)하여 시간대 별로 분류를 진행합니다.

 

3. 문제에서 요구하는 컬럼을 조회하고, 시간대를 기준으로 오름차순 정렬 진행하여 문제를 해결할 수 있었습니다.

 

요약 : n이라는 컬럼에 초깃값을 저장하고, 1씩 증가시키는 재귀 쿼리를 작성한 후, LEFT JOIN하여 시간대 별 그룹 분류 이후 조건에 맞는 결과를 출력

 

SQL 반복문을 사용할 수 있다는 점, 재귀 쿼리의 구조에 대해 학습해 볼 수 있는 시간이었다! 정말 쉽게 이해하고 푼 문제 같지만 정말 오랜시간을 붙잡고 이해하려고 노력한 문제였다 ..