[프로그래머스] 9주차 MySQL 스터디 2

1. SELECT - [특정 물고기를 잡은 총 수 구하기] _ LEVEL 2

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다.

FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다.

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

컬럼명은 'FISH_COUNT`로 해주세요.

 

[ 정답 쿼리 ]

SELECT COUNT(*) FISH_COUNT
FROM fish_info
WHERE fish_type IN (
    SELECT fish_type
    FROM fish_name_info
    WHERE fish_name IN ('BASS','SNAPPER')
    )
;

 

[ 풀이 ]

• 서브쿼리를 활용하여 두 테이블에 있는 정보를 연결

---

🌟🌟 2. SELECT - [대장균들의 자식의 수 구하기] _ LEVEL 3

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다.

대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요.

자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT
    PARENT.ID,
    COUNT(CHILD.ID) AS CHILD_COUNT
FROM
    ECOLI_DATA AS PARENT
LEFT OUTER JOIN
    ECOLI_DATA AS CHILD ON PARENT.ID = CHILD.PARENT_ID
GROUP BY
    PARENT.ID
ORDER BY
    PARENT.ID​

 

[ 풀이 ]

• 자식이 없는 경우까지 포함하기 위해 inner join이 아닌 outer join을 수행
• 이 경우 자식이 없는 row의 CHILD.ID는 NULL임
• 따라서 COUNT(CHILD.ID)로 명확하게 개수를 셀 column을 지정해주면 CHILD.ID의 값이 NULL이 아닌 row의 수만 세게 되어 원하는 답이 됨

---

좌측이 PARENT, 우측이 CHILD

SELECT
    PARENT.ID,
    CHILD.ID
FROM
    ECOLI_DATA AS PARENT
LEFT OUTER JOIN
    ECOLI_DATA AS CHILD ON PARENT.ID = CHILD.PARENT_ID;

• LEFT SELF-JOIN 이 헷갈리기 때문에 먼저 JOIN 후 PARENT.ID, CHILD.ID를 각각 찍어서 확인
  • GROUP BY, ORDER BY 의 기준은 부모가 되고, CHILD_COUNT 필드를 구성하는 집계 함수 COUNT()의 대상은 자식이 된다

---

3. SELECT - [대장균의 크기에 따라 분류하기 1] _ LEVEL 3

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다.

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

 

[ 정답 쿼리 ]

SELECT 
    id
    , CASE 
        WHEN size_of_colony <=100 THEN 'LOW' 
        WHEN size_of_colony <=1000 THEN 'MEDIUM' 
        WHEN size_of_colony > 1000 THEN 'HIGH' 
        END AS size
FROM ecoli_data
ORDER BY 1;

---

🌟🌟 4. GROUP BY - [가격대 별 상품 개수 구하기] _ LEVEL 2

다음은 어느 의류 쇼핑몰에서 판매중인 상품들의 정보를 담은 PRODUCT 테이블입니다.

PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요.

이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요.

결과는 가격대를 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

-- 풀이 1. CASE WHEN을 이용한 하드코딩
SELECT 
    CASE WHEN price < 10000 THEN 0
        WHEN price < 20000 THEN 10000
        WHEN price < 30000 THEN 20000
        WHEN price < 40000 THEN 30000
        WHEN price < 50000 THEN 40000
        WHEN price < 60000 THEN 50000
        WHEN price < 70000 THEN 60000
        WHEN price < 80000 THEN 70000
        WHEN price < 90000 THEN 80000
        END AS price_group
    , COUNT(*) AS products
FROM product
GROUP BY 1
ORDER BY 1;

-- 풀이 2. DIV
SELECT 
    (price DIV 10000) * 10000 as PRICE_GROUP
    , count(*) as PRODUCTS
from product
group by PRICE_GROUP
order by PRICE_GROUP;


-- 풀이 3. FLOOR
SELECT 
    FLOOR(price/10000)*10000 as PRICE_GROUP
    , count(*) as PRODUCTS
from product
group by PRICE_GROUP
order by PRICE_GROUP;

 

[ 풀이 ]

 

풀이 1) 처음에는 만원 단위의 구간으로 나누는 방법이 생각나지 않아서 하드코딩으로 문제 해결

   → 이 방법은 데이터가 바뀔 때마다 CASE WHEN 문의 개수가 바뀌어야 한다는 치명적 단점 존재

(EX. 해당 데이터에 갑자기 20만원짜리 물품이 추가되면 해당 물품은 위의 쿼리문으로 처리하지 못하게 됨)

---

 

풀이 2) 같은 간격 or 비율의 구간으로 나누기 → 간단한 수학적 성질을 이용해 필드 구성하기!

• 10,000원 미만 / 20,000원 미만 / 30,000원 미만 .. → 10,000으로 나눴을 때 몫이 동일
  1. (price DIV 10000) * 10000
  2. FLOOR(price/10000) * 10000
  • 위의 두 방식 모두 같은 결과. 구체적으로 DIV 함수는 결과가 bigINT값으로 비교적 안전

---

5. String, Date - [카테고리 별 상품 개수 구하기] _ LEVEL 2

다음은 어느 의류 쇼핑몰에서 판매중인 상품들의 정보를 담은 PRODUCT 테이블입니다.

상품 별로 중복되지 않는 8자리 상품코드 값을 가지며, 앞 2자리는 카테고리 코드를 의미합니다.

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    LEFT(product_code,2) category
    , COUNT(*) products
FROM product
GROUP BY 1
ORDER BY 1;