[프로그래머스] GROUP BY절

원본 링크

https://programmers.co.kr/learn/courses/30/parts/17044

개요

해당 챕터에선 SQL문을 응용한 형태로 GROUP BY절이 추가로 들어올 것이다.
GROUP BY는 말 그대로 어떤 기준 하에 묶어낸다는 의미로 파이썬에서 collections.Counter()의 역할을 한다 보면 된다.
따라서 꽤 자주 쓰이는 키워드 중 하나이다.
여기선 GROUP BY를 포함해서 풀어야 하는 다양한 문제들을 볼 것이다.

문제 1, 2 공통 조건

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

문제 1 (고양이와 개는 몇 마리 있을까)

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

예를 들어 ANIMAL_INS 테이블이 다음과 같다면

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A373219	Cat	2014-07-29 11:43:00	Normal	Ella	Spayed Female
A377750	Dog	2017-10-25 17:17:00	Normal	Lucy	Spayed Female
A354540	Cat	2014-12-11 11:48:00	Normal	Tux	Neutered Male

고양이는 2마리, 개는 1마리 들어왔습니다. 따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

ANIMAL_TYPE	count
Cat	2
Dog	1

나만의 풀이 1

우선 문제 중간에 밑줄 쳐진 부분이 있을 것이다.
왜 쳤나면 저거 안보고 제출했다가 한 번 틀렸기 때문이다....
문제 조건대로 출력되도록 구문을 짜주자. (제발)

먼저, 총 2개의 열이 필요한데, 하나는 ANIMAL_TYPE이고, 다른 하나는 count이다.
count를 보면 ANIMAL_TYPE의 개수임을 알 수 있다.
따라서 COUNT(ANIMAL_TYPE) AS 'count'의 형태로 먼저 열을 지정한다.

그리고 뽑아내야 할 Data는 ANIMAL_TYPE이 Cat 혹은 Dog인 경우이므로, WHERE절로 다음의 구문을 넣으면 된다.
WHERE ANIMAL_TYPE = 'Cat' OR ANIMAL_TYPE = 'Dog'
다른 방법도 있긴 하나, 이게 직관적이라 이렇게 썼다.
(+a, WHERE ANIMAL_TYPE IN ('Cat', 'Dog')도 가능!)

앞서 COUNT()라는 메소드를 썼기 때문에, ANIMAL_TYPE을 기준으로 묶겠다는 의미의 구문을 써준다.
즉, GROUP BY ANIMAL_TYPE을 그 뒤에 적는다.

마지막으로 ORDER BY를 적으면 끝난다.
최종 형태는 아래와 같다.

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS 'count' FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Cat' OR ANIMAL_TYPE = 'Dog'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

문제 2 (동명 동물 수 찾기)

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

예를 들어 ANIMAL_INS 테이블이 다음과 같다면

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A396810	Dog	2016-08-22 16:13:00	Injured	Raven	Spayed Female
A377750	Dog	2017-10-25 17:17:00	Normal	Lucy	Spayed Female
A355688	Dog	2014-01-26 13:48:00	Normal	Shadow	Neutered Male
A399421	Dog	2015-08-25 14:08:00	Normal	Lucy	Spayed Female
A400680	Dog	2017-06-17 13:29:00	Normal	Lucy	Spayed Female
A410668	Cat	2015-11-19 13:41:00	Normal	Raven	Spayed Female

• Raven 이름은 2번 쓰였습니다.
• Lucy 이름은 3번 쓰였습니다
• Shadow 이름은 1번 쓰였습니다.

따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

NAME	COUNT
Lucy	3
Raven	2

나만의 풀이 2

우선, SELECT에서 NAME이라는 속성을 선택했기에, NAME이 NULL인 경우는 자동으로 배제된다.
COUNT로 동일 NAME을 묶어내는 것은 앞선 문제와 유사하다.
다만, 이번 문제에선 COUNT(NAME)이 1보다 큰 경우를 찾아야 한다.

따라서 GROUP BY 뒤에 따라붙는 HAVING절을 추가로 알아야 한다.
HAVING은 GROUP BY로 묶어낸 속성을 비교하기 위해 사용한다.
그냥 WHERE절에다가 쓰면 문법 오류가 난다.
아무튼, 이 문제에선 HAVING COUNT(NAME) > 1의 형태로 뒤에 구문을 추가해주면 된다.

마지막으로 NAME순으로 정렬하면 된다.
아래 코드가 전체 결과물이다.

SELECT NAME, COUNT(NAME) AS 'COUNT' FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1  
ORDER BY NAME

문제 3, 4 공통 조건

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

문제 3 (입양 시각 구하기(1))

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

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR	COUNT
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2

나만의 풀이 3

이번엔 테이블이 다른 테이블로 바뀌었다.
ANIMAL_OUTS를 사용하는걸 유의해서 문제를 풀면 된다.

DATE자료형에서 시간을 추출하려면 HOUR(컬럼명)의 형태로 쓰면 된다.
또한 이건 GROUP BY랑 무관한 단순 값 치환 메소드이다.
따라서 WHERE절로 비교하면 되니 앞선 문제랑 착각하지 말자.

SELECT HOUR(DATETIME) AS 'HOUR', COUNT(DATETIME) AS 'COUNT' FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

