mysql - 서브쿼리(subquery) 응용

시작

프로그래머스 2021 winter coding - 겨울방학 스타트업 인턴 프로그램에 접수를 하고 코딩테스트를 했다.

총 4문제가 나왔다. 

알고리즘 문제는 3개이고 sql문제가 1개이다.

총 2시간 동안 시험을 보는데 알고리즘 문제는 느낌상 40분 컷으로 3문제중 2.8 솔을 했다.

나머지 1시간 20분동안 sql문제를 풀었지만......

문법 오류로 답을 제출하지 못했다.

아직은 정확하게 내가 어디 부분을 몰라 틀린지는 모르지만 서브 쿼리 혹은 join과 관련된 부분인 것 같다.

화가 나지만 다음에 틀리지 않기 위해 내가 모르는 sql지식을 채우자.

 

서브쿼리(subquery)

mysql에서 서브쿼리는 다른 쿼리 안에 있는 쿼리입니다.

예를 들어 select, insert, update, delete 또는 또 다른 서브쿼리 안에 존재할 수 있습니다.

서브쿼리는 아래와 같은 형태의 문법을 가집니다.

select * from table1
where col = (select col form table2 )

여기서 table1은 where절에 서브쿼리를 포함하고 있습니다.

이렇게 서브쿼리를 포함하는 table1을 '서브쿼리 table 2를 포함하는 메인쿼리 table1'이라 합니다.

서브쿼리와 메인쿼리 사이에는 규칙이 존재합니다.

 

규칙은 다음과 같습니다.

1. 서브쿼리는 메인쿼리의 컬럼에 사용 가능하다.

2. 메인쿼리는 서브쿼리의 칼럼에 사용 불가능하다.

 

이러한 규칙을 만든 정확한 이유는 모르겠지만,

쿼리 1개는 table1개를 만드는 일이기 때문에 위의 규칙을 적용해도 모든 경우를 커버할 수 있습니다.

또한 위 규칙은 실행 순서에 영향을 미치는데,

서브쿼리가 메인 쿼리의 칼럼에 사용할 수 있는 자유도가 있기 때문에,

서브쿼리가 먼저 실행되고 그다음으로 메인쿼리가 실행이 됩니다.

 

실행 순서를 상상하며 쿼리를 작성하면 좀 더 크리스탈 클리어하게 쿼리를 작성할 수 있니 기억해 둡시다.

서브쿼리 3가지 문법

서브쿼리는 메인쿼리의 select, from, where 뒤에 사용할 수 있습니다.

문법은 대략 이렇습니다.

SELECT의 경우 JOIN과 흡사하게 사용할 수 있습니다.

SELECT COL1, (SELECT FROM TABLE2 WHERE TABLE1.KEY = TABLE2.KEY ) 
FROM TABLE1

from은 저장된 테이블의 조건을 주어 가져올 때 사용 가능합니다.

SELECT COL1, SUM 
FROM (
      SELECT COL1, SUM(COL2) AS 'SUM' 
      FROM TABLE1 
      GROUP BY COL2
      )

where은 조건을 추가할 때 사용됩니다.

SELECT COL1 FROM TABLE1
WHERE COL1 IN (SELECT COL2 FROM TABLE2)

서브쿼리 언제 사용?

자 서브쿼리를 어떻게 언제 사용하는지 감을 익히기 위해 아래의 문제를 읽어 봅시다.

https://programmers.co.kr/learn/courses/30/lessons/59045?language=mysql 

코딩테스트 연습 - 보호소에서 중성화한 동물

출처: 프로그래머스 코딩 테스트 연습, https://programmers.co.kr/learn/challenges

문제는 대략 이렇습니다.

동물이 들어온 기록을 가진 테이블 ANIMAL_INS와 동물이 나간 기록을 가진 ANIMAL_OUTS테이블이 존재합니다.

동물이 들어올 땐 중성화되지 않았지만 나갈 땐 중성화된 동물을 찾는 문제입니다.

 

가장 단순하게 생각해 봅시다.

