[MySQL] 프로그래머스 SQL 고득점 Kit - GROUP BY

 

생각보다 양이 많네.....

 

https://school.programmers.co.kr/learn/courses/30/parts/17044

프로그래머스

 

 

---

1. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT 
    DATE_FORMAT(START_DATE, '%m') AS MONTH,
    CAR_ID,
    COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
AND CAR_ID IN (
    -- 5회 이상 대여된 자동차 ID만 필터링
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;

• START_DATE를 기준으로 구하고 있으므로 WHERE 조건 절에 START_DATE를 기준으로 날짜 사이에 대여한 것들을 필터링한다. 또한 CAR_ID를 기준으로 그룹화 하여 HAVING 조건을 붙인다.
• 이렇게 필터링 된 정보들을 MONTH와 CAR_ID로 GROUP BY 한다. 
  • GROUP BY에 데이터를 두 개 이상 넣는 경우 MONTH 그룹화 후 그 안에서 CAR_ID로 그룹화하게 된다. 
  • 비집계함수인 경우, 반드시 GROUP BY에 병시된 칼럼만 SELECT에서 사용할 수 있다! ( sql_mode=only_full_group_by 오류)

 

2. 자동차 대여 기록에서 대여중/대여 가능 여부 구분하기

SELECT 
    CAR_ID, 
    CASE 
        WHEN MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0 END) = 1 
        THEN '대여중' 
        ELSE '대여 가능' 
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

• GROUP BY로 묶은 후 조건 검사 시 GROUP 하나로 묶인 모든 데이터를 검사한다.
• 단, 하나라도 대여중인 결과가 나오면 대여중으로 표시해야 하므로 MAX() 를 이용해 검사한다.

 

3. 고양이와 개는 몇 마리 있을까

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(*) AS count FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE;

• CAT을 먼저 오도록 정렬하기 위해 알파벳순으로 ORDER BY했다. 만약 데이터 순서를 명시적으로 작성하고 싶으면 ORDER BY FIELD(ANIMAL_TYPE, 'Dog', 'Cat', 'Rabbit', 'Other');  와 같이 FIELD() 함수 이용하기

 

4. 동명 동물 수 찾기

-- 코드를 입력하세요
SELECT NAME, COUNT(*) AS COUNT FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME HAVING COUNT(*)>=2 ORDER BY NAME;

 

 

 

5. 년, 월, 성별 별 구매 회원 수 구하기

-- 코드를 입력하세요
SELECT YEAR(OS.SALES_DATE) AS YEAR, MONTH(OS.SALES_DATE) AS MONTH, UI.GENDER, COUNT(DISTINCT OS.USER_ID) AS USERS FROM USER_INFO UI JOIN ONLINE_SALE OS ON UI.USER_ID=OS.USER_ID 
WHERE GENDER IS NOT NULL
GROUP BY YEAR(OS.SALES_DATE), MONTH(OS.SALES_DATE), UI.GENDER
ORDER BY YEAR(OS.SALES_DATE), MONTH(OS.SALES_DATE), UI.GENDER;

• 문제 주의!! 총 구매수가 아닌 회원의 수를 구해야 한다. 따라서 USER_ID를 DISTINCT 한 갯수를 SELECT 해야 한다.

 

6. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

-- 코드를 입력하세요
SELECT CAR_TYPE, COUNT(*) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE "%가죽시트%" OR OPTIONS LIKE "%열선시트%" OR OPTIONS LIKE "%통풍시트%" GROUP BY CAR_TYPE ORDER BY CAR_TYPE;

• WHERE 절이 LIKE ~ OR을 여러개 쓰면서 지저분해졌다. 그러나 LIKE 조건을 IN으로는 표현할 수 없음.

SELECT CAR_TYPE, COUNT(*) AS CARS 
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS REGEXP '가죽시트|열선시트|통풍시트' 
GROUP BY CAR_TYPE 
ORDER BY CAR_TYPE;

• 정규표현식 REGEXP 을 이용한 방식이다.

 

7. 성분으로 구분한 아이스크림 총 주문량

-- 코드를 입력하세요
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF AS FH JOIN ICECREAM_INFO AS II ON FH.FLAVOR=II.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;

• 정렬은 SELECT 절에 명시된 별칭으로도 가능하다.

 

8. 진료과별 총 예약 횟수 출력하기

-- 코드를 입력하세요
SELECT MCDP_CD AS "진료과코드", COUNT(*) AS "5월예약건수" FROM APPOINTMENT WHERE APNT_YMD BETWEEN "2022-05-01" AND "2022-05-31" 
GROUP BY MCDP_CD ORDER BY 2, 1;

• DATE TYPE이랑 문자열 비교 안됨. BETWEEN 이용하기.
• 정렬은 SELECT 절에 명시된 컬럼 순서 숫자로도 가능

 

9. 입양 시각 구하기(1)

-- 코드를 입력하세요
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) BETWEEN "9" AND "19" GROUP BY HOUR(DATETIME) ORDER BY 1;

• HOUR() 함수 사용!

 

