프로그래머스의 SQL 코테를 풀다가 다음과 같은 문제가 나왔다. 먼저 데이터 'ANIMAL_OUTS'를 보면,
SELECT *
FROM ANIMAL_OUTS
LIMIT 10;
이러한 데이터에서, 시간대별 입양 건수를 조회하라는 문제였다.
즉, DATETIME 열의 HOUR 값에 따라 ANIMAL_ID를 카운트하면 되는데, 이게 그냥 GROUP BY를 사용하면 문제가 있었다.
SELECT HOUR(DATETIME), COUNT(ANIMAL_ID)
FROM ANIMAL_OUTS
GROUP BY 1
ORDER BY 1
위와 같이 시간대별 입양 건수가 있는 결과만 뽑혀나오는 문제가 생겼다.
하지만 이 문제의 경우 왼쪽 사진과 같이 COUNT값이 0이어도 값이 0으로 나와야 했는데, 그냥 집계함수를 사용하면 0의 경우는 나오지 않았다.
그래서 사용한 것이 MySQL의 RECURSIVE 구문을 활용해 HOUR 값의 배열(0~23까지의 값)을 만들고,
이 값을 기준으로 HOUR별 COUNT 값을 LEFT JOIN시키는 방법을 사용했다.
먼저 HOUR 값을 0부터 23까지 만드는 코드는 다음과 같다.
WITH RECURSIVE num(HOUR) AS(
SELECT 0
UNION ALL
SELECT HOUR+1 FROM num WHERE HOUR<23)
SELECT *
FROM num
이렇게 HOUR 열의 값 0부터 23까지의 배열이 만들어졌다.
이것을 빅쿼리에서 한다면 GENERATE_ARRAY와 UNNEST 구문을 사용해 만들어주면 된다.
-- 빅쿼리용 코드
WITH hour_array AS(
SELECT GENERATE_ARRAY(0,23,1) as hour
)
SELECT
hour
FROM hour_array, UNNEST(hour) as hour
하지만 이 테스트는 MySQL을 기반으로 하는 것이라 RECURSIVE를 이용했다.
그럼 이제 만들어진 HOUR 열에, 입양 건수를 카운트한 값을 LEFT JOIN시켜보자.
WITH RECURSIVE num(HOUR) AS(
SELECT 0
UNION ALL
SELECT HOUR+1 FROM num WHERE HOUR<23)
SELECT A.HOUR, B.COUNT
FROM num A
LEFT JOIN (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY 1) B ON A.HOUR = B.H
ORDER BY 1
그런데 이렇게 만든 경우엔 카운트 값이 없는 경우는 NULL로 나오는 문제가 있었다.
그래서 서브쿼리와 CASE WHEN을 사용해, NULL 값이면 0으로 채워주었다.
WITH RECURSIVE num(HOUR) AS(
SELECT 0
UNION ALL
SELECT HOUR+1 FROM num WHERE HOUR<23)
SELECT sub.HOUR,
CASE WHEN sub.COUNT IS NOT NULL THEN sub.COUNT
ELSE 0 END AS COUNT
FROM (SELECT A.HOUR, B.COUNT
FROM num A
LEFT JOIN (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY 1) B ON A.HOUR = B.H) sub
ORDER BY 1
완성!
문제 출처: 프로그래머스 SQL 고득점 Kit - GROUP BY 입양 시각 구하기(2)
'Skillset > SQL' 카테고리의 다른 글
| SQL 특정 문자들로 시작 or 끝나는 도시 출력하기 (0) | 2022.08.08 |
|---|---|
| [MySQL] 가장 짧은 글자와 가장 긴 글자에 해당하는 도시 구하기 (LENGTH) (0) | 2022.08.07 |
| SQL N번째 순위 추출하기(2번째로 큰 값, 상위 N개, 하위 N개 구하기) (1) | 2022.06.14 |
| [MS-SQL] 항목별 비율값 집계하기 (CAST, ROUND, CASE WHEN 구문) (0) | 2022.06.13 |
| SQL CASE WHEN 구문 사용법 - 예제로 알아보기 (0) | 2022.06.07 |