일단 ANIMAL_INS에서 중성화되지 않은 동물을 뽑아서 저장해 둡니다.

그리고 ANIMAL_OUTS에서 중성화된 케이스를 뽑습니다.

위의 계산된 테이블에서 ANIMAL_ID가 모두 포함된 케이스를 찾습니다. 

 

위의 생각을 구현해 봅시다.

WITH문을 이용하여 조건에 충족되는 테이블을 저장하고 이름을 붙여줍니다.

그리고 두 테이블에 모두 포함하고 있는 ROW를 서브쿼리로 구현하여 정답으로 제출합니다.

 

NOTE
WITH문은 가상테이블(view)을 만드는 구문이다.
가상테이블(view)이란 변수처럼 가공한 데이터를 잠시 사용하는 용도로 만든 테이블을 말한다.
버퍼캐쉬란 곳에 올려 사용하기 때문에 메모리 사용량이 올라간다는 단점을 가지고 있다.
하지만 반복되는 쿼리가 많다면 with구문을 사용하여 계산량을 줄일 수 있는 장점을 가지고 있다.
코딩테스트에선 쿼리가 1개이다. 
그래서 굳이 사용할 필요는 없지만 문제를 단순화할 수 있는 장점이 있어 시간이 부족할 때 작성하기 좋다.

 

-- 코드를 입력하세요

WITH 
    In_Intact AS(
    SELECT * FROM ANIMAL_INS
    WHERE SEX_UPON_INTAKE LIKE 'Intact%'
    ),

    Out_Not_Intact AS(
    SELECT * FROM ANIMAL_OUTS
    WHERE SEX_UPON_OUTCOME LIKE '%Spayed%' OR SEX_UPON_OUTCOME LIKE '%Neutered%'
    )   

--- ()안의 쿼리가 서브쿼리이다.
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM Out_Not_Intact
WHERE ANIMAL_ID IN (SELECT ANIMAL_ID FROM In_Intact)

1개만 with구문으로 처리하고 구현할 수도 있습니다.

-- 코드를 입력하세요

WITH In_Intact AS(
SELECT ANIMAL_ID, SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE 'Intact%'
)

--- ()안의 쿼리가 서브쿼리이다.
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_OUTS
WHERE (SEX_UPON_OUTCOME LIKE 'Spayed%' OR SEX_UPON_OUTCOME LIKE 'Neutered%')
AND ANIMAL_ID IN  (SELECT ANIMAL_ID FROM In_Intact)

JOIN을 이용해 보자.

JOIN은 서브쿼리를 대체할 수 있는 문법입니다.

외래키를 이용하여 연결하고 ROW의 COLUNM의 수가 증가합니다.

앞에서 배운 SELECT 위치에 서브쿼리를 작성하는 방법과 같은 결과를 리턴합니다. 

실제로 1개의 쿼리를 처리할 땐 JOIN이 서브쿼리 보단 성능이 좋다고 합니다.

-- 코드를 입력하세요
SELECT AO.ANIMAL_ID, AO.ANIMAL_TYPE, AO.NAME FROM ANIMAL_OUTS AS AO
JOIN ANIMAL_INS AS AI
ON AO.ANIMAL_ID = AI.ANIMAL_ID
WHERE (AO.SEX_UPON_OUTCOME LIKE 'Spayed%' OR AO.SEX_UPON_OUTCOME LIKE 'Neutered%' )
AND AI.SEX_UPON_INTAKE LIKE 'Intact%'

그룹바이(GROUP BY)와 서브쿼리

약간 뇌정지가 오는 파트입니다.

그래도 차근 차근 잘해봅시다.

요걸 익히기 위해 문제를 봅시다.

https://programmers.co.kr/learn/courses/30/lessons/77487

코딩테스트 연습 - 헤비 유저가 소유한 장소

출처: 프로그래머스 코딩 테스트 연습, https://programmers.co.kr/learn/challenges

문제는 대략 이렇습니다.

공간 대여 히스토리를 담은 테이블 PLACES가 있습니다.

