[프로그래머스\SQL 고득점 kit] GROUP BY - 입양 시각 구하기(2) 풀이 2가지 (mysql)

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

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

 

첨엔 GROUP BY로 해결하려 했으나 COUNT가 0인 부분에 대해서 조회할 수 없어서 실패 

두번째 시도는 UNION ALL 이었는데,,,, (sol 2) 같이 일일히 다 써야하기 때문에 패스! 

(sol 1) SET, @변수 사용 

SET @HOUR := -1;
SELECT (@HOUR := @HOUR+1) AS HOUR, (SELECT COUNT(*) 
                                    FROM ANIMAL_OUTS
                                    WHERE HOUR(DATETIME) = @HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

 

1. "SET" 사용 : 변수 선언 (어떤 변수에 특정 값을 할당 하는 것을 말함)

- 변수 앞에 @ 지정 : 프로시저가 종료되어도 유지됨 , 따라서 값 누적이 가능

 - " = ", " := "에 따라 의미가 달라진다 

• " = " : 대입연산자, 비교연산자 
• " := " : 대입연산자로만 사용,
• = 는 대입연산자로도 쓸 수 있지만, 비교연산자와의 혼동을 막기위해 := 주로 사용

따라서, HOUR라는 변수를 프로시저가 종료되어도 유지되도록 설정하고, -1로 초기화 -> SET @HOUR := -1; 

 

※ 왜 -1 인가? 

SELECT 문에서 (@HOUR := @HOUR +1) AS HOUR 로 지정하는데, 이 식 때문에 -1+1 = 0 부터 시작하게 된다! 

 

2.  HOUR가 0~23까지 1씩 증가해야하므로 (@HOUR := @HOUR +1) 로 지정해주고 alias 사용하여 HOUR 칼럼명 지정

COUNT의 경우는, HOUR(DATETIME)과 @HOUR변수의 값이 동일할 때의 개수를 세어주면 되므로 아래와 같다

(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT

 

3. FROM ANIMAL_OUTS(테이블 선정)

 

4. WHERE @HOUR<23 : @HOUR가 23이 될때까지

(sol 2) UNION ALL 사용

SELECT HOUR, COUNT
FROM(
    SELECT 0 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 0
    UNION ALL
    SELECT 1 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 1
    UNION ALL
    SELECT 2 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 2
    UNION ALL
    SELECT 3 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 3
    UNION ALL
    SELECT 4 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 4
    UNION ALL
    SELECT 5 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 5
    UNION ALL
    SELECT 6 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 6
    UNION ALL
    SELECT 7 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 7
    UNION ALL
    SELECT 8 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 8
    UNION ALL
    SELECT 9 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 9
    UNION ALL
    SELECT 10 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 10
    UNION ALL
    SELECT 11 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 11
    UNION ALL
    SELECT 12 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 12
    UNION ALL
    SELECT 13 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 13
    UNION ALL
    SELECT 14 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 14
    UNION ALL
    SELECT 15 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 15
    UNION ALL
    SELECT 16 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 16
    UNION ALL
    SELECT 17 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 17
    UNION ALL
    SELECT 18 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 18
    UNION ALL
    SELECT 19 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 19
    UNION ALL
    SELECT 20 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 20
    UNION ALL
    SELECT 21 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 21
    UNION ALL
    SELECT 22 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 22
    UNION ALL
    SELECT 23 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 23
    UNION ALL
    SELECT 24 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 24
    ) AS t
WHERE HOUR >= 0 AND HOUR <= 23
ORDER BY HOUR

 

참고로, UNION ALL 과 UNION의 차이는

UNION ALL은 중복을 제거하지 않고 모두! (합집합+교집합)

UNION은 중복을 제거한상태로 합치는 것! (합집합)