10. 입양 시각 구하기(2)

WITH RECURSIVE TIME AS(
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
        FROM TIME
        WHERE HOUR < 23
)
SELECT T.HOUR, COUNT(ANIMAL_ID) AS COUNT
    FROM TIME T
    LEFT JOIN (SELECT *, HOUR(DATETIME) AS HOUR
            FROM ANIMAL_OUTS) O
      ON T.HOUR = O.HOUR
    GROUP BY 1
    ORDER BY 1;

• ANIMAL_OUTS TABLE에 모든 시간대 정보가 없으므로, 0부터 23까지의 시간대 정보가 담긴 TABLE(혹은 쿼리)과 LEFT JOIN을 수행해야 한다.
• 재귀를 수행하지 않고 SELECT 0 AS HOUR UNION ALL ~~~ 이런식으로 모든 시간대를 직접 입력할 수 있지만, 코드가 못생겨지므로 안했다.
• TIME CTE를 수행하여 HOUR 컬럼의 값을 0부터 23까지 생성한다.이후 TIME CTE와 ANIMAl_OUTS를 LEFT JOIN하여 수행

 

 

11. 가격대 별 상품 개수 구하기

SELECT 
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP,
    COUNT(*) AS PRODUCTS
FROM PRODUCT
WHERE PRICE >= 10000
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

• FLOOR() 또는 DIV 연산자를 이용해 PRICE를 10000원대별로 구간을 나눈 후 그룹화하여 SELECT 한다.

 

 

12. 조건에 맞는 사원 정보 조회하기

-- 코드를 작성해주세요
SELECT SUM(SCORE) AS SCORE, HE.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL 
FROM HR_DEPARTMENT HD
JOIN HR_EMPLOYEES HE ON HD.DEPT_ID = HE.DEPT_ID
JOIN HR_GRADE HG ON HE.EMP_NO = HG.EMP_NO
WHERE HG.YEAR="2022"
GROUP BY 2
ORDER BY 1 DESC
LIMIT 1;

• 3개 이상의 테이블을 JOIN하는 방식에 주목

 

13. 연간 평가 점수에 해당하는 평가 등급 및 성과금 조회하기

-- 코드를 작성해주세요
SELECT HE.EMP_NO, HE.EMP_NAME, 
    CASE WHEN AVG(HG.SCORE) >= 96 THEN "S"
    WHEN AVG(HG.SCORE) >= 90 THEN "A"
    WHEN AVG(HG.SCORE) >= 80 THEN "B"
    ELSE "C"
    END AS GRADE,
    
    CASE WHEN AVG(HG.SCORE) >= 96 THEN HE.SAL*20/100
    WHEN AVG(HG.SCORE) >= 90 THEN HE.SAL*15/100
    WHEN AVG(HG.SCORE) >= 80 THEN HE.SAL*10/100
    ELSE 0
    END AS BONUS
    
FROM HR_DEPARTMENT HD
JOIN HR_EMPLOYEES HE ON HD.DEPT_ID = HE.DEPT_ID
JOIN HR_GRADE HG ON HE.EMP_NO = HG.EMP_NO
WHERE HG.YEAR="2022"
GROUP BY EMP_NO
ORDER BY 1;

• 조건이 너무 없어서....SCORE는 평균으로 계산해야한다.

 

14. 부서별 평균 연봉 조회하기

-- 코드를 작성해주세요
SELECT HD.DEPT_ID, HD.DEPT_NAME_EN, ROUND(AVG(HE.SAL)) AS AVG_SAL
FROM HR_DEPARTMENT HD
JOIN HR_EMPLOYEES HE ON HD.DEPT_ID = HE.DEPT_ID
GROUP BY HD.DEPT_ID
ORDER BY 3 DESC;

• 첫 째 자리에서 반올름은 ROUNT() 이용

 

15. 노선별 평균 역 사이 거리 조회하기

-- 코드를 작성해주세요
SELECT ROUTE, CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') TOTAL_DISTANCE, CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AVERAGE_DISTANCE 
FROM SUBWAY_DISTANCE GROUP BY ROUTE ORDER BY SUM(D_BETWEEN_DIST) DESC;

• ROUND(데이터, N) -> N으로 반올림이다. 
• CONCAT()을 이용해 문자열 합산
• ORDER BY할 때 SELECT 에서 사용한 TOTAL_DISTANCE로 정렬하면 문자열을 기준으로 정렬하게 되므로 주의!

 

16. 물고기 종류 별 잡은 수 구하기

-- 코드를 작성해주세요
SELECT COUNT(*) FISH_COUNT, FNI.FISH_NAME FISH_NAME FROM FISH_INFO FI JOIN FISH_NAME_INFO FNI ON FI.FISH_TYPE=FNI.FISH_TYPE
GROUP BY FNI.FISH_NAME
ORDER BY 1 DESC;

 

 

 

17. 월별 잡은 물고기 수 구하기

