[SQL 연습 문제] 프로그래머스 SQL 고득점 키트 어려웠던 문제 풀이

드디어 프로그래머스 SQL 문제 Level 1 ~  Level 4까지 정주행을 마쳤습니다. 한동안 열심히 풀다가 Level 3에서 방치해둔 상태였는데요. 오늘 각 잡고 기초 문법 복기를 위해 Level 1부터 끝까지 다시 풀었습니다. Level 1, 2는 문제를 읽으면서 바로바로 쿼리문이 떠올랐고 Level 3부터는 고민이 필요했습니다. 그중 가장 어려웠던 문제, 며칠 뒤 다시 풀어봤을 때도 헤맬 것 같은 3문제만 준비해보았습니다. 

헤비 유저가 소유한 장소

프로그래머스

문제

• 공간을 둘 이상 등록한 사람을 '헤비 유저'라고 부른다. 헤비 유저가 등록한 공간의 정보 출력
• 아이디 순으로 조회

 

해결 과정

1. 2번 이상 등장한 HOST_ID를 WHERE절 서브쿼리로 넣어서 필터링되도록 한다.

-- 2번 이상 등장한 HOST_ID 찾기
SELECT HOST_ID
FROM PLACES 
 GROUP BY HOST_ID
HAVING COUNT(HOST_ID)>=2

 

2. HOST_ID가 WHERE절에서 필터링된 HOST_ID 목록에 있다면 조회한다.

SELECT * 
FROM PLACES 
WHERE HOST_ID IN (SELECT HOST_ID
                  FROM PLACES 
                  GROUP BY HOST_ID
                  HAVING COUNT(HOST_ID)>=2)

 

Solution

SELECT * 
FROM PLACES 
WHERE HOST_ID IN (SELECT HOST_ID
                  FROM PLACES 
                  GROUP BY HOST_ID
                  HAVING COUNT(HOST_ID)>=2)

---

보호소에서 중성화한 동물

프로그래머스

문제

• 보호소에 들어올 당시에는 중성화 X (Intact)
• 보호소를 나갈 당시에는 중성화 O (Spayed, Neutered)
• 동물의 아이디와 생물 종, 이름을 조회
• 아이디 순으로 조회

 

해결 과정

-- 보호소에 들어올 당시에는 중성화 X
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_INS 
WHERE SEX_UPON_INTAKE LIKE 'Intact%'

-- 보호소를 나갈 당시에는 중성화 O
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE 'Spayed%' 
OR SEX_UPON_OUTCOME LIKE 'Neutered%'

 

Solution

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS A
INNER JOIN (SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
      FROM ANIMAL_OUTS
      WHERE SEX_UPON_OUTCOME LIKE 'Spayed%' 
      OR SEX_UPON_OUTCOME LIKE 'Neutered%') B ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%'
ORDER BY A.ANIMAL_ID

---

입양 시각 구하기(2)

프로그래머스

문제

• 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회
• 시간대 순으로 정렬

 

해결 과정

1. 각 시간대별로 입양이 몇 건 발생했는지 조회한다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS 
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

입양이 발생하지 않은 시간대가 존재한다. 문제에서는 0~23시까지라고 했으므로 입양이 발생하지 않은 시간대까지 나올 수 있도록 임의로 HOUR 테이블을 만들어야 한다.

 

2. 재귀 호출을 사용하여 0~23시까지 HOUR 테이블을 만들어 준다.

재귀호출 WITH RECURSIVE

• 비 재귀적인 초기값 필요
• 계산식과 정지 조건 필요
• UNION OR UNION ALL로 결합

WITH RECURSIVE 테이블명 AS (
SELECT 초기값 -- 비 재귀적 파트
UNION ALL -- OR UNION
SELECT 계산식 FROM 테이블명 WHERE 제어문 ) -- 재귀적 파트

WITH RECURSIVE hr AS(
    SELECT 0 AS hr -- 초기값 0을 만들고
    UNION ALL -- 아래 행에 붙인다
    SELECT hr+1 FROM hr WHERE hr<23) -- 23까지
    
SELECT * FROM hr -- 확인

 

3. 같은 시간대를 기준으로 LEFT JOIN, 만약에 입양 건수가 없으면(=NULL) 0으로 처리

SELECT A.HR, IFNULL(B.COUNT,0) AS COUNT
FROM hr A
LEFT JOIN (
            SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
            FROM ANIMAL_OUTS 
            GROUP BY HOUR ) B ON A.HR = B.HOUR

 

Solution

WITH RECURSIVE hr AS(
    SELECT 0 AS hr
    UNION ALL
    SELECT hr +1 FROM hr WHERE hr<23)   
SELECT A.HR, IFNULL(B.COUNT,0) AS COUNT
FROM hr A
LEFT JOIN (
            SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
            FROM ANIMAL_OUTS 
            GROUP BY HOUR ) B ON A.HR = B.HOUR
ORDER BY A.HR