3. GROUP BY

문제

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

GROUP BY의 4번 문제를 따로 다루게 된 이유는 먼저 내가 풀지 못해서이다. 이제까지 간단한
문법만을 사용해서 문제를 풀 수 있었는데 이 문제에서는 새로운 개념을 다뤄야 했기 때문에 바로 풀지 못했고 여러 글과 풀이를 참고한 후에 이해를 할 수 있었다.

문제 3번인 입양 시각 구하기(1)과 다른 점이라면, 가지고 있는 시간이외 모든 시간대에 대한 결과를 조회해야만 하는 점이다. 처음에 생각했던 방식으로는 0~23시까지 모든 시간대가 존재하는 테이블과 animal_outs 테이블과의 left join을 통해서 문제를 해결할 수 있다고 생각했지만 워낙 mysql에 대한 문법적 지식이 부족해서 구현할 수 없었다.

가장 코드와 과정을 이해하기 쉬운 2가지 풀이 방법을 얘기해보겠다.

WITH RECURSIVE HOUR AS(
SELECT 0 AS h # 3번 정리(비반복문)
UNION ALL # 2번 정리(UNION 사용)
SELECT h+1 FROM HOUR WHERE h<23); # 4,5번 정리( HOUR 참조, where 정지조건)

SELECT h AS HOUR, COALESCE(COUNT(ANIMAL_ID),0) AS COUNT
FROM HOUR LEFT JOIN ANIMAL_OUTS ANI ON HOUR.h = HOUR(ANI.DATETIME)
GROUP BY HOUR.h;

질문하기 게시판에 존재했던 recursive와 left join을 활용한 풀이이다. 위에서 언급한 내가 처음에 생각했던 접근 방식과 굉장히 유사하다는 느낌이 들었다.

먼저 WITH RECURSIVE 는 재귀 쿼리이며 부모코드나 자식코드가 존재하는 계층 구조 보고싶을 때 자주 사용하는 것으로 나온다. 여기서는 0~23이라는 모든 시간대를 만들어주기 위해서 사용된 것으로 보이며 재귀 쿼리에 대한 정리는 다음과 같다.

WITH RECURSIVE 정리

• (1) 메모리 상에 가상의 테이블을 저장
• (2) 반드시 UNION 사용
• (3) 반드시 비반복문도 최소한 1개 요구됨
• (4) 서브쿼리에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요함
• (5) 반복되는 문장은 반드시 정지조건이 요구됨
• (6) 가상의 테이블을 구성하면서 자신을 참조하여 값을 결정할 때 유용함

출처 : https://unrealengine.tistory.com/163

이렇게 알아본 WITH RECURSIVE 문은 while문과 굉장히 유사하다는 느낌을 받았다. 위와 같이 재귀 쿼리를 사용한 다음 HOUR 테이블을 조회하게 되면 0,1,2~23의 1열 23행의 테이블이 조회된다.

다음으로 새롭게 만든 HOUR 테이블과 시간과 입양 건수에 대한 정보가 있는 animal_outs 테이블과 left join을 통해 모든 시간을 살리되 각 시간별 입양건수를 group by를 통해 집계하게 된다. 여기서 사용한 COALESCE() 는 주어진 인수에서 NULL 이 아닌 첫번째 값을 반환하는 함수이다. 즉, 시간대에 아무런 입양이 일어나지 않을 경우 NULL이 반환될 수도 있기 때문에 NULL이 아닌 0을 반환하기 위해 함수를 사용한 것 같다. 하지만 여기서는 COALESCE()를 사용하지 않아도 정답으로 인정되기는 한다.

SET @hour := -1;

SELECT (@hour := @hour + 1) as HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23;

두번째 풀이는 가장 많이 언급되던 로컬 변수를 활용한 풀이이다. SET 변수에 대해서 간단히 설명하고 가자면 다음과 같다. 사용자 정의 변수인 SET 변수는 말그대로 어떤 변수에 특정 값을 할당하는 것으로 python pandas의 query문에서 변수를 사용할 때 @ 를 사용하는 것과 굉장히 유사했다. 사용자 정의 변수의 사용법은 다음과 같다.

사용자 정의 변수 선언 및 초기화

SET @var = 5; 혹은 SET @var := 5;
SELECT @var := 5 FROM ~;

사용자 정의 변수 사용법

SET @start = 15, @finish = 20;
SELECT * FROM employee WHERE id BETWEEN @start AND @finish;

출처 : https://three-pleasure.tistory.com/256

즉, 여기서는 먼저 hour 변수를 -1로 선언하게 된다. 그 다음 hour이 +1 씩 증가하게 되면서 hour 조건에 맞는 서브 쿼리를 참조하여 해당 hour(시간대)의 입양 건수를 구하게 되는 것이다.