Programmers - SQL - GROUP BY 문제들 풀어보기

 

Programmers - SQL - SUM, MAX, MIN 문제들 풀어보기

지난번엔 SUM, MAX, MIN 문제들을 풀었습니다.

이번에는 GROUP BY 문제들을 풀어봅니다.

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

코딩테스트 연습 - 고양이와 개는 몇 마리 있을까

생물 종(동물의 종류)은 animal_type칼럼에 기록됩니다. 그래서 animal_type으로 group by를 적용하고 각 그룹별 count를 구하면 각 동물의 마릿수를 구할 수 있습니다. 그리고 고양이를 개보다 앞에 오도록 해야 합니다. 고양이가 cat이고 개는 dog이라서 오름차순으로 정렬하면 됩니다.

SELECT animal_type, count(*)
FROM ANIMAL_INS
group by animal_type
order by animal_type

이렇게 하면 통과하기는 한데요. 동물이 고양이와 개만 있기 때문인데요. 사실 다른 동물들도 있다면 이렇게 하면 안 됩니다. 다른 동물이 있고 개와 고양이만 조회하려면 다음과 같이 where 조건을 추가합니다.

SELECT animal_type, count(*)
FROM ANIMAL_INS
where animal_type in ('Cat', 'Dog')
group by animal_type
order by animal_type

 

동명 동물 수 찾기

코딩테스트 연습 - 동명 동물 수 찾기

같은 이름이 두 개 이상 있는 경우 이름별로 개수를 셉니다. 결과는 이름 순으로 정렬합니다.

group by name으로 이름으로 그룹핑합니다. 그리고 order by name으로 이름순으로 정렬합니다. 그리고 count함수를 이용해서 수를 셉니다. count결과는 count로 칼럼 이름을 바꿔주고요.

같은 이름이 두 개 이상인 내역을 조회하려면 having 절을 이용합니다. having count >= 2를 사용하면 2개 이상은 경우로 필터링합니다.

SELECT name, count(*) count
FROM ANIMAL_INS
group by name
having count >= 2
order by name

그런데 이렇게 해서 실행해 보면 실행 결과는 정상적으로 나오는데요. 제출해보면 틀렸다고 나옵니다. 함정이 있는데 동물 이름에 null이 있는 경우가 있기 때문입니다. 이름이 null인 내용을 제거해 주면 통과합니다.

SELECT name, count(*) count
FROM ANIMAL_INS
where name is not null
group by name
having count >= 2
order by name

 

입양 시각 구하기(1)

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

몇 시에 입양이 가장 활발하게 일어나는지 알아봅니다. 시간은 09:00~19:59까지입니다. 각 시간대별로 입양이 몇 건이 발생하는지 조회합니다. 결과는 hour와 count로 출력합니다.

입양정보가 있는 테이블을 animal_outs테이블입니다. 시간을 검색조건으로 사용하려면 hour함수를 이용합니다. hour(datetime) >= 9를 검색조건으로 넣으면 9시 이상이 됩니다. hour(datetime) < 20은 20시 전입니다. 시간별로 그룹핑하고 정렬합니다.

select hour(datetime) hour, count(*) count
from ANIMAL_OUTS
where hour(datetime) >= 9 and hour(datetime) < 20
group by hour
order by hour

이렇게 해도 통과하긴 한데요. hour(datetime) < 20 대신 hour(datetime) <= 19로 해도 됩니다. 그런데 사실 틀린 답일 수도 있습니다. 문제에서는 20시 전이 아니고 19:59까지라고 했기 때문입니다. 문제만 봤을 때 엄밀히 따지면 19:59:30에 입양된 내역은 나오면 안 될 수도 있는데 위의 쿼리를 이용하면 19:59:30도 포함되어 버립니다. 사실문제가 잘못되었을 거 같긴 한데 좀 애매합니다.

문제를 가지고 좀 더 엄밀히 풀어 보면 아마 다음과 같이 하는 것이 맞을 겁니다.

select hour(datetime) hour, count(*) count
from ANIMAL_OUTS
where time(datetime) >= '09:00'
and   time(datetime) <= '19:59'
group by hour
order by hour

 

 

입양 시각 구하기(2)

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

문제가 좀 까다로워졌습니다. 0시에서 23시까지 시간대별로 입양이 몇 건 씩 발생했는지 알아보는 방법입니다. 이전 문제는 입양이 발생하지 않은 시각의 데이터는 출력하지 않았습니다. 보통 이런 경우 비워두고 결과를 받아서 처리하는 애플리케이션에서 빈 시간을 채워주는 식으로 구현합니다. 하지만 여기서는 DB에서 빈 시간을 채워줘야 합니다.

0~23의 데이터가 들어있는 테이블을 만들어서 사용할 수 있고요. 쿼리에서 가상의 테이블을 임시로 생성하는 방법 등 다양한 방법이 있습니다. 저는 그냥 별다른 기교를 부리지 않고 쿼리로 0~23의 데이터를 만들고 outer join을 이용해서 풀었습니다.

select hour, count(a.animal_id)
from (SELECT 0 AS hour
         UNION ALL SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
         UNION ALL SELECT 6
         UNION ALL SELECT 7
         UNION ALL SELECT 8
         UNION ALL SELECT 9
         UNION ALL SELECT 10
         UNION ALL SELECT 11
         UNION ALL SELECT 12
         UNION ALL SELECT 13
         UNION ALL SELECT 14
         UNION ALL SELECT 15
         UNION ALL SELECT 16
         UNION ALL SELECT 17
         UNION ALL SELECT 18
         UNION ALL SELECT 19
         UNION ALL SELECT 20
         UNION ALL SELECT 21
         UNION ALL SELECT 22
         UNION ALL SELECT 23
) h left join ANIMAL_OUTS a on h.hour = hour(a.datetime)
group by hour
order by hour