프로그래머스 코딩테스트 연습 SQL - 입양 시각 구하기(2) 풀이

코딩테스트 연습 - 입양 시각 구하기(2)

문제 설명

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

NAMETYPENULLABLE

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

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 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

 

풀이

정답 출처:

MySQL 제가 푼 답안입니다. (set 사용)

set @a := -1; 
SELECT A.HOUR, IFNULL(B.count, 0) FROM 
(SELECT @a:=@a+1 as HOUR FROM ANIMAL_OUTS) A LEFT JOIN 
(SELECT HOUR(datetime) AS hour, COUNT(*) AS count FROM animal_outs GROUP BY HOUR(datetime)) B 
ON A.HOUR = B.hour WHERE A.HOUR BETWEEN 0 and 23;

이 문제의 포인트는 animal_outs 테이블의 datetime에는 7~19시 사이의 데이터만 존재하는데, 0~23시 사이를 모두 출력 결과에 포함해야 하는 점이다. 접근 방법은, 0부터 23의 숫자를 칼럼의 값으로 가지는 테이블을 하나 만들고, animal_outs 테이블에서 각 시간 별로 group by 한 뒤 빈도를 count 한 테이블을 합치는 것이다. 단, 빈도가 없더라도 0으로 표기해야 하기 때문에, Inner Join이 아닌 left join 등을 이용하여 0~23의 값을 포함하고, 빈도가 없는 경우 NULL 대신 0으로 표기를 바꿔주면 된다.

위의 코드를 나눠서 분석하자면, 아래 순서로 보면 된다.

 

1. set @a := -1;
2. SELECT @a:=@a+1 as HOUR FROM ANIMAL_OUTS) A
3. SELECT HOUR(datetime) AS hour, COUNT(*) AS count FROM animal_outs GROUP BY HOUR(datetime)) B
4. (SELECT @a:=@a+1 as HOUR FROM ANIMAL_OUTS) A LEFT JOIN (SELECT HOUR(datetime) AS hour, COUNT(*) AS count FROM animal_outs GROUP BY HOUR(datetime)) B
5. SELECT A.HOUR, IFNULL(B.count, 0) FROM <위의 3번> ON A.HOUR = B.hour WHERE A.HOUR BETWEEN 0 and 23;

설명하자면,

1. a라는 변수를 만들고 -1을 대입한다. :=는 대입 연산자이다. (=으로 해도 되는데 쿼리문 안에서 =은 ==같은 의미이기 때문에 대입 연산자는 :=로 보통 표기한다고 한다.)
2. 우선 0부터 23 (혹은 그 이상)까지를 칼럼의 값으로 저장할 테이블이 필요하다. 위의 select문을 실행하면 animal_outs의 hour의 행 개수만큼 0부터 1씩 더해가며 행이 생긴다. (행이 99개면 99까지) 행 하나짜리의 테이블이 완성된다. 이것을 A 테이블이라고 명명.
3. datetime에서 시간만 뽑아낸 뒤 시간별로 group by 하여 빈도를 출력한다. 단, 이 테이블은 빈도가 0인 것은 아예 왼쪽 칼럼에 표기되지 않기 때문에 2번에서 생성한 테이블에 left join을 해주게 됩니다.
4. 두 테이블을 left join 한다. 큰 서브 쿼리가 된다고 생각하면 됨.
5. 둘을 합친 뒤 Hour와 count 행을 조회하면 되는데, 이때 IFNULL함수를 이용해서 아예 값이 없어서 NULL인 경우, 0으로 대체한다.