크게 설명할 내용은 없고 위 코드로도 이해할 수 있다고 본다.
참고로, 중간의 WHERE절을 WHERE HOUR(DATETIME) BETWEEN 9 AND 19로 바꿔도 된다.
WHERE 속성 BETWEEN 값A AND 값B의 구문을 넣게 되면, 값A <= 속성 <= 값B인 경우만 추출하게 된다.

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

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

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR	COUNT
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	3
8	1
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2
20	0
21	0
22	0
23	0

나만의 풀이 4

문제 자체만 따지면 앞선 문제 3과 거의 비슷한 수준이라 느껴질 것이다.
그런데 놀라운 사실이 있다.
무려 이 친구는 프로그래머스에서 Level 4에 임박하는 무시무시한 문제라는 것을....
왜 그렇게 분류됐는지 이유를 파악해보자.

아까 잠깐 설명하고 넘어갔던 부분이 있는데, NULL값의 경우 속성이 무시된다고 적어놨던 부분이 있다.
그런데 현재 사용하는 데이터엔 시간대가 7시 ~ 19시인 경우만 있고 그 외의 경우는 없다.
즉, 단순한 HOUR()와 GROUP BY만으로는 0이라는 값을 출력할 수가 없다. 야 이거 큰일났네

나도 이 문제는 약간의 구글링을 통해서 해결방법을 찾아낸 친구이다.
나름대로 SQL을 좀 배우긴 했지만 기존에 몰랐던 기능을 써야 이 문제를 풀 수 있었다.
크게 2가지가 있는데, 각각 풀어서 설명하겠다.

방법 1. 변수 선언

MySQL에서 변수를 생성하기 위해선 SET @변수명 = 대입할 값;이라는 구문을 쓰게 된다.
혹은 = 연산자 대신 :=를 쓸 수도 있다.
그리고, SELECT문에서 대입 연산을 할 경우, :=로 무조건 통일해서 써야 한다. (=가 비교연산자로 인식된다.)

먼저, 원하는 동작을 구현하기 위해 @H라는 변수를 -1의 값으로 선언하자.
이후, 다음의 형태로 코드를 짜면 되는데, 쉽게 말하면 for문을 돌면서 @H값을 증가시켜나가고, 그에 대응되는 COUNT()값을 저장하면 되는 식이다.

SELECT (@H := @H+1) AS 'HOUR', (
    SELECT COUNT(HOUR(DATETIME))
    FROM ANIMAL_OUTS
    WHERE @H = HOUR(DATETIME)
) AS 'COUNT'

위 코드를 보면 알겠지만, 'COUNT'에 해당하는 속성이 꽤나 길 것이다.
먼저 내부의 SELECT문에선 'COUNT'에 해당되는 속성을 추출하되, 시간이 @H값과 같은 경우만 세도록 코드를 짰다.
이후 외부의 SELECT문에선 (@H := @H+1)의 형태로 각 row마다 인덱싱되도록 코드를 짜면 된다.
그리고, 맨 마지막에 WHERE @H < 23을 넣어서 HOUR가 23인 경우 이후엔 terminate 되도록 코드를 짜자.

최종 구조는 아래와 같다.

SET @H = -1;  # 변수 선언
SELECT (@H := @H+1) AS 'HOUR', (  # 매 col마다 동작 수행
    SELECT COUNT(HOUR(DATETIME))
    FROM ANIMAL_OUTS
    WHERE @H = HOUR(DATETIME)
) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE @H < 23

방법 2. 가상 table 생성

여기에선 총 2단계를 거쳐서 코드를 짜게 된다.
하나는 가상의 table을 생성하는 과정이며, 다른 하나는 그 table과 JOIN하는 과정이다.
JOIN연산은 향후 상세히 다룰 예정이라 여기선 간단히 소개만 하고 넘어가려 한다.

WITH RECURSIVE myCte(h) AS (  # 가상의 table 생성
    SELECT 0
    UNION ALL  # 여러개의 select문을 합치는 효과
    SELECT h+1 FROM myCte WHERE h<23
)

먼저 위 코드는 table을 생성하는 부분이다.
재귀의 형태로 Query가 수행된다고 보면 되겠다.
WITH RECURSIVE 이름명(col 이름, ...) AS ( 내용 )꼴로 작성된다.
그냥 이름명 AS ..의 형태로 써도 되긴 하다.
여기선 시간 값과 관련있으므로 h라고 지정했다.

SELECT myCte.h AS 'HOUR', COUNT(AO.ANIMAL_ID) AS 'COUNT' FROM myCte
LEFT JOIN ANIMAL_OUTS AS AO
ON myCte.h = HOUR(AO.DATETIME)
GROUP BY h ORDER BY h

이후 다음과 같이 ANIMAL_OUTS와 myCte를 조인하는 형태로 코드를 짜면 된다.
myCte를 기준으로 LEFT JOIN했으며, 따라서 NULL값이 있어도 누락되지 않고 COUNT() = 0의 형태로 처리가능하다.
참고로 편의상 ANIMAL_OUTS 테이블을 AO라 재정의했다.

GROUP BY를 활용한 대표적인 문제들을 만나 보았다.
4번 문제가 꽤 복잡하고 까다로웠지만, 그 외에는 그럭저럭 풀 만한 문제였다고 본다.
여기선 안 나왔지만, GROUP BY는 다른 키워드와 함께 써서, 가령 학년별 성적 평균, 성별에 따른 키의 분산 등을 구할 때도 사용할 수 있다.
따라서 GROUP BY 정도는 알고 넘어갈 수 있도록 하자.

물론 저기 변수 선언하고 CTE 생성하고 하는 부분은 조금 넘어가도 될 것 같긴 하다만...