프로그래머스 문제를 풀다가.. 띠용 했다.
https://programmers.co.kr/learn/courses/30/lessons/59413
코딩테스트 연습 - 입양 시각 구하기(2)
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물
programmers.co.kr
이 문제였는데, 주어진 테이블로 SELECT 연산을 하면 0~23시 데이터 중 빈 데이터가 있어 모든 시간마다 column이 생성되지 않아 정답이 틀렸다고 나왔다.
여기서 혼란이 왔다.
아니 애초에 테이블에 정보가 없는 데이터는 SELECT 으로 나올 수가 없고
그 정보를 가진 테이블이랑 JOIN을 해야 할 텐데 어떻게 만들지?
질문하기 눌러서 탐색해보다가 갓-답변을 발견했다.
깔끔쓰한 해결책은 바로 WITH RECURSIVE 구문 사용하기!
WITH RECURSIVE
mysql에서 제공하는 WITH RECURSIVE 구문은 말 그대로 재귀함수처럼 동작하는 친구이다.
구성은 다음과 같다.
WITH RECURSIVE 생성할_뷰_이름 AS
(
초기_SQL
UNION ALL
반복할_SQL_반복을_멈출_WHERE절_포함
)
이게 뭐람? 나도 그랬다.
근데 공식 홈페이지에 나온 예시를 보면 대충 눈치를 챌 수 있다.
예제를 살펴보자.
일단 recursive 구문을 통해 생성할 뷰 이름은 cte 이고, cte에는 n이라는 column이 존재한다.
그리고 SELECT 1 구문을 통해 딱 1이 담긴 initial row set 하나를 생성한다.
여기까지 하면 cte 테이블의 형태는
n |
1 |
과 같을 것이다.
이제 아래의 반복문을 실행하면서 나온 결과를 모두 UNION ALL 하게 되는데,
이 반복문의 뜻은 n이 가지고 있는 직전 row set 값이 5보다 작을 때, n+1인 row set을 하나 만든다는 것이다.
고로 initial row set이 위의 표와 같을 때,
첫번째 반복문에서는 n = 1이므로 반복문을 통해 아래의 row set이 생성될 것이다.
n |
2 |
그럼 얘랑 아까 걔랑 UNION ALL 하면
n |
1 |
2 |
이 되는 것이다!
이걸 직전 row set 값이 4일때까지 반복하므로, 결과적으로 1~5값이 담긴 cte 뷰가 생성된다.
이제 얘를 이용해서 HOUR가 0~24인 뷰를 만들고, 걔랑 주어진 데이터를 JOIN 하면 된다.
풀이
WITH RECURSIVE cte (HOUR) AS
(
SELECT 0
UNION ALL
SELECT HOUR + 1 FROM cte WHERE HOUR < 23
)
SELECT cte.HOUR, COUNT(ANIMAL_OUTS.ANIMAL_ID) AS 'COUNT'
FROM cte
LEFT JOIN ANIMAL_OUTS
ON cte.HOUR = HOUR(ANIMAL_OUTS.DATETIME)
GROUP BY HOUR
먼저 WITH RECURSIVE 구문으로 HOUR column에 0~23 값을 가진 cte 뷰를 생성한다.
이제 이렇게 만들어진 cte 와 ANIMAL_OUTS를 LEFT JOIN 하게 되는데, cte.HOUR와 ANIMAL_OUTS의 HOUR(AHIMAL_OUTS.DATETIME)이 같은 녀석들을 기준으로 LEFT JOIN 한다.
HOUR()함수는 ANIMAL_OUTS의 DATETIME 형식에서 HOUR 데이터만 쏙 뽑아올 수 있는 함수이다.
LEFT JOIN을 하는 이유는 오른쪽 테이블에 맞는 컬럼이 없어도 왼쪽 테이블을 기준으로 무조건 JOIN 되기 때문에,
만약 HOUR(ANIMAL_OUTS.DATETIME)에 18시가 없다 한들 cte에는 있으므로 cte.HOUR 18에 해당하는 COUNT(ANIMAL_OUTS.ANIMAL_ID) 값은 0으로 row가 생성될 것이다.
그렇다! 그렇게 하면 된다
아 그리고 보통 이런거 GROUP BY 개수 세라고 하면 COUNT(*) 많이 하는데, 여기서 COUNT(*) 하면 틀린다.
왜냐하면, HOUR(ANIMAL_OUTS.DATETIME)에 18시가 없다 하더라도 cte.HOUR에는 18이 있으므로
LEFT JOIN한 테이블에는 ANIMAL_OUTS값은 NULL로 채워진 cte.HOUR 18 row가 있을 것이다.
따라서, COUNT(*)을 하게 되면 row 개수를 세는 것이기 때문에 입양데이터가 없어도 cte 뷰 때문에 생성된 row가 count 되어 COUNT(*)값은 무조건 1 이상이 된다.
그러나, 우리는 입양 건수가 있는 것만 세어야 하므로 여기서는 COUNT(ANIMAL_OUTS.ANIMAL_ID)를 세도록 하였다.
'잡다한 시도 > SQL 특별 관리 구역' 카테고리의 다른 글
[SELECT] 중복과 NULL을 제거한 개수 세기 (COUNT, NULL, DISTINCT) (0) | 2021.04.21 |
---|