[프로그래머스 SQL] GROUP BY 문제 풀어보기

0. 기본 테이블 설명

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

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

1. 몇 마리인지 count -> SELECT ANIMAL_TYPE, count(ANIMAL_ID) count 
2. 여러 동물 중 고양이와 개를 필터링 -> WHERE ANIMAL_TYPE IN ('CAT','DOG')
3. 고양이인지 개인지에 따라서 나누어서 셈-> GROUP BY ANIMAL_TYPE
4. 고양이(Cat)를 개(Dog)보다 먼저 조회 -> ORDER BY ANIMAL_TYPE

정답

SELECT ANIMAL_TYPE, count(ANIMAL_ID) count 
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('CAT','DOG')
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

조회 결과

2. 동명 동물 수 찾기

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

1. 이름과 그 이름이 쓰인 횟수 조회 -> SELECT NAME, COUNT(ANIMAL_ID)
2. 이름이 없는 동물은 제외 -> WHERE NAME IS NOT NULL 
3. 이름이 쓰인 횟수를 이름에 따라 집계 -> GROUP BY NAME
4. 두 번 이상 쓰인 이름 조건 -> HAVING COUNT(ANIMAL_ID) > 1
5. 결과를 이름 순으로 조회 -> ORDER BY NAME

정답

SELECT NAME, COUNT(ANIMAL_ID) count -- count로 쓴 alias는 안해도 무방
FROM ANIMAL_INS
WHERE NAME IS NOT NULL 
GROUP BY NAME
HAVING COUNT(ANIMAL_ID) > 1
ORDER BY NAME

조회 결과

3. 입양 시각 구하기(1)

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

참고) 이전까지는 MySQL로 풀었으나 이 이후부터는 Oracle로 품

1. 시간대별로 발생한 입양 건수 -> EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) hour, COUNT(animal_id) count 
2. 시간대는 오전 9시부터 오후 8시 전까지 -> EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) BETWEEN 9 AND 20
3. 시간대 별로 집계할 것 -> GROUP BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) 
4. 결과를 시간대 순으로 정렬 -> ORDER BY HOUR

정답

-- Oracle
SELECT EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) hour, COUNT(ANIMAL_ID) count 
FROM ANIMAL_OUTS 
WHERE EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) BETWEEN 9 AND 20
GROUP BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) 
ORDER BY HOUR

-- 서브쿼리를 이용해서 조금 더 깔끔하게
SELECT * 
FROM( 
    SELECT EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) hour, COUNT(animal_id) count 
    FROM ANIMAL_OUTS 
    GROUP BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)))
WHERE hour BETWEEN 9 AND 20
ORDER BY hour

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

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

이 문제는 헷갈릴 것 같아서 더 설명을 붙이자면,
1. 문제가 요구하는대로 0시부터 23시까지 가진 시간 테이블(밑에서는 A) 생성
2. 그리고 시간별로 입양 건수를 카운트한 테이블 (밑에서는 B) 생성
3. 이 두개를 A 를 원본으로 두고, 시간대 컬럼을 기준으로해서 left outer join 하면 입양이 일어난 시간대엔 입양 수가 붙게 되고, 입양이 안 이루어진 시간대에는 null 이 들어가게 됨
4. 입양이 일어나지 않은 시간대의 입양건수는 nvl함수를 이용해서 null이 아니라 0으로 표현
5. 결과를 시간대 순서대로 정렬해야 하므로 order by를 이용해서 시간대 순서대로 정렬

정답

-- Oracle
SELECT A.HOUR, NVL(B.COUNT,0) COUNT
FROM 
    (SELECT level-1 as Hour
    from dual 
    connect by level <= 24) A
LEFT OUTER JOIN (SELECT TO_CHAR(DATETIME,'HH24') HOUR, COUNT(ANIMAL_ID) COUNT 
		FROM ANIMAL_OUTS 
                GROUP BY TO_CHAR(DATETIME, 'HH24')
                ) B
ON A.HOUR = B.HOUR
ORDER BY A.HOUR

실행 결과