[PS] 코딩테스트 SQL 정리

 

SQL 코딩테스트를 준비하며 학습한 내용들을 정리한 글이다.

---

SQL 쿼리문 순서

우선 쿼리 문법의 작성 순서와 실행 순서는 다음과 같다.

작성 순서

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY

실행 순서

반면 실제 실행 순서는 작성 순서와는 조금 다르다.

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

작성 순서와 실행 순서가 다름에 따라 주의해야 할 것은 ALIAS를 사용할 때다.

 

두 경우를 살펴봤을 때, 좌측 쿼리문은 문제 없이 정상적으로 동작한다. ORDER BY가 SELECT문 뒤에 실행되기 때문이다. 하지만 우측 쿼리문의 경우 오류가 발생한다. WHERE문이 SELECT문보다 먼저 실행되어 WHERE문이 실행될 당시에는 ID라고 하는 ALIAS를 인식할 수 없기 때문이다.

그래서 항상 작성 순서는 물론이고 실행 순서를 고려하며 쿼리문을 작성해야 한다.

 

 

SELECT

DATE_FORMAT

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE) = '2021'

24-12-21, 24년 12월 21일, 2024-12-21 10시 40분 23초 등 다양한 날짜 형식을 만들 수 있다.

 

DATEDIFF

SELECT HISTORY_ID, 
    CAR_ID, 
    DATE_FORMAT(START_DATE, "%Y-%m-%d") AS START_DATE, 
    DATE_FORMAT(END_DATE, "%Y-%m-%d") AS END_DATE,
    IF(DATEDIFF(END_DATE, START_DATE) >= 29, "장기 대여", "단기 대여") AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE "2022-09%"
ORDER BY HISTORY_ID DESC

두 날짜의 차이를 출력해준다. 다만 주의해야 할 점이 해당 기간이 며칠인지를 알고 싶다면 +1을 해주어야 한다. 즉 DATEDIFF의 출력값은 1이지만 사실상 이틀이다. 이러한 미묘한 차이때문에 틀리는 경우가 잦았다.

 

SUM, AVG, MAX, MIN

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

해당 컬럼에 존재하는 값들의 합, 평균값 또는 최대/최솟값을 골라서 보여준다. 주의해야할 점은 해당 컬럼의 최댓값을 보여준다고 해서 최댓값을 가지고 있는 행이 출력되는 것은 아니다.

 

IF, CASE

SELECT ANIMAL_ID, NAME, 
IF(SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%', 'O', 'X') AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN STATUS = 'SALE' THEN '판매중'
     WHEN STATUS = 'RESERVED' THEN '예약중'
     WHEN STATUS = 'DONE' THEN '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05%'
ORDER BY BOARD_ID DESC

위와 같이 조건문에 대해 사용할 수 있다. 유사 함수로 IFNULL, COALESCE 등이 있다.

 

DISTINCT

SELECT COUNT(DISTINCT NAME) count
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

DISTINCT는 중복된 결과를 제거하는 데에 사용된다.

 

ROUND, TRUNCATE

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*)
FROM PRODUCT
GROUP BY TRUNCATE(PRICE, -4)
ORDER BY PRICE_GROUP

ROUND는 반올림, TRUNCATE는 절삭(내림) 함수를 나타낸다. 둘 다 인자값으로 (대상 값, 자릿수)를 필요로 한다.

 

CONCAT

SELECT CONCAT(MAX(LENGTH), "cm") AS MAX_LENGTH
FROM FISH_INFO

CONCAT 함수는 SELECT문에서 문자열을 합칠 때 사용한다.

 

SUBSTR, LEFT, RIGHT

SELECT U.USER_ID, U.NICKNAME, 
    CONCAT(U.CITY, " ", U.STREET_ADDRESS1, " ", STREET_ADDRESS2) AS 전체주소, 
    CONCAT(LEFT(U.TLNO, 3), "-", SUBSTRING(U.TLNO, 4, 4), "-", RIGHT(U.TLNO, 4)) AS 전화번호
FROM USED_GOODS_BOARD AS B
JOIN USED_GOODS_USER AS U
ON B.WRITER_ID = U.USER_ID
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3
ORDER BY U.USER_ID DESC

