천천히 빛나는

MySQL : GROUP BY 문 고득점 Kit 본문

STUDY/MYSQL

MySQL : GROUP BY 문 고득점 Kit

까만콩 •ᴥ• 2023. 10. 21. 00:39

프로그래머스 SQL 고득점 Kit의 GROUP BY 문제입니다.

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

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

SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD, USED_GOODS_USER
WHERE USER_ID = WRITER_ID AND STATUS = 'DONE'
GROUP BY USER_ID
HAVING  SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES;

FROM → WHERE절 → GROUP BY → HAVING → SELECT → ORDER BY 순서인 것을 고려해서 WHERE과 HAVING을 설정해야 한다.

 

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

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                   	     FROM REST_INFO
                   	     GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

서브에서 GROUP BY를 해주는 문제였다

 

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

SELECT B.AUTHOR_ID, W.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK AS B, AUTHOR AS W, BOOK_SALES AS BS
WHERE B.AUTHOR_ID = W.AUTHOR_ID AND B.BOOK_ID = BS.BOOK_ID AND YEAR(BS.SALES_DATE) = 2022 AND MONTH(BS.SALES_DATE)=1
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC;

SUM을 이용해서 그룹핑 된 값들의 곱의 합계를 구해야한다

 

 

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

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF, ICECREAM_INFO
WHERE FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR
GROUP BY INGREDIENT_TYPE;

GROUP BY의 기본 문제였다

 

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

SELECT CAR_ID, IF(('2022-10-16' <= MAX(END_DATE)), '대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-10-16'
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

if (조건문, 참일 때 반환하는 값, 거짓일 때 반환하는 값) 을 사용하였다

CASE WHEN CAR_ID IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) 
                        THEN '대여중'
                        ELSE '대여 가능'
END AS 'AVAILABILITY'

CASE WHEN 조건식 THEN 반환식

           WHNE 조건식 THEN 반환식

            ELSE 만족하지 않을경우

END

와 같은 형식의 CASE WHEN을 사용해도 된다.

CASE WHEN과 GROUP BY를 같이 썼을 때, 하나라도 대여 중이라면 모두 대여 중으로 기록이 되는 것이다. 하나도 대여 중이라고 판단되는게 없으면 대여 가능이 된다.

https://weaver9651.github.io/mysql/2021/03/14/mysql-case-having.html

 

Archive - MySQL CASE, HAVING 정리

개요 MySQL의 CASE, GROUP BY, HAVING에 대해서 간략하게 정리하고 복잡했던 예시를 하나 소개한다. CASE CASE는 다음과 같은 형태로 사용한다. CASE WHEN 조건 THEN 참값 ELSE 거짓값 END ‘조건’에 새로운 CASE

weaver9651.github.io

 

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;

LIKE를 이용해서 쉽게 구현할 수 있다

 

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

SELECT MONTH(START_DATE) 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 (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, CAR_ID desc

메인 쿼리에 날짜 조건문을 붙여야 하는데 서브에만 붙여서 오류가 많이 났따. 서브에만 붙이면 해당 날짜 외에도 COUNT가 된다. 그러므로 메인쿼리를 꼭 신경써야 한다.

 

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

SELECT MCDP_CD AS '진료과코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD;

GROUP BY 기본 문제이다

 

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

SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES
FROM BOOK, BOOK_SALES
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE)=1 AND BOOK.BOOK_ID = BOOK_SALES.BOOK_ID
GROUP BY CATEGORY
ORDER BY CATEGORY;

GROUP BY는 ~~별 과 같은 문장이 나올 때 사용하면 된다

 

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

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

내부쿼리에 GROUP BY를 사용해야 한다

 

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

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

고양이 먼저 하려면 오름차순으로 하면 된다. (CAT, DOG)

 

12. 동명 동물 수 찾기

SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT >= 2
ORDER BY NAME;

IS NOT NULL : NULL 값은 제외

 

13. 년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, USER_INFO.GENDER, COUNT(DISTINCT USER_INFO.USER_ID) AS USERS
FROM USER_INFO, ONLINE_SALE
WHERE USER_INFO.USER_ID = ONLINE_SALE.USER_ID AND GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

DISTINCT 함수로 중복을 제거해야 한다

 

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

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS  COUNT
FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME, '%H:%i') BETWEEN '09:00' AND '19:59'
GROUP BY HOUR
ORDER BY HOUR;

BETWEEN 없이 하면 HOUR 그대로 이용할 수도 있다

 

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

없는 시간까지 통계를 내서 만들어주어야 하는 문제였다. 문제 풀이 전에 변수 선언하는 법을 알아야 한다

SET @sum := 0; // 변수 선언

SET @변수이름 := 값;을 이용해서 변수를 선언해줄 수 있다. 지역변수가 된다.

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23;

@HOUR 값에 1씩 증가시키면서 SELECT 문을 실행하게 된다. WHERE 조건을 통해 23까지만 출력을 하게 된다.

SET @HOUR := -1; # 변수선언

SELECT (@HOUR := @HOUR +1) AS HOUR,
	(SELECT COUNT(*) 
	FROM ANIMAL_OUTS 
	WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
	FROM ANIMAL_OUTS
WHERE @HOUR < 23;

변수 선언에 대해 알고 있어야지만 풀 수 있는 문제였다.

왜 -1로 시작하고 <23이 되는지는 FROM → WHERE절 → GROUP BY → HAVING → SELECT → ORDER BY 를 안다면 이해할 수 있다.

 

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

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

나는 직접 바꿔주는 식을 썼는데 TRUNCATE(PRICE, -4)를 사용해도 됐다. TRUNCATE(숫자, 버릴자리수) 이다.

 

 

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

SELECT 
    GRADE, ID, EMAIL
FROM (
    SELECT 
        CASE 
            WHEN 
                EXISTS (SELECT * FROM SKILLCODES WHERE NAME = 'PYTHON' AND CODE & DEVELOPERS.SKILL_CODE > 0)
                AND EXISTS (SELECT * FROM SKILLCODES WHERE CATEGORY = 'Front End' AND CODE & DEVELOPERS.SKILL_CODE > 0)
                THEN 'A'
            WHEN 
                EXISTS (SELECT * FROM SKILLCODES WHERE NAME = 'C#' AND CODE & DEVELOPERS.SKILL_CODE > 0)
                THEN 'B'
            WHEN 
                EXISTS (SELECT * FROM SKILLCODES WHERE CATEGORY = 'Front End' AND CODE & DEVELOPERS.SKILL_CODE > 0)
                THEN 'C'
        END AS GRADE, ID, EMAIL
    FROM 
        DEVELOPERS
) AS subquery
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID;

WHERE에서 FILTER 해줄라면 순서 고려해서 테이블 하나 만들어주고 해야 된다.

저작자표시 비영리 변경금지

'STUDY > MYSQL' 카테고리의 다른 글

MySQL : JOIN 연산 + 기본 예제  (0) 2023.10.21
MySQL : IS NULL 문 고득점 Kit  (0) 2023.10.21
MySQL : SUM, MAX, MIN 문 고득점 Kit  (1) 2023.10.20
MySQL : SELECT 문 고득점 Kit  (1) 2023.10.20
MySQL : SELECT 문 + 기본 예제 (2)  (1) 2023.10.20
'STUDY/MYSQL' Related Articles more