[MySQL] SQL 문제풀이 (GROUP BY)

SQL 고득점 Kit

고득점 키트에서 풀지 않은 그룹바이 문제 11개를 풀고자 합니다 ^_^

 

오잉 그런데 순서대로 풀고 있었는데,, 며칠 뒤 그룹바이 문제목록을 보니까 순서가 바뀌어있네요 ?! 

 뒤죽박죽으로 11문제를 풀어보았습니다..,,

---

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

그룹바이로 분류된 문제지만 조인과 연관도 있는 문제였다

우선 두 테이블을 조인한다. 그리고 where문을 통해 개별 행마다 done인 것을 추출해낼 것이다

그리고 유저 아이디로 그룹화한 다음, 유저가 판 물건 금액의 합이 700000이상인 것을 고른다.

(여담: 속성은 주로 복붙하는데.. nickname이 문제 설명에 nickanme으로 표기되어있어서 복붙 실수를 했다 ㅋㅋㅋ) 

SELECT U.USER_ID AS USER_ID, U.NICKNAME AS NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B LEFT JOIN USED_GOODS_USER AS U
ON U.USER_ID= B.WRITER_ID
WHERE B.STATUS='DONE'
GROUP BY U.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;

---

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

서브쿼리를 사용해야하는 문제이다.

정답 코드는 아래와 같다. (사실... 정답입니다! 는 뜨지만 정답이 아니다...ㅋㅋㅋ 아래 내용 참고 ㅠ)

찬찬히 들여다 보면.. 

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

...

질문) 아래 코드 처럼 MAX (FAVORITES)를 가져오면 안되남

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

 

답변) GROUP BY를 쓰면 MAX 함수를 썼을 때,

MAX(그룹바이했을때 썼던 속성) 값은 잘 불러 오는데, 그 외의 속성을 select 하면 엉뚱한 값을 불러온다.

그래서 그룹바이를 한번한 뒤, 그룹마다의 MAX값이 뭔지 꺼내오고, 그 값이랑 같은 값을 가진 row를 꺼내온 것이다. 이를 구현하기 위해 서브쿼리를 쓴 것이다.

 

그룹바이를 공부하다보면 "GROUP BY는 GROUP BY에 정의한 내용(컬럼 또는 변형된 컬럼)만 SELECT절에 그대로 사용할 수 있다." 라는 말이 있다. 이 말이 바로 그 말이다! (출처: https://wikidocs.net/132723)

 

바로 위의 코드처럼 그룹바이를 할 경우, 그룹바이때 사용한 속성과, 집계함수를 쓴 속성 외의 속성들은 값을 랜덤하게 가져온다.

 

여담으로 아래와 같이 sql_mode를 변경하면 랜덤하게 값을 가져오는게 아니라 error가 발생하게 할 수 있다 (출처: https://school.programmers.co.kr/questions/38703) 

SET sql_mode='ONLY_FULL_GROUP_BY';

실제로 이 속성을 반영하면 프로그래머스에서 아래와 같은 오류가 뜬다.

해석하면 알 수 없는 값을 select 하고 있어서 오류가 난거다.. 라는 

SQL 실행 중 오류가 발생하였습니다.
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_runner_run.REST_INFO.REST_ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

근데 생각해보니까... 정답입니다! 가 떴던 아까 그 코드.. 뭔가 이상하다!!

아까 그 코드대로 한다면 만약

한식 즐겨찾기 수: 100, 300, 600

일식 즐겨찾기 수: 200, 300 인 경우에

서브쿼리 결과물은 [600, 300] 이 있을 것이다.

그리고 FAVORITES가 [600,300]에 매핑되는 row를 가져와야하는데

최종적으로 원하는 row는 [한식, 600] 이지만... [한식, 300] 값을 불러올 수도 있는거 아녀?

 

그래서~ WHERE 절을 (타입, 즐찾수) IN (SELECT 타입, MAX(즐찾수) FROM 테이블 GROUP BY 타입) 이라고 해야한다. 그래야 타입-즐찾수가 쌍으로 조회되고 올바른 row를 가지고 오게 된다.

 

아래는 진짜 정답 코드이다.

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)
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC;

위에 첨부한 위키 문서를 위에서부터 읽어보면 " GROUP BY에 정의하지 않은 컬럼을 SELECT절에서 사용하려면 반드시 집계함수 처리를 해야 한다."라고 한다. 이 말이 곧 그 말이며 ....

 

자 자 .. 다시 생각을 해 봅시다

서브쿼리만 떼고 보면

SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE

위와 같다. 위의 코드는 왜 문제가 안생길까?

SELECT 한 FOOD_TYPE의 경우, 그룹바이에서 사용한 속성이기 때문에 원하는 값을 명확히 셀렉할 수 있다.

그리고 MAX(FAVORITES)의 경우, 집계함수인 MAX를 썼기 때문에 올바른 값을 가져올 수 있다.

그냥 FAVORITES라고 하면 또 문제가 생길 것이다.

여기에 추가로 SELECT에 REST_NAME을 넣는다면 ? 마찬가지로 문제가 생기는 것이다. (랜덤 값 or 그룹화된 row들 중 가장 상단의 row의 REST_NAME이 셀렉한 REST_NAME에 들어가게 됨 -> 우리가 원하지 않는 값이 들어간단 소리!)

 

위에 남긴 프로그래머스의 QnA 링크 타고 들어가면 "GROUP BY 구의 기준이 되는 필드가 아니거나 집계 함수를 통해 어떤 값을 반환해야 할 지 결정해주지 않은 필드를 SELECT 구에 사용할 경우 문법적으로 틀린 SQL 구문입니다." 라는 말이 있는데 이말이 그말이다.

 

헷갈리니까.. 결론만 말하자면 아래 코드가 정답이다!

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)
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC;