PLACES는 ID, NAME, HOST_ID롤 구성되어 있습니다.

이 테이블에서 HOST_ID가 2 이상 존재하는 HOST_ID를 헤비 유저라 정의합니다.

이러한 상황에서 헤비유저가 포함되어 있는 ROW를 출력하면 됩니다.

 

먼저 해비 유저에 대해서 정의합시다.

WITH를 이용하여 헤비유저를 정의합시다.

헤이 유저는 GROUP BY로 HOST_ID를 묶고 조건절을 이용하여 2 이상인 것을 고르면 됩니다.

정의한 해비 유저 가상테이블을 이용하여 PLACES에서 HOST_ID가 헤비 유저 ID 테이블에 존재하는지 확인합니다.

 

-- 코드를 입력하세요
WITH HAVY_USER AS (
SELECT HOST_ID FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) >= 2
)

SELECT ID, NAME, HOST_ID FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID FROM HAVY_USER)

코딩테스트에서 틀린 문제 (조건 2개를 이용한 GROUP BY => 이를 COUNT => COUNT중최댓값 출력)

정확히는 기억이 나지 않지만........ ㅅㅂ

ID, DATETIME, PLACENAME, HOSTID로 구성된 '예약'테이블이 있습니다.

이 테이블에서 월별로 가장 많이 예약이 된 PLACE의 예약수를 구하는 문제입니다.

 

위의 상황을 연습하기 위해 아래의 문제 환경을 이용합시다.

https://programmers.co.kr/learn/courses/30/lessons/59414

코딩테스트 연습 - DATETIME에서 DATE로 형 변환

문제는 잊어버리고 TABLE만 이용합시다.

ANIMAL_OUTS테이블에서 월별로, ANIMAL_TYPE별로 묶고 카운트를 하고,

그룹 중 가장 큰 값만 선택하여 출력해 봅시다.

출력은 아래의 형식으로 해봅시다. 

MONTH,  ANIMAL_TYPE, COUNT

 

먼저 GROUP BY는 1개 이상의 조건으로 묶을 수 있습니다.

MONTH(DATETIME)과 ANIMAL_TYPE으로 묶어보면,

월과 TYPE의 조합에 대해 ROW가 생성된다.

COUNT() 함수를 이용하여 수를 세보면 월과 TYPE의 조합으로 묶은 성분의 수가 나온다.

SELECT MONTH(DATETIME) AS 'M',ANIMAL_TYPE, COUNT(MONTH(DATETIME)) AS 'C' 
FROM ANIMAL_INS
GROUP BY  M, ANIMAL_TYPE
ORDER BY  M, ANIMAL_TYPE

여기서 M에 대하여 GROUP BY 하고 MAX() 값을 추출하면 끝입니다.

코드는 아래와 같습니다.

저의 경우 뇌 정지가 와서 WITH로 분리했습니다.

-- 코드를 입력하세요
WITH A AS (
SELECT MONTH(DATETIME) AS 'M',ANIMAL_TYPE, COUNT(MONTH(DATETIME)) AS 'C' 
FROM ANIMAL_INS
GROUP BY  M, ANIMAL_TYPE
ORDER BY  M, ANIMAL_TYPE 
)


SELECT M, ANIMAL_TYPE, MAX(C) FROM A
GROUP BY M

GROUP BY 할 조건이 2개이고 COUNT, MAX를 처리해야 한다.

그래서 GROUP BY당 1개씩 조건을 분배해서 구현하였다.

정리

테스트 당시 틀렸던 문법이 정확하게는 모르겠지만,

with as를 사용하면 간결하게 생각을 정리할 수 있다는 사실을 알았다.

다음에는 틀리지 말자.

서브쿼리를 사용하면 좋은데 뇌정지가 올수 도 있습니다. 뇌정지가 올떄는 WITH AS구문을 이용하여 독립적으로 생각합시다.  GROUP BY는 2가지 이상의 정보를 묶을수 있으니 이 점도 꼭 기억합시다.