1. 고양이와 개는 몇 마리 있을까
문제
시행착오
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE LIKE 'Cat' OR ANIMAL_TYPE LIKE 'Dog'
ORDER BY ANIMAL_TYPE;처음에 group by를 안했더니 출력 결과에 cat과 count가 100으로 출력되었다.
group by를 하지 않으면 where 절에서 필터링한 고양이와 개를 합친 하나의 그룹의 개수를 한꺼번에 세버리게 된다. 그래서 고양이와 개의 개수를 합쳐서 100이라고 출력되고, animal_type도 맨 첫 번째 고양이만 출력된 것이었다..
정답 풀이
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE LIKE 'Cat' OR ANIMAL_TYPE LIKE 'Dog'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;WHERE 절에서 동물 타입 이름이 Cat이거나 Dog인 행들을 필터링한다. 그리고 GROUP BY ANIMAL_TYPE 으로 고양이와 개 그룹을 먼저 나눈 후, COUNT로 그룹별로 개수를 세준다. 마지막으로 고양이를 먼저 출력해주기 위해 ORDER BY를 사용하여 abc순 정렬을 해준다.
다른 풀이
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat', 'Dog') -- IN을 사용해도 된다!
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;2. 입양 시각 구하기(1)
문제
시행착오
SELECT HOUR(DATETIME) HOUR, count(DATETIME) as COUNT
FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME, '%H') BETWEEN '9' AND '19'
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)DATE_FORMAT()은 반환형이 STR인데, 오전 9시라면 ‘09’와 같이 반환한다. 따라서 ‘9’로 비교를 하면 ‘09’의 ‘0’이 ‘9’보다 작다고 판단하여 데이터를 누락시켜버린다.
정답 풀이
SELECT HOUR(DATETIME) HOUR, count(DATETIME) as COUNT
FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME, '%H') BETWEEN 9 AND 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)DATE_FORMAT(DATETIME, '%H') BETWEEN 9 AND 19 와 같이 작성하면 MySQL이 반환된 문자를 숫자로 암시적으로 형변환을 해주어 알아서 비교해준다. 이를 이용하여 9:00~19:59 사이 시간들을 필터링한다. 그 후 시간별로 그룹핑하고 오름차순 정렬하여 개수를 출력해준다.
다른 풀이
SELECT HOUR(DATETIME) HOUR, count(DATETIME) as COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)HOUR() 함수는 INTEGER로 반환해주기 때문에 간단하게 WHERE HOUR(DATETIME) BETWEEN 9 AND 19 로 작성해주어도 된다. 데이터베이스가 억지로 형변환을 하지 않아도 되기 때문에 성능상으로도 이게 더 좋은 방법인 것 같다…ㅎㅎ
3. NULL 처리하기
문제
정답 풀이
SELECT ANIMAL_TYPE, IFNULL(NAME, "No name") AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_IDANIMAL_TYPE, NAME, SEX_UPON_INTAKE을 차례로 출력하되, IFNULL() 함수를 사용하여 이름이 없을 경우 “No name”으로 출력되도록 하였다. 그리고 ORDER BY를 이용해 ANIMAL_ID를 기준으로 오름차순 정렬하였다.
4. DATETIME에서 DATE로 형 변환
문제
정답 풀이
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;문제에서 ‘년-월-일’로 출력하라 하였기에 DATE-FORMAT()을 이용하여 해당 형식으로 바꿔주었다. 그리고 ORDER BY로 동물 아이디 기준 오름차순 출력하였다.
5. 중성화 여부 파악하기
문제
정답 풀이
SELECT ANIMAL_ID, NAME, CASE
WHEN SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
WHEN SEX_UPON_INTAKE LIKE 'Intact%' THEN 'X'END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;SELECT 절에서 CASE절을 이용해 SEX_UPON_INTAKE가가 Neutered 또는 Spayed으로 시작하면 O를, Intact으로 시작하면 X를 출력하도록 분기문을 작성하였다. 그리고 ANIMAL_ID을 기준으로 오름차순 출력하였다.
6. 오랜 기간 보호한 동물(1)
문제
정답 풀이
SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS ILEFT
OUTER JOIN ANIMAL_OUTS O ON I.ANIMAL_ID=O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY I.DATETIME
LIMIT 3;ANIMAL_INS 기준으로 ANIMAL_OUTS와 왼쪽 외부 조인을 하면 자연스럽게 ANIMAL_INS에는 있지만(보호소에 들어왔지만) ANIMAL_OUTS에 없는(입양을 못 간) 데이터는 NULL값으로 채워진다. 따라서 이를 이용해 ANIMAL_OUTS 테이블의 DATETIME이 NULL값인 행들을 필터링하였다. 그 후 DATETIME 오름차순 정렬 후 LIMIT 3으로 3개의 데이터만 출력하였다.
7. 없어진 기록 찾기
문제
정답 풀이
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
LEFT OUTER JOIN ANIMAL_INS I ON I.ANIMAL_ID=O.ANIMAL_ID
WHERE I.NAME IS NULL AND O.NAME IS NOT NULL
ORDER BY O.ANIMAL_ID;ANIMAL_OUTS 기준으로 왼쪽 조인을 하면 ANIMAL_INS에 없는 내용은 자동으로 NULL로 채워지므로, 조인 후 ANIMAL_INS쪽 이름이 NULL인 행들을 필터링하면 정보가 유실된 데이터를 고를 수 있다. 여기까지만 하고 코드를 실행해보면 이름이 비어있는 데이터도 같이 출력된다. 이는 ANIMAL_OUTS에도 데이터가 없는 것이므로 데이터가 유실된 것이 아닌 원래부터 존재하지 않았다고 보는게 맞을 것 같다. 존재하지 않았던 데이터들까지 O.NAME IS NOT NULL로 걸러준다.
마지막으로 동물 아이디 기준 오름차순 정렬하여 출력한다.
8. 헤비 유저가 소유한 장소
문제
시행착오
-- 진짜 모르겠다...
SELECT *
FROM PLACES
WHERE EXISTS (SELECT 1
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(DISTINCT ID)>1)
ORDER BY ID;EXISTS 안쪽 쿼리가 의미하는 건 테이블 전체를 대상으로 장소를 2개 이상 가진 host_id가 하나라도 존재하냐?다.. 그래서 예시 데이터에는 존재한다라는 참 값만 반환하니, 메인 쿼리는 아무 필터링도 되지 않고 places 테이블의 모든 데이터를 다 출력해버린다.ㅠㅠ
정답 풀이
아직도 group by가 너무 헷갈린다…. group by의 동작방식을 제대로 이해하고자, 쿼리문의 실행 흐름을 정리해 볼려고 한다.
일단, group by는 내가 지정한 열을 기준으로 바구니를 만들어 데이터 압축해 저장해주는 방식이다. 내가 헷갈렸던건, 아래 코드를 실행하면 하나의 host_id에 하나의 id값만 출력되었기 때문이다.
SELECT *
FROM PLACES
GROUP BY HOST_ID우리 눈에는 값이 하나 있는 것처럼 출력되지만, 실제 컴퓨터에는 하나의 host_id에 해당하는 데이터들이 바구니 안에 압축되어 저장되어 있다.
SELECT *
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(DISTINCT ID) > 1그래서 위와 같이 작성하면, 바구니에 압축된 데이터들을 확인하여 하나의 host_id에 두 개 이상의 id가 있으면 데이터를 출력해준다.
이제 이를 활용하여 서브쿼리를 만들어 보자.
SELECT *
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(DISTINCT ID) > 1)
ORDER BY ID;위와 같이 작성하면, 우선 하나의 host_id에 2개 이상의 id를 가진 Host_id 명단을 만든다. 그리고 메인 쿼리는 서브쿼리에서 만든 명단과 host_id를 대조해보며, 명단에 host_id가 존재하면 해당 데이터를 ID 기준 오름차순 출력한다.
다른 풀이
-- feat. Gemini
SELECT *
FROM PLACES A
WHERE EXISTS (
SELECT 1
FROM PLACES B
WHERE A.HOST_ID = B.HOST_ID -- 1. "나랑 똑같은 호스트 아이디를 가졌는데,"
AND A.ID != B.ID -- 2. "장소 아이디는 나랑 다른 데이터가 존재해?"
)
ORDER BY A.ID;EXISTS를 사용하는 코드도 보고 싶어서 제미나이에게 정답을 물어봤다. 위 코드는 무거운 group by를 사용하지 않고 exists를 사용하는 버전이다.
동작방식을 살펴보면, 우선 서브 쿼리에서 A의 데이터 행과 B의 데이터가 호스트 아이디는 같은데, id가 다른 것이 존재하면 참을 반환하는 형식이다. id가 다르다는 것이 결국 서로 다른 장소 2개 이상을 가졌다는 의미와 같기 때문에 group by를 사용하지 않아도 된다.
성능 분석
IN을 사용하는 방식은 서브쿼리에서 처음 명단을 만들고 메인쿼리에서 대조만하기 때문에, 반복문처럼 도는 EXISTS 방식보다 성능이 좋을 것이라 예상했었다. 하지만 찾아보니 EXISTS는 테이블을 끝까지 돌지 않는다고 한다. 쿼리에서 제시한 조건을 만족하면 바로 참을 반환하고 다음 데이터로 넘어가기 때문에 EXISTS가 훨씬 빠르다고 한다..!
