[프로그래머스 SQL 고득점 Kit] SELECT

 

서울에 위치한 식당 목록 출력하기

다음은 식당의 정보를 담은 REST_INFO 테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. REST_INFO 테이블은 다음과 같으며 REST_ID, REST_NAME, FOOD_TYPE, VIEWS, FAVORITES, PARKING_LOT, ADDRESS, TEL은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미합니다.

 

REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

내가 푼 코드

-- REST_INFO와 REST_REVIEW 테이블에서 '서울에 위치'한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문
-- 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림
-- 결과는 평균점수를 기준으로 내림차순 정렬, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬
SELECT i.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS,
ROUND(AVG(REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO i
JOIN REST_REVIEW r ON  i.REST_ID=r.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY i.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

 

 

오프라인/온라인 판매 데이터 통합하기

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다. ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

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

어떻게 풀어야 할지 감이 안 잡혀서 답안을 봤다.

https://school.programmers.co.kr/questions/75879

각 테이블에서 조건에 맞는 데이터를 찾은 후 UNION해주면 되는 것 같다.

-- ONLINE_SALE 테이블과 OFFLINE_SALE 테이블
-- "2022년 3월"의 오프라인/온라인 상품 판매 데이터
-- 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문
-- OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
-- 판매일 오름차순, 판매일이 같다면 상품 ID 오름차순, 상품ID까지 같다면 유저 ID 오름차순
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 1, 2, 3

 

NULL AS 컬럼명을 사용하여 USER_ID를 NULL로 넣는 게 인상적이었다.

 

 

특정 물고기를 잡은 총 수 구하기

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. FISH_INFO 테이블의 구조는 다음과 같으며 ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다. 단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다. FISH_NAME_INFO 테이블의 구조는 다음과 같으며, FISH_TYPE, FISH_NAME 은 각각 물고기의 종류(숫자), 물고기의 이름(문자) 입니다.

FISH_INFO 테이블에서 잡은 BASS와 SNAPPER의 수를 출력하는 SQL 문을 작성해주세요. 컬럼명은 'FISH_COUNT`로 해주세요.

내가 푼 코드

-- FISH_INFO 테이블에서 잡은 BASS와 SNAPPER의 수를 출력하는 SQL 문을 작성해주세요.
-- 컬럼명은 'FISH_COUNT`로 해주세요.
SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO a
JOIN FISH_NAME_INFO b ON a.FISH_TYPE=b.FISH_TYPE
WHERE b.FISH_NAME IN ('BASS', 'SNAPPER')

 

대장균들의 자식의 수 구하기

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며,  ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다. 최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

정답 코드

-- 대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문
-- 자식이 없다면 자식의 수는 0으로 출력
-- 결과는 개체의 ID 에 대해 오름차순 정렬
SELECT a.ID, COUNT(b.PARENT_ID) AS CHILD_COUNT
FROM ECOLI_DATA a
LEFT JOIN ECOLI_DATA b ON a.ID=b.PARENT_ID
GROUP BY a.ID

 

틀린 코드

자식이 없는 자식의 수를 어떻게 0으로 출력해야하는지 몰라서 못 풀었다.

-- 대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문
-- 자식이 없다면 자식의 수는 0으로 출력
-- 결과는 개체의 ID 에 대해 오름차순 정렬
SELECT PARENT_ID AS ID, COUNT(*) AS  CHILD_COUNT
FROM ECOLI_DATA
GROUP BY PARENT_ID
HAVING PARENT_ID IS NOT NULL

 

LEFT JOIN을 써서 풀어야 한다고 해서 LEFT JOIN을 한 테이블을 살펴봄

SELECT *
FROM ECOLI_DATA a
LEFT JOIN ECOLI_DATA b ON a.ID=b.PARENT_ID

정답 도출

-- 대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문
-- 자식이 없다면 자식의 수는 0으로 출력
-- 결과는 개체의 ID 에 대해 오름차순 정렬
SELECT a.ID, COUNT(b.PARENT_ID) AS CHILD_COUNT
FROM ECOLI_DATA a
LEFT JOIN ECOLI_DATA b ON a.ID=b.PARENT_ID
GROUP BY a.ID

 

부모의 형질을 모두 가지는 대장균 찾기

 

정답 코드

2진수로 바꿔서 하나씩 비교해야 한다고 생각했는데, 비트연산자 &를 사용해서 따로 변환없이 부모의 형질을 모두 가지는지 판단이 가능했음.

-- 부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력
-- 결과는 ID에 대해 오름차순 정렬해주세요.
SELECT a.ID, a.GENOTYPE, b.PARENT_GENOTYPE
FROM ECOLI_DATA AS a
    LEFT JOIN
    (SELECT ID, GENOTYPE AS PARENT_GENOTYPE FROM ECOLI_DATA) AS b
    ON a.PARENT_ID = b.ID
WHERE a.GENOTYPE & b.PARENT_GENOTYPE = b.PARENT_GENOTYPE
ORDER BY ID ASC;

 

https://school.programmers.co.kr/questions/81218

대장균들의 자식 수 구하기랑 비슷한 문제라고 느껴져서 LEFT JOIN을 해서 결과를 봤다

-- 부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력
-- 결과는 ID에 대해 오름차순 정렬해주세요.
SELECT *
FROM ECOLI_DATA a
LEFT JOIN ECOLI_DATA b ON a.ID=b.PARENT_ID
ORDER BY a.ID

 

대장균의 크기에 따라 분류하기 2

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며,  ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다. 최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

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

정답 코드

-- 대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류
-- 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력
-- 결과는 개체의 ID 에 대해 오름차순 정렬
SELECT ID,
case
    when PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) * 100 <=25
    then 'CRITICAL'
     when PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) * 100 <=50
    then 'HIGH'
    when PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) * 100 <=75
    then 'MEDIUM'
    else 'LOW'
end AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID

 

문제를 풀기 위해 알아야 하는 함수!

[MySQL] RANK(), PERCENT_RANK()

RANK()를 사용하면 전체 순위를 기반으로 퍼센트를 수동으로 계산
PERCENT_RANK()를 사용하면 퍼센트를 바로 계산

 

특정 세대의 대장균 찾기

 

정답 코드

-- 3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요.
-- 결과는 대장균의 ID 에 대해 오름차순 정렬
SELECT c.ID
FROM ECOLI_DATA c
JOIN ECOLI_DATA p ON c.PARENT_ID=p.ID
JOIN ECOLI_DATA g ON p.PARENT_ID=g.ID
WHERE g.PARENT_ID IS NULL
ORDER BY c.ID

연결을 제대로 하는 게 핵심인 거 같다. LEFT JOIN과 그냥 JOIN의 차이를 잘 모르는 것 같음.