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

1. GROUP BY - [물고기 종류 별 잡은 수 구하기] _ LEVEL 2

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

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

FISH_NAME_INFO에서 물고기의 종류 별 물고기의 이름과 잡은 수를 출력하는 SQL문을 작성해주세요.

물고기의 이름 컬럼명은 FISH_NAME, 잡은 수 컬럼명은 FISH_COUNT로 해주세요.

결과는 잡은 수 기준으로 내림차순 정렬해주세요.

[ 정답 쿼리 ]

SELECT 
    COUNT(*) fish_count
    , fish_name
FROM fish_info
JOIN fish_name_info 
USING (fish_type)
GROUP BY 2
ORDER BY 1 DESC;

 

[ 풀이 ]

• USING 사용해 두 테이블 조인
  • JOIN되는 키의 필드 명이 같아야하고
  • 문법상 USING () ← 괄호를 빼먹으면 안됨

---

2. GROUP BY - [월별 잡은 물고기 수 구하기] _ LEVEL 2

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

월별 잡은 물고기의 수와 월을 출력하는 SQL문을 작성해주세요.

잡은 물고기 수 컬럼명은 FISH_COUNT, 월 컬럼명은 MONTH로 해주세요.

결과는 월을 기준으로 오름차순 정렬해주세요.

단, 월은 숫자형태 (1~12) 로 출력하며 9 이하의 숫자는 두 자리로 출력하지 않습니다. 잡은 물고기가 없는 월은 출력하지 않습니다.

 

[ 정답 쿼리 ]

SELECT 
    COUNT(*) fish_count
    , MONTH(time) month
FROM fish_info
GROUP BY 2
ORDER BY 2;

---

3. GROUP BY - [특정 조건을 만족하는 물고기별 수와 최대 길이 구하기] _ LEVEL 3

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

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

FISH_INFO에서 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여 잡은 수, 최대 길이, 물고기의 종류를 출력하는 SQL문을 작성해주세요.

결과는 물고기 종류에 대해 오름차순으로 정렬해주시고, 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해주세요.

컬럼명은 물고기의 종류 'FISH_TYPE', 잡은 수 'FISH_COUNT', 최대 길이 'MAX_LENGTH'로 해주세요.

 

[ 정답 쿼리 ]

SELECT 
    COUNT(*) fish_count
    , MAX(length) max_length
    , fish_type
FROM fish_info
GROUP BY fish_type
HAVING AVG(COALESCE(length,10)) >= 33
ORDER BY 3;

 

[ 풀이 ]

• 평균 길이가 33cm 이상인 물고기들을 종류별로 분류 : GROUP BY 물고기 종류 HAVING 평균(물고기 길이)
• 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL이지만, 문제에서 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해달라고 했으므로 COALESCE() 함수 사용
  • COALESCE() : 처음으로 NULL이 아닌 값 반환

---

4. String, Date - [분기별 분화된 대장균의 개체 수 구하기] _ LEVEL 2

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

각 분기(QUARTER)별 분화된 대장균의 개체의 총 수(ECOLI_COUNT)를 출력하는 SQL 문을 작성해주세요.

이때 각 분기에는 'Q' 를 붙이고 분기에 대해 오름차순으로 정렬해주세요. 대장균 개체가 분화되지 않은 분기는 없습니다.

 

[ 정답 쿼리 ]

-- 풀이 1. MONTH, CASE WHEN 사용
SELECT 
    CASE WHEN MONTH(differentiation_date) <= 3 THEN '1Q'
        WHEN MONTH(differentiation_date) <= 6 THEN '2Q'
        WHEN MONTH(differentiation_date) <= 9 THEN '3Q'
        ELSE '4Q' END AS quarter
    , COUNT(id) ecoli_count
FROM ecoli_data
GROUP BY 1
ORDER BY 1;

-- 풀이 2. CEIL, 나누기 연산 사용
SELECT
    CONCAT(CEIL(MONTH(DIFFERENTIATION_DATE) / 3), 'Q') QUARTER
    , COUNT(ID) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY 1
ORDER BY 1;

-- 풀이 3. QUARTER() 함수 사용
SELECT
    CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') QUARTER
    , COUNT(ID) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY 1
ORDER BY 1;

 

[ 풀이 ]

 

풀이 2) CEIL, 나누기 연산 사용

• 1, 2, 3 : CEIL(1/3) = CEIL(2/3) = CEIL(3/3) = 1
• 4, 5, 6 : CEIL(4/3) = CEIL(5/3) = CEIL(6/3) = 2
• 4, 5, 6 : CEIL(7/3) = CEIL(8/3) = CEIL(9/3) = 3
• 4, 5, 6 : CEIL(10/3) = CEIL(11/3) = CEIL(12/3) = 4

풀이 3) QUARTER() 함수 사용

• QUARTER(DATE) : 입력 날짜의 분기를 출력
• EX. QUARTER(2020-12-31) = 4