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

 

프로그래머스 GROUP BY 문제가 보통 Level 3~4인 문제들이 꽤나 다수로 나온다 어떤 걸 집계하고, 풀어나가야 되는지

어려운 문제들이 많기 때문에 오답노트와 풀이과정을 좀 정리해보려 한다.

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

 

 

문제 설명

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE는 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

 

[ 문제 ]

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중'이라고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

[ 예시 ]

 

정답 쿼리

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;

 

풀이 해석

 

1. 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중'이라고 표시하고, 대여 중이지 않는 자동차인 경우 '대여 가능'을 표시하는 칼럼(칼럼명: AVAILABILITY)을 추가

    CASE
        WHEN MAX(
            CASE 
                WHEN  '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1
                ELSE 0
            END
        ) = 1 THEN '대여중'
        ELSE '대여 가능'

 

- CASE문을 사용 

- 서브쿼리를 사용해 2022-10-16일이 대여 시작일자(포함)와 대여 종료 일자(포함) 사이에 있는 CAR_ID를 구한다.

- GROUP BY를 통해 CAR_ID별로 위에서 구한 조건에 해당하는 CAR_ID라면 '대여중'으로, 그렇지 않으면 '대여 가능'으로 표시해 준 후 AVAILABILTY라는 칼럼에 값을 출력한다.

 

2. 자동차 ID를 기준으로 내림차순 정렬

- ORDER BY 칼럼명 DESC

 

---

Programmers - 저자 별 카테고리 별 매출액 집계하기

https://school.programmers.co.kr/learn/courses/30/lessons/144856

프로그래머스

 

문제

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가)을, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

 

풀이 해석

" 이문제는 풀이해석으로 단계별씩 풀이를 진행하였다"

BOOK - 저자 ID, 카테고리, 판매가 조회

AUTHOR - 저자명 조회

BOOK_SALES - 판매량 조회

2022년 1월 도서 판매 데이터여야 함

저자 ID, 카테고리 ASC

 

 

※ 여기서 (판매가*판매량)을 계산하여 TOTAL_SALES 칼럼 추가해야 함

1단계 - 세 테이블을 포함한 컬럼을 동시에 조회하는 INNER JOIN

SELECT
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID

 

2단계 - 저자 ID, 저자명, 카테고리, 매출액을 조회하는 SELECT문

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALE AS BS ON B.BOOK_ID = BS.BOOK_ID

 

 

매출액은 '판매량 * 판매가' 로 계산해주고 TOTAL_SALES 컬럼으로 정의해 준다.

 

3단계 - YEAR(),MONTH()함수를 이용 - WHERE절

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALE AS BS ON B.BOOK_ID = BS.BOOK_ID
WHERE YEAR(BS.SALES_DATE) = 2022 AND MONTH(BS.SALES_DATE) = 01

 

만약 데이터 포맷이 '%Y-%m-%d %h:%m:%s'의 형태가 아니라 연속된 숫자 형식이라면

데이터의 위치를 통해 년도는 0~3번째가 '2022'인것, 월은 4~5번째가 '01'인것으로 조회할수 있지 않을까?

 

4단계 - 저자 별, 카테고리 별 매출액 그룹화 - GROUP BY

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALE AS BS ON B.BOOK_ID = BS.BOOK_ID
WHERE YEAR(BS.SALES_DATE) = 2022 AND MONTH(BS.SALES_DATE) = 01
GROUP BY A.AUTHOR_NAME, B.CATEGORY

 

5단계 - 오름차순, 내림차순 - ORDER BY (정답쿼리)

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALE AS BS ON B.BOOK_ID = BS.BOOK_ID
WHERE YEAR(BS.SALES_DATE) = 2022 AND MONTH(BS.SALES_DATE) = 01
GROUP BY A.AUTHOR_NAME, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC;

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

 

문제

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

 

풀이 해석

 

1단계 - 식품 분류별로 가격이 제일 비싼 식품의 분류,가격,이름 조회 SELECT

