[programmers] GROUP BY 문제 풀기

 

- 고양이와 개는 몇 마리 있을까

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS 'count'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

ANIMAL_TYPE 기준으로 그룹화 -> dog, cat

 

 

- 동명 동물 수 찾기

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

SELECT NAME, COUNT(NAME) AS 'COUNT'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME;

* 'WHERE NAME IS NOT NULL'은 꼭 넣지 않아도 'COUNT(NAME)'를 수행하는 과정에서 자동으로 NULL 값이 포함된 레코드는 제외된다 * (count(컬럼명) : null 값을 제외하고 컬럼의 값을 추출)

 

 

- 입양 시각 구하기 (1)

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR(DATETIME);

이전 문제와는 달리 별도의 별칭을 쓰지 않아도 상관 없다는 얘기가 없어서 별칭을 제대로 썼다면 다음의 내용을 알지 못했을 것이다.

 

HAVING의 비밀

처음엔 별칭을 쓰지 않고 HOUR(DATETIME)을 HAVING 절에 넣어서 작동시켰을 때,

Unknown column ‘datetime’ in ‘having clause’ 오류가 나타났었다.

 

구글링 해보니 HOUR(DATETIME)은 DATETIME에서 시간을 추출하는 즉, COMMAND가 가미된 컬럼이기 때문에 해당 컬럼명을 알 수 없다고 에러메시지가 출력된 것이다. 

따라서 HAVING 절에서 무조건 컬럼의 별칭을 사용하면 이 오류를 해결할 수 있다. (다른 절에서는 크게 상관 없는듯)

 

또한, HAVING 절에서는 이상, 이하 등을 나타내는 부등호 대신 'BETWEEN 값1 AND 값2' 사용하자

 

 

- 입양 시각 구하기 (2)

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

사진은 잘렸지만 HOUR 컬럼의 값이 23까지 있는 상황

여기서 가장 난이도가 높은 문제!

단순히 문제를 딱 보고 이전 문제랑 크게 다를게 없다고 생각했는데, 0, 1, 2, ~ 22, 23 까지의 새로운 숫자가 있고 카운트도 0이라 left join 문제라고 감이 왔다. 그렇지만 어떻게 풀어나가야 할지 막막했었다. (어떤 테이블과 조인을 해야하는지?)

# HOUR(0~23)컬럼이 있는 TEMP 테이블 생성
WITH RECURSIVE TEMP AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR+1 FROM TEMP 
WHERE HOUR < 23)

SELECT HOUR, COUNT(DATETIME) 'COUNT'
FROM TEMP
    LEFT OUTER JOIN ANIMAL_OUTS
    ON TEMP.HOUR = HOUR(DATETIME)
GROUP BY HOUR
ORDER BY HOUR;

[MySQL] 계층 쿼리 - WITH, WITH RECURSIVE 사용법

 

join을 하기 전 ANIMAL_OUTS 테이블의 HOUR(DATETIME) 컬럼에는 없는 0, 1, 22, 23 등의 값이 결과 테이블에 나타나야 하므로, 이 값들이 담긴 가상의 테이블을 만들어야 한다. 이를 위해 *WITH RECURSIVE 구문을 사용하면 된다.

* WITH RECURSIVE 구문 : 가상의 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용

 

이를 바탕으로 작성한 쿼리를 보면, TEMP 라는 이름의 가상 테이블을 생성하고, 초기값 0을 HOUR이라는 별칭으로 설정했다. 

UNION ALL으로 쿼리를 이어붙여주고, HOUR이 23이 될 때까지 1씩 더해준다. (근데 < 인데 왜 23도 포함이 되는지는 잘 모르겠다...)

이후 생성된 가상 테이블과 ANIMAL_OUTS 테이블을 left outer join (공통적인 부분 + left에 있는 것만) 하면 된다.