세 함수 모두 문자열을 파싱하는 용도로 사용한다. SUBSTR는 문자열 인덱스 시작점과 끝점을 모두 필요로 하고, LEFT와 RIGHT는 끝점 혹은 시작점만 필요로 한다.

 

 

WHERE

LIKE

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'dog' AND NAME LIKE '%EL%'
ORDER BY NAME

LIKE문은 해당 문자열의 포함여부를 판단할 수 있다. 문자열 양 옆에 '%'를 넣을 수 있고, 와일드카드 역할을 한다.

 

JOIN

SELECT B.TITLE, 
    B.BOARD_ID, 
    R.REPLY_ID, 
    R.WRITER_ID, 
    R.CONTENTS CONTENTS, 
DATE_FORMAT(R.CREATED_DATE, '%Y-%m-%d') CREATED_DATE
FROM USED_GOODS_BOARD AS B
JOIN USED_GOODS_REPLY AS R ON B.BOARD_ID = R.BOARD_ID
WHERE YEAR(B.CREATED_DATE) = '2022' AND MONTH(B.CREATED_DATE) = '10'
ORDER BY R.CREATED_DATE, B.TITLE

JOIN은 다른 테이블과의 매핑이 필요할 때 사용한다. JOIN의 종류에는 INNER JOIN(JOIN), LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN 등이 있다. FULL OUTER JOIN의 경우 MySQL에서 지원하지 않기 때문에 LEFT JOIN과 RIGHT JOIN을 함께하여 동일한 결과를 만들 수 있다.

 

 

GROUP BY

HAVING

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

GROUP BY는 특정 컬럼을 그룹화할 때 사용한다. 이때 HAVING절을 통해 각 그룹의 조건 설정이 가능하다.

 

 

ORDER BY

LIMIT

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS AS I
WHERE I.ANIMAL_ID NOT IN (SELECT O.ANIMAL_ID FROM ANIMAL_OUTS AS O)
ORDER BY I.DATETIME
LIMIT 3

ORDER BY는 특정 컬럼을 기준으로 오름차순(ASC), 내림차순(DESC) 정렬한다.

LIMIT문을 통해 출력되는 데이터의 개수를 제한할 수 있다.

 

 

WITH

WITH절

WITH BT AS (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD AS B
    ORDER BY VIEWS DESC
    LIMIT 1
)

SELECT CONCAT("/home/grep/src/", F.BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE AS F
WHERE F.BOARD_ID = (SELECT BOARD_ID FROM BT)
ORDER BY FILE_ID DESC

WITH는 임시 테이블을 생성하는 명령어다. 특히 동일한 SQL이 반복되어 사용될 때 성능을 개선시키기 위해 사용된다. 물리적인 테이블을 만들지 않고도 테이블을 만드는 것과 같은 효과를 낸다. 물론 임시 테이블도 메모리를 차지한다.

 

WITH RECURSIVE 절

WITH RECURSIVE CTE AS (
    SELECT 0 AS NUM
    UNION
    SELECT NUM + 1
    FROM CTE
    WHERE NUM < 23
)

SELECT CTE.NUM AS HOUR, COUNT(HOUR(O.DATETIME)) AS COUNT
FROM ANIMAL_OUTS AS O
RIGHT JOIN CTE
ON HOUR(O.DATETIME) = CTE.NUM
GROUP BY CTE.NUM
ORDER BY CTE.NUM

WITH RECURSIVE는 재귀 쿼리 기법으로, 반복적으로 데이터를 생성할 때 사용한다. 반드시 UNION을 사용해야 하고, 반복되기 때문에 정지 조건 또한 필수이다.

가상의 테이블을 구성하면서 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용하다.

 

 

UNION

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE "2022-03%"
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE "2022-03%"
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

두 개의 테이블에서 조회한 결과를 합칠 때 사용한다. UNION ALL은 중복값을 허용하고, UNION은 중복값을 허용하지 않는다.

위 예시에서 마지막의 ORDER BY는 아래의 쿼리문 결괏값에 적용되는 것이 아니라 UNION된 결괏값에 적용된다. 만약 각자의 쿼리문 결괏값에 적용시키고 싶다면 서브 쿼리를 이용하면 된다.