SELECT CATEGORY, PRICE AS MAX_PRICE,PRODUCT_NAME

 

CATEGORY - 식품의 분류

PRICE - 식품의 가격

PRODUCT_NAME - 식품의 이름

 

2단계 - 식품 분류별로 가격이 제일 비싼 식품의 분류 - 서브쿼리

WHERE PRICE IN(SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)

 

3단계 - 이때 식품 분류가 '과자','국','김치','식용유'인 경우 - IN

AND CATEGORY IN ('과자','국','김치','식용유')

4단계 - 식품 가격을 기준으로 내림차순

ORDER BY MAX_PRICE DESC

 

*이문제에서는 IN조건 을 쓰는거에 대해서 배웠다.

- 여러값을 OR관계로 묶어 나열하는 조건을 WHERE절에 사용할 때 쓸수있는 키워드이다.

- IN 연산자는 조건의 범위를 지정하는데 사용된다.

- 값은 콤마(,)로 구분하여 괄호 내에 묶으며, 이 값 중에서 하나 이상과 일치하면 조건에 맞는것으로 평가됨.

 

정답 쿼리

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

---

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/151139

프로그래머스

 

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

 

풀이

1. 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들

- 위 조건을 먼저 서브쿼리로 구해야 한다.(쿼리문의 4번째 줄부터 8번째 줄에 해당)

- DATE_FORMAT 함수를 사용해 차종별 대여 시작일이 2022년 8월과 10월 사이에 있는 행들을 구한다.

- HAVING 절을 사용해 차종별 총 대여 횟수가 5번 이상인지 확인

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
  )

 

2. 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수 리스트를 출력

- 대여 시작일이 2022년 8월부터 2022년 10월인 자동차 중 위에서 구한 서브쿼리에 해당하는 행을 구한다.

- GROUP BY를 월별, 자동차 ID별 총 대여 횟수를 구한다.

- 이때, 특정 월의 총 대여횟수가 0인 경우는 결과에서 제외하기 위해 HAVING 절에 대여 횟수가 1 이상인 경우를 조건으로 걸어준다.

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

 

3. 정렬

- ORDER BY 컬럼명 오름차순(ASC 생략가능)정렬, 내림차순(DESC)정렬

ORDER BY month ASC, car_id DESC;

 

정답 쿼리

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 ASC, car_id DESC;

 

처음 제출한 답안인데 틀렸다. 생각해보니 월별 자동차 ID별 총 대여 횟수를 구해야 하는데, CAR_ID로만 묶을 것이 아니라 월(MONTH)로도 묶어야할 것 같다는 생각이 들었다.

 

비슷하지만 WHERE절에 서브쿼리를 사용한 것 GROUP BY에서 MONTH와 CAR_ID를 이용해 묶은 것이 다르다.

---

Programmers - 입양 시각 구하기(2)

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

이문제를 풀면서 SET절과 @HOUR에 대해서 배우게 되었다.

문제를 다시 읽어보니 0시부터 23시까지 출력을 하라고 했는데 해당 데이터에서는 7시부터 데이터가 존재한다.

그래서 0~6시까지의 결과가 출력되지 않았다.

 

따라서 이 문제는 0~6시까지의 데이터를 따로 생성해줘야 한다.

어떻게 풀어야 할지 감이 안와서 다른 사람들의 코드를 보고 아이디어를 참고 했다.

이 문제에서는 SET 명령어를 사용해야 한다.

 

풀이

SET은 어떤 변수에 특정 값을 할당할때 쓰는 명령어이다.

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

다음 코드는 @HOUR변수에 -1을 할당한 후 22까지 +1씩 더해준다는 뜻이다.

 

여기서 주의를 해야 한다. SET 사용시 대입 연산자를 '='를 사용하고 그 외에는 := 를 사용해야 한다.

다음과 같이 코드를 실행하면 아래와 같은 결과가 나온다.

 

정답 쿼리

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