SQL 고득점 Kit-프로그래머스 GROUP BY

#group by having
#set
#null값 count 하기

GROUP BY

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

SELECT animal_type, count(*) from animal_ins
group by animal_type
order by animal_type

 

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

SELECT name, count(*) from animal_ins
group by name having count(name) >= 2
order by name

 

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

SELECT hour(datetime) as hour, count(*) as count from animal_outs
group by hour having hour >=9 and hour <=19
order by hour

 

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

 

이 문제는 조금 까다로웠다. 아는 선에서 아래처럼 코드를 작성하니,

SELECT hour(datetime) as hour, count(*) as count
from animal_outs
group by hour having hour <=23
order by hour

실행결과는 아래와 같았는데, count 값이 null인 것은 표시가 되지 않는 문제가 있다.

 

검색을 해보니 바로 연관검색어가 뜨는 것을 보면, 많은 사람들이 겪는 문제로 보인다.

 

아니 뭔가 참 간단할 것 같은데, 많이 찾아봐도 일단 찾지못했다 ㅠ 결론은 마법같은 한줄 추가 같은건 없고 다른 방법을 써야했다.

 

검색해서 찾아낸 방법은, 변수를 선언하는 것이다.

 

접근 방법은 이러하다.

count값이 Null 일때도 알아서 해주면 좋은데, 그 Null 값을 넣어줄 row가 없기 때문에 불가능하다.

따.라.서. 먼저 row를 만들어 주고 나서 count를 해주면 null값을 0으로 표시해줄수 있다.

 

정답코드는 아래와 같다.

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

 

이제 한 줄 한 줄 살펴보자.

set @hour := -1;
#hour라는 함수를 선언하고, 초기값을 -1로 설정합니다.

SELECT
#첫 번째 컬럼/hour: 로컬변수 hour를, 로컬변수 hour에 1씩더하는 형태로 써서 0~23을 출력합니다.
(@hour := @hour +1) as hour,

#두 번째 컬럼/count: 
    (select count(*) from animal_outs
    where hour(datetime) = @hour)
    as count
from animal_outs

where @hour <23
#@hour 값이 23 전까지, 즉 22까지 계속 @hour+1을 반복합니다.

sample table에는 비는 구간이 있다 (0-6시, 20-23시가 없다). 따라서, 0-23시 row를 새로 만들어줘야한다. 

 

초기값이 -1인 이유는:
-시간은 1씩 증가하기 때문에 @hour := @hour+1 식을 사용해야한다.
-0~23시를 출력하기 위해선, 초기값이 -1이여야 위 식을 통해서 첫 출력 값이 0이 되기 때문이다.

 

썹쿼리로 두번째 컬럼 count를 작성해줘야 하는 이유는:

-로컬변수 hour로 0부터 23시를 새로 만들었기 때문에
-animal_outs에 있는 hour(datetime)을 로컬변수hour와 매칭시켜준다.
-그 다음 coun(*)를 출력하면, Null인 row는 0으로 카운트 해준다.

---

Set

Set 함수에 대해 더 알아보자.

 

variable/변수는 어떤 값을 잠깐 담아두는 메모리라고 보면된다. 

변수는 3종류로 나뉜다.

 

1. User-defined variable 유저가 만드는 변수

한번 실행하고 나면 사라지는 변수다. 다른 사람은 쓸 수 없다.

@변수이름 := 초기값;

 

2. Local Variable

1과 달리 저장되는 변수이기 때문에, 1과 차이는 다른 사람이 쓸 수 있느냐 없느냐인 듯하다.

DECLARE 변수이름 데이터타입 초기값;

 

3. Global Variable 이미 시스템에 있는 built-in 변수

@@변수이름

:=은 비교연산자 = 과 구별하기 위해서 쓰는 대입연산자다.
; 땀을 흘려줘야 변수가 선언된다.

 

Reference

chanhuiseok.github.io/posts/db-6/

[프로그래머스] 입양 시각 구하기(1), (2) (GROUP BY, HAVING, SET)

stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql

How to declare a variable in MySQL?