---

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

join을 3번 하면 된다!

판매된 책에 관련된 데이터가 필요하므로 판매량 테이블을 기준으로 left join을 2번 한다.

그리고 2022-01에 판매된 것만 필요하기 때문에, SALES_DATE에 2022-01이 포함된 것을 가져온다.

마지막으로 그룹바이를 통해 아이디와 카테고리를 기준으로 그룹화를 하고, 정렬하면 된다.

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(S.SALES*B.PRICE) AS TOTAL_SALES
FROM BOOK_SALES AS S LEFT JOIN BOOK AS B
ON S.BOOK_ID = B.BOOK_ID
LEFT JOIN AUTHOR AS A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE S.SALES_DATE LIKE '2022-01%'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID, B.CATEGORY DESC;

---

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

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;

---

5) 카테고리 별 도서 판매량 집계하기

두 테이블을 조인해준 다음, 2022-01에 판매된 책들을

카테고리 별로 그룹화 하면 된다.

SELECT B.CATEGORY, SUM(S.SALES) AS TOTAL_SALES
FROM BOOK_SALES AS S LEFT JOIN BOOK AS B
ON S.BOOK_ID = B.BOOK_ID
WHERE S.SALES_DATE LIKE '2022-01%'
GROUP BY CATEGORY
ORDER BY CATEGORY;

---

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

우선 자동차 아이디 별로 대여 기록을 그룹화를 한다.

그리고 그룹 내 하나의 기록이라도 대여중으로 처리가 되어있다면,

그 아이디에 해당되는 자동차는 대여중인 것이다.

 

우선 서브쿼리를 이용해 해당 날짜에 대여중인 차량을 모두 추출한다.

그리고 해당 자동차 아이디가, 서브쿼리의 결과물 내에 하나라도 있으면 (=IN) 그 차는 대여중인것이다.

서브쿼리를 이용해서 풀었다.

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

---

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

이 문제는 2) 즐겨찾기가 가장 많은 식당 정보 출력하기 문제와 아주 유사하다.

위의 문제를 이해했다면 같은 방식으로 풀 수 있다!

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

---

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

우선, 8월과 10월 사이에 5회 이상 대여한 자동차의 리스트를 구하기 위해 서브쿼리를 썼다.

서브쿼리 없이 한번에, 세 달동안 5회 이상인지 합산을 하고, 각각의 달마다 대여 횟수를 계산하려고 하니까 처음엔 문제가 잘 안풀렸다.

 

