3. GROUP BY

문제

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

SELECT ANIMAL_TYPE, count(ANIMAL_ID) as 'count' from ANIMAL_INS
    where ANIMAL_TYPE = 'Cat' or ANIMAL_TYPE = 'Dog'
    group by ANIMAL_TYPE
    order by ANIMAL_TYPE asc;

동물 보호소에 존재하는 고양이와 개의 수를 구하는 문제로, 동물 보호소에 동물의 종류는 animal_type 으로 구분할 수 있다. test 데이터의 animal_type 에는 어떠한 동물 종류가 있는지 모르기 때문에 cat 과 dog 만 where조건으로 뽑아야 한다.
이 때,
where ANIMAL_TYPE IN ('Cat', 'Dog') 으로 해도 무방하다.

다른 방법으로, group by는 pandas의 groupby와 굉장히 비슷한 것으로 생각되며 cat 과 dog을 먼저 뽑지 않고 모든 level을 묶어준 뒤에 having 절을 통해서 원하는 level만을 추출 할 수 있다.

SELECT ANIMAL_TYPE, count(ANIMAL_ID) as 'count' from ANIMAL_INS
    group by ANIMAL_TYPE
    having ANIMAL_TYPE IN ('Cat', 'Dog')
    order by ANIMAL_TYPE asc;

마지막으로 오름차순으로 정렬한 이유는, '고양이를 개보다 먼저 조회해주세요.'라는 요구사항이 있었기 때문이다.

2) 동명 동물 수 찾기

SELECT NAME, count(ANIMAL_ID) as 'count' from ANIMAL_INS
	group by NAME having count(NAME) > 1
 	order by NAME asc;

같은 이름의 동물을 집계하기 위해서는 group by 로 동물의 name을 묶어주어야 한다. 그런데 동물의 이름이 NULL인 경우에도 group by로 집계가 된다. 하지만 count(NAME) 에서 name이 존재하지 않기 때문에 count 집계가 되지 않아 NULL은 제외되게 된다.\

만약에 name은 없지만 animal_id가 있는 경우에 count(animal_id) > 1 를 할 경우, 이름이 null이지만 animal_id가 존재하는 동물이 2마리 이상인 경우 결과에 노출되게 된다. 그런 일을 방지하기 위해서는 처음부터

where NAME is not NULL

을 통해 name이 null인 행을 제외하고 group by를 진행할 수 있다.

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

SELECT HOUR(DATETIME) as 'HOUR', count(HOUR(DATETIME)) as 'COUNT' from ANIMAL_OUTS
    # where HOUR(DATETIME) >= 9 and HOUR(DATETIME) < 20
    where HOUR(DATETIME) between 9 and 20
    group by HOUR
    # Having Hour between 9 and 20
    order by HOUR;

동물 보호소에서 시각에 따라 입양이 발생한 건수를 구하는 문제이다. 테이블에서는 입양된 시간은 DATETIME으로 '2020-08-21 12:12:29'의 형식을 가지고 있다. 먼저 이러한 형식에서 시각만을 추출하는 작업이 필요한데, HOUR() 을 사용하게 되면 매우 쉽게 추출할 수 있다.

그 뒤로는 시각을 그룹으로 묶어 count() 를 사용해 건수를 집계할 수 있다. 이 때에도 조건을 설정한 이후에 group by 를 사용할 수 있고 group by 이후에 조건을 설정할 수 있다.
그런데 where 절에서는 select 절에서 설정한 alias를 사용할 수 없고 group by 이후의 조건절인 having 절에서는 alias를 사용해 코드의 길이를 줄일 수 있다.

• YEAR()
• MONTH()
• DAY()
• MINUTE()
• SECOND()

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

level4의 난이도 상승으로 따로 다룰 예정입니다.