-- 코드를 작성해주세요
SELECT COUNT(*) FISH_COUNT, MONTH(TIME) MONTH FROM FISH_INFO GROUP BY MONTH(TIME) ORDER BY 2;

 

 

 

18 . 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

SELECT COUNT(*) AS FISH_COUNT,
       MAX(CASE WHEN LENGTH <= 10 THEN 10 ELSE LENGTH END) AS MAX_LENGTH,
       FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(CASE WHEN LENGTH <= 10 THEN 10 ELSE LENGTH END) >= 33
ORDER BY FISH_TYPE;

• 바보같이 처음에 HAVING 안쓰고 WHRER이랑 서브쿼리 써서 풀었다. 왜그랬지?
• 문제 이해를 잘 못했는데, 평균을 구할 때 10 이하인 물고기들(LENGTH가 NULL)을 생각해서 계산하는 식을 작성해야 한다.(신경 안쓰면 테스트 1, 5번 틀림..)

 

19. 저자 별 카테고리 별 매출액 집계하기

-- 코드를 입력하세요
SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(BS.SALES*B.PRICE) TOTAL_SALES
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID=A.AUTHOR_ID
JOIN BOOK_SALES BS ON B.BOOK_ID=BS.BOOK_ID
WHERE BS.SALES_DATE BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY B.AUTHOR_ID, B.CATEGORY
ORDER BY 1 ASC, 3 DESC;

• 같은 저자, 카테고리의 책이 여러권 있을 수 있으므로 SUM을 해준다.
• 참고로 MySQL에서 GROUP BY에 명시되지 않은 값은 SELECT에 사용할 수 없지만, 동일한 값이 없는 경우에는 사용가능함

 

20. 식품분류별 가장 비싼 식품의 정보 조회하기

SELECT FP.CATEGORY, FP.PRICE MAX_PRICE, FP.PRODUCT_NAME
FROM FOOD_PRODUCT FP
WHERE FP.CATEGORY IN ('과자', '국', '김치', '식용유')
  AND FP.PRICE = (SELECT MAX(PRICE) 
                  FROM FOOD_PRODUCT 
                  WHERE CATEGORY = FP.CATEGORY)
ORDER BY FP.PRICE DESC;

• 서브쿼리 없이 바로 GROUP BY를 사용하면 MAX값은 맞게 출력되지만 PRODUCT_NAME은 해당 PRICE의 제품이 아닌 다른 제품(순서대로 아무거나..) 출력된다.

 

21. 카테고리 별 도서 판매량 집계하기

-- 코드를 입력하세요
SELECT B.CATEGORY, SUM(BS.SALES) FROM BOOK B JOIN BOOK_SALES BS ON B.BOOK_ID=BS.BOOK_ID
WHERE BS.SALES_DATE BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY B.CATEGORY
ORDER BY 1;

 

 

22. 즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT RI.FOOD_TYPE, RI.REST_ID, RI.REST_NAME, RI.FAVORITES
FROM REST_INFO RI
WHERE RI.FAVORITES = (
    SELECT MAX(FAVORITES) 
    FROM REST_INFO 
    WHERE FOOD_TYPE = RI.FOOD_TYPE
)
ORDER BY RI.FOOD_TYPE DESC;

 

 

23. 조건에 맞는 사용자와 총 거래금액 조회하기

-- 코드를 입력하세요
SELECT UGU.USER_ID, UGU.NICKNAME, SUM(UGB.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD UGB JOIN USED_GOODS_USER UGU ON UGB.WRITER_ID=UGU.USER_ID
WHERE UGB.STATUS = "DONE"
GROUP BY UGU.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY 3;

 

 

 

24. 언어별 개발자 분류하기

WITH FrontEndCode AS (
    SELECT SUM(CODE) AS CODE
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End'
),
PythonCode AS (
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
),
CSharpCode AS (
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
)
SELECT 
    CASE 
        WHEN SKILL_CODE&FrontEndCode.CODE
             AND SKILL_CODE&PythonCode.CODE THEN 'A'
        WHEN SKILL_CODE&CSharpCode.CODE THEN 'B'
        WHEN SKILL_CODE&FrontEndCode.CODE THEN 'C'
    END AS GRADE,
    ID, EMAIL
FROM DEVELOPERS, FrontEndCode, PythonCode, CSharpCode
HAVING GRADE IS NOT NULL
ORDER BY GRADE, ID;

• A, C조건을 모두 충족하는 경우가 있어 틀릴 수 있음
• FrontEndCode, PythonCode, CSharpCode 모두 중복적으로 서브쿼리를 계속 호출하므로 CTE를 만들어 주었다. 이것들을 SELECT에 사용하기 위해 FROM에서 호출하기
• 만약 SELECT 절에 사용한 데이터에 조건을 추가로 걸고 싶으면 WHERE이 아닌 HAVING 절에 작성한다.

 

 

 

---

회고

 

마지막 문제에서 진을 다 뺐다....

CTE에 익숙해 질수록 더 효율적인 코드가 나올 것 같다.

SQL은 문제 조건이 모호한게 많아서 더 헷갈린다.ㅜㅜ