서브쿼리를 통해, 8-10월 사이에 5회 이상 대여한 자동차의 아이디를 조회한 뒤,

해당 아이디를 가진 자동차의 8-10월 데이터를 가져왔고,

이 데이터를 아이디와 월을 기준으로 그룹화했다.

이때, 특정 월의 대여 기록이 0이라면 제외해야 해서 HAVING에 조건을 추가했다.

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= '2022-08-01' AND START_DATE < '2022-11-01'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) AND START_DATE >= '2022-08-01' AND START_DATE < '2022-11-01'
GROUP BY CAR_ID, MONTH(START_DATE)
HAVING COUNT(*) > 0
ORDER BY MONTH(START_DATE), CAR_ID DESC;

---

9) 년, 월, 성별 별 상품 구매 회원 수 구하기

이 문제 또한 8번 처럼 여러 속성을 기반으로 그룹화를 하면 되는데, 간과하기 쉬운 부분이 있다.

바로 SELECT 절에 DISTINCT를 사용하는 것이다.

왜냐하면, 같은 회원이, 같은 년도 같은 월에 다른 상품을 구매할 수 있는데, 이 경우 중복 카운트가 되기 때문이다.

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

---

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

이 문제는 쉬워보였는데 어려웠다.

0 ~ 23 시 까지 각각의 시간대에 입양된 동물 수를 구해야하는데, (없을 경우 0으로 표기)

그냥 그룹바이만 하면 입양된 동물이 있는 시간대만 조회된다. (입양된 동물이 0마리일 경우 해당 시간대 조회 X)

 

그래서 임시로 0 ~ 23 이 포함된 아래와 같은 임시 테이블을 만들어서,

임시 테이블을 기준으로, 기존 테이블을 left join 시켰다.

+------+
| HOUR |
+------+
|   0  |
|   1  |
|   2  |
|   3  |
|  ... |
|  22  |
|  23  |
+------+

 

이 임시 테이블을 만들기 위해 RECURSIVE WITH 을 사용했다. (처음 써봄)

우선 WITH 절은, 서브쿼리 및 임시 테이블 처럼 활용할 수 있는 절이다.

RECURSIVE WITH는 일종의 for문과 같은 효과를 낼 수 있다.

 

두번째 줄에 있는, SELECT 0 AS HOUR 을 통해 초기 값을 설정할 수 있다.

그리고 이후 초기값을 기반으로 반복을 통해 값을 추가할 것이다.

아래 코드는, HOUR의 값을 1씩 증가시키며 HOUR이라는 칼럼에 0 ~ 23을 넣는 코드이다.

처음에 무의식적으로 HOUR < 24 라고 했는데,

이 경우 HOUR가 23일 때도 반복을 돌아서 HOUR + 1 = 24,

즉 0 ~ 24까지 값이 저장된다. 따라서 0 ~ 23이라는 값을 넣으려면 HOUR < 24 라고 해야한다.

 

그리고 이렇게 만들어진 테이블과, 기존의 동물 입양 정보가 적힌 테이블을 join 시켰다.

WITH RECURSIVE TEMP_TABLE AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM TEMP_TABLE
    WHERE HOUR < 23
)

SELECT T.HOUR, COUNT(A.ANIMAL_ID) AS COUNT
FROM TEMP_TABLE AS T LEFT JOIN ANIMAL_OUTS AS A
ON HOUR(A.DATETIME) = T.HOUR
GROUP BY T.HOUR
ORDER BY T.HOUR;

---

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

10000원 단위로 그룹화 해야하기 때문에, /10000을 한 뒤 소숫점 이하를 버리고 * 10000을 했다.

그리고 그 금액을 기준으로 그룹화를 했다.

 

소숫점을 버리려면 FLOOR 함수를 사용하면 된다.

이 함수는 소숫점을 버리고 가장 근접한 정수를 반환한다.

 

추가로, ROUND는 반올림을 하는 자릿수를 정해, 반올림 할 수 있는 함수다.

CEILING은 올림 자릿수를 지정할 수 없으며, 반올림을 해서 정수를 반환하는 함수다.

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

---

 

GROUP BY 문제풀이 끝!