[프로그래머스 SQL 문제] 입양 시각 구하기(2)

https://programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(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문을 실행하면 다음과 같이 나와야 합니다.

HOURCOUNT

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

 

 

이번건 꽤 애를 먹었다. substr, date_format, hour 모두 값이 있는 항목에 대해서만 나타내기때문에 존재하지 않는 값들을 따로 추가해야하는 방법을 생각해야 했다.

 

답안을 작성하기 앞서 'recursive문' 을 배울 필요가 있다.

recursive 는 JAVA, Python 의 for 반복문 같은 것이다. 

 

<recursive 기본 형식>

WITH RECURSIVE 테이블명 AS (
	SELECT 초기값 as 컬럼명
    UNION ALL
    SELECT 컬럼명 계산식
    FROM 테이블명
    WHERE 제어문)

작동 원리

1. 메모리 상에 가상의 테이블을 저장

2. RECURSIVE 쿼리를 이용하여 실제로 테이블을 생성하거나 데이터 삽입을 하지 않아도 가상 테이블을 생성할 수 있다.

 

 

ex) hour 라는 컬럼에 1시부터 10시까지 데이터를 갖는 가상의 테이블을 생성해보자!

with recursive hour_tb as(
    select 1 as hour
    union all
    select hour + 1 from hour_tb where hour < 10)

select *
from hour_tb

- 초기값 1부터 시작하여 계속 1시간씩 더해주었고 시간이 < 10 일때 까지 반복문을 돌려 'hour_tb'란 테이블을 만들었다

- 참고로 where hour < 10 이라고 하면 9까지 만들어질 것 같지만 9 일때 10을 만들고 11을 만들려는 10일때 그만두기 때문에 10까지 만들어진다!

- 왜인지는 모르겠으나 select * from hour_tb 를 하지 않으면 코드에 오류가 난다. 테이블을 만들고 바로 써먹어야하나 싶다 (흠 이건 더 생각해보는 걸로) 

 

 

< 답안 >

 

with recursive hour_tb as(
    select 0 as hour
    union all
    select hour + 1 from hour_tb where hour < 23)

select hour_tb.hour, 
        ifnull(original_tb.cnt,0)
from hour_tb
left outer join 
    (select date_format(datetime,'%H') as h, count(1) as cnt
    from animal_outs
    group by h) as original_tb
on hour_tb.hour = original_tb.h

(아니 왜갑자기 색깔입혀지고 지랄이지? 하 너무 힘드므로 모르쇠하겠다)

 

전반적인 플로우는 이렇다.

1. recursive 문을 통해 hour 칼럼이 0-23 까지 있는 테이블을 만든다.

2. 입양 시각 구하기(1) 문제 처럼 본래 테이블에서 해당 시간 별 동물 수 테이블을 만든다.

3. 1테이블과 2테이블을 조인한다. 

 - 이때 1테이블에 23같은 경우는 본래 테이블에 값이 없기 때문에, 0 값을 추가해주는 과정을 넣어야한다. (나는 여기서 infull 함수를 사용했다. ('coalesce 함수' 를 사용하는 분도 계셨다.)

 

 

 

 + 'coalesce' 와 'ifnull' 차이가 궁금해졌다.

 => ifnull 는 하나만, coalesce 는 여러개의 파라미터를 넣을 수 있다 ! (ifnull은 MYSQL에만 있고 coalesce는 표준함수라고 한다. 그래서 coalesce 함수를 권장한다고 한다)

 

*coalesce 함수 설명

 coalesce( 인수1, 인수2, 인수3, (치환값))

 : 왼쪽부터 차례로 인수를 보고 처음으로 NULL이 아닌 값이 나타났을 때 그 값을 반환한다. 치환하려는 값을 지정해주    면 그 값으로 변경해준다.

 -> 그러므로 단순히 NULL 값이 아닌 값을 알아내려고도 사용할 수 있지만, NULL 값에 해당 값을 치환할 때도 사용할 수 있다.

 

1) coalesce(col1,col2)

-> null 값이 아닌 처음 값을 반환

2) coalesce(co1,col2, 0)

-> null 값을 0으로 치환

 

1의 경우 주의할 점이 있다.

주의 사항은 해당 사이트를 참고했다. (http://www.gurubee.net/article/83405 )

 

col1	col2
100	100
null	60
null	null

위와 같은 tb 라는 테이블이 있다

 

select coalesce(col1, col2*50, 50)
from tb

위와 같은 명령을 내렸을 때를 생각해보자.

 

100

5000

50

이라는 결과 값을 예상했다. 왜냐하면 col1 에서 null 값이 아닌 첫번째값은 100이며, col2 에서도 null 값이 아닌 첫번째값이 100이기 때문이다.

 

그러나 생각해보자. 

위 함수 설명에 나와있듯이 '왼쪽부터 차쳬로 인수를 본다'

열을 따로따로 보는 게 아니라 행을 보는 거라고 생각하면 쉽다.

먼저 각 계산식을 적용한다고 생각해보자

1행 : (100, 100*50, 50) = 100

2행 : (null, 60*50, 50) = 3000

3행 : (null, null*50, 50) = 50

 

 

100

3000

50

이 정답이 된다!

(내가 정리하긴 했지만 머리가 터질 것 같다..완벽한 숙지를 통해 본문을 깔끔하게 수정해야 할듯 싶다)

 

 

우와 머리아프다~!