[DB/MYSQL] SQL 고득점 Kit - SELECT문(Lv.3~5)

해당 글은 프로그래머스의 'SQL 고득점 Kit'을 풀고 작성한 글입니다. 

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

프로그래머스

 

SELECT > 오프라인/온라인 판매 데이터 통합하기 (Lv.4)

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

 

문제) ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

SELECT 
    DATE_FORMAT(N.SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    N.PRODUCT_ID, 
    N.USER_ID, N.SALES_AMOUNT
FROM ONLINE_SALE N
WHERE N.SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'

UNION ALL # 합집합

SELECT 
    DATE_FORMAT(F.SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    F.PRODUCT_ID, 
    NULL AS USER_ID, # NULL로 나타내기. 다른 걸로 나타낼때도 같은 방법 사용.
    F.SALES_AMOUNT
FROM OFFLINE_SALE F
WHERE F.SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'

ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC # ORDER BY는 한번!

합집합
- UNION ALL: 중복 포함한 결과 반환
- UNION: 중복 제거한 결과 반환

교집합
- INTERSECT
MYSQL에서는 INTERSECT가 없으므로 INNER JOIN 혹은 IN을 사용해야 함!!

차집합
- EXCEPT
MYSQL에서는 EXCEPT가 없으므로 LEFT JOIN + WHERE NULL을 사용해야 함!!

 

 

SELECT > 대장균들의 자식의 수 구하기 (Lv.3)

https://school.programmers.co.kr/learn/courses/30/lessons/299305

프로그래머스

 

ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE,  GENOTYPE은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

문제) 대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

SELECT P.ID AS ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA P LEFT JOIN ECOLI_DATA C ON P.ID = C.PARENT_ID
GROUP BY P.ID
ORDER BY ID

 

SELECT > 대장균 크기에 따라 분류하기1 (Lv.3)

https://school.programmers.co.kr/learn/courses/30/lessons/299307

프로그래머스

 

문제) 대장균 개체의 크기가 100 이하라면 'LOW', 100 초과 1000 이하라면 'MEDIUM', 1000 초과라면 'HIGH' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류(SIZE)를 출력하는 SQL 문을 작성해주세요.이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

SELECT ID, 
    CASE # if else 처럼 위에서부터 적용됨. 
        WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
        WHEN SIZE_OF_COLONY < 1000 THEN 'MEDIUM'
        ELSE 'HIGH'
    END AS SIZE # 별칭 설정
FROM ECOLI_DATA
ORDER BY ID

 

 

SELECT > 대장균 크기에 따라 분류하기2 (Lv.3)

https://school.programmers.co.kr/learn/courses/30/lessons/301649

프로그래머스

 

문제) 대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

SELECT ID, 
    CASE NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) # NTILE(N) 사용해서 그룹화
        WHEN 1 THEN 'CRITICAL'
        WHEN 2 THEN 'HIGH'
        WHEN 3 THEN 'MEDIUM'
        WHEN 4 THEN 'LOW'
    END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID ASC

NTILE(N)은 결과를 N개의 동일한 그룹으로 나눠서 각각의 행에 그룹 번호를 매겨준다. 

 

SELECT ID, 
    # ROUND(PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC), 2) AS COLONY_NAME,
    CASE # PERCENT_RANK()으로 백분율 계산
        WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.25 THEN 'CRITICAL'
        WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.50 THEN 'HIGH'
        WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID ASC

PERCENT_RANK()는 데이터를 정렬 후 각 행에 대해 상대적 순위를 0~1 사이의 백분율로 계산해준다.
-> 정확한 비율 설정할 수 있다

 

 

SELECT > 특정 세대의 대장균 찾기 (Lv.4)

https://school.programmers.co.kr/learn/courses/30/lessons/301650

프로그래머스

 

문제) 3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

SELECT ID
FROM ECOLI_DATA
WHERE ID IN (
    SELECT CC.ID 
    FROM ECOLI_DATA P JOIN ECOLI_DATA C ON P.ID = C.PARENT_ID JOIN ECOLI_DATA CC ON C.ID = CC.PARENT_ID
    WHERE P.PARENT_ID IS NULL
)
ORDER BY ID ASC

 

 

SELECT > 멸종 위기의 대장균 찾기 (Lv.5)

https://school.programmers.co.kr/learn/courses/30/lessons/301651

프로그래머스

 

문제) 각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

WITH RECURSIVE ANCESTOR AS ( # CTE(Common Table Expression) 생성

    SELECT ID, PARENT_ID, 1 AS GENERATION 
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL # 부모가 없으면 1세대
    
    UNION # 중복되는 행 자동 제거
    
    SELECT C.ID, C.PARENT_ID, (A.GENERATION + 1) AS GENERATION # 세대 설정
    FROM ECOLI_DATA C, ANCESTOR A
    WHERE C.PARENT_ID = A.ID # C는 A의 자식
)

SELECT COUNT(*) AS COUNT, GENERATION
FROM ANCESTOR
WHERE ID NOT IN (SELECT PARENT_ID FROM ANCESTOR WHERE PARENT_ID IS NOT NULL) # 부모가 있는 것 제거
GROUP BY GENERATION # 집계함수 위한 GROUPING
ORDER BY GENERATION

 

최상위 조상에서부터 얼마나 떨어져 있는지를 뜻하는 세대(GENERATION)를 구하는 게 핵심이다. 
루트에서 시작해 자식으로 내려갈 때마다 1씩 증가하는 숫자로 표현해야 하는데, 이는 재귀 쿼리로 구할 수 있다. 

재귀 쿼리로 생성된 CTE(Common Table Expression)는 쿼리 내부에서 임시로 생성된 테이블이다. 그래서 쿼리가 실행될 때만 임시로 생성되었다가 쿼리가 끝나면 사라지는 특성을 지닌다.