GROUP BY를 제대로 이해하는 시간. 항상 어려워하는 부분 중 하나인데 이번 기회에 제대로 뿌시고 가고 싶다.
[LV3] 즐겨찾기가 가장 많은 식당 정보 출력하기
MAX
⏰ 25:04
[배움 1] GROUP BY의 기본 출력 법칙
SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC
처음에 내가 풀었던 답은 이거였다.
아니... 쉬운 문제 아니야? 라고 생각했지만 GROUP BY의 기본 규칙이 내가 생각하는 것과 같지 않았다.
GROUP BY를 했을 때, SELECT에서 불러오는 나머지 값들 (위에서는 REST_ID와 REST_NAME)은 가장 먼저 찾은 기준 행을 불러 온다. 근디 MAX()가 있는 열은 그 기준에 해당하는 값들 중 가장 큰 걸 불러온다(!!)
그러면 뭐다? ID와 NAME 따로, MAX(FAVORITES) 값 따로 각각 다른 행에서 불러올 수 있다는 거다.
이건 늘 GROUP BY를 헷갈려하는 내게 아주 중요한 단서가 되었다.
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
[배움 2] WHERE과 튜플(그룹)
또 하나 배운 건 WHERE 절에서 쓰고 있는 '튜플' 기준이다. 나의 최고의 SQL 선생님 챗GPT 님을 모십니다...
[LV3] 조건에 맞는 사용자와 총 거래금액 조회하기
서브쿼리 / HAVING
⏰ 14:15
겁먹지 말고 하나씩 하나씩 차근차근 쓰다 보면 생각보다 긴 SQL도 뚝딱 만들 수 있다. 기량이 쌓여가고 있다... 아자아자!
SELECT U.USER_ID, U.NICKNAME, B.TOTAL_SALES
FROM (SELECT WRITER_ID, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD
WHERE STATUS = 'DONE'
GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000) B
JOIN USED_GOODS_USER U
ON B.WRITER_ID = U.USER_ID
ORDER BY B.TOTAL_SALES
[LV4] 저자 별 카테고리 별 매출액 집계하기
JOIN / SUM
⏰ 6:45
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY,
SUM(B.PRICE * S.SALES) AS TOTAL_SALES
FROM BOOK B
JOIN AUTHOR A
USING (AUTHOR_ID)
JOIN BOOK_SALES S
USING (BOOK_ID)
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC
[LV3] 카테고리 별 도서 판매량 집계하기
SUM / GROUP BY
⏰ 2:49
SELECT B.CATEGORY, SUM(S.SALES) AS TOTAL_SALES
FROM BOOK B
JOIN BOOK_SALES S
USING (BOOK_ID)
WHERE DATE_FORMAT(S.SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY 1
ORDER BY 1
[LV3] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
MAX / SUM / IF / CASE
⏰ 50:32
한번씩 방심하면 엄청난 복병(?)이 기다리고 있다...
이번 문제의 핵심은 그룹 내에 일정 값이 포함되어 있는지 여부를 판별해 그에 따라 출력값을 다르게 뽑는 것.
다른 사람들의 다양한 풀이법을 보다 보면 이 함수가 이런 식으로도 활용이 가능하구나, 많이 배워간다.
이번 배움도 꿀팁 가득이라 하나씩 잘 정리해본다.
[배움 1] MAX의 다른 쓰임 - BETWEEN 등의 판별식과 함께 쓰면 해당 그룹에 TRUE값이 있는 경우에만 값을 출력한다.
SELECT CAR_ID, case
when max('2022-10-16' between start_date and end_date) then '대여중'
else '대여 가능'
end as AVAILABILITY
from car_rental_company_rental_history
group by car_id
order by car_id desc;- MAX 를 BETWEEN 함수와 함께 쓸 수 있는 거였는지 처음 알았다. 순서를 정리하면 아래와 같다.
- GROUP BY에서 CAR_ID를 기준으로 묶을거고
- MAX에서 각 그룹별로 가장 큰 값을 뽑을 건데
- 2022-10-16이 BETWEEN 날짜에 해당되는 경우가 하나라도 있다면 그 TRUE 값 = 1이 가장 큰 값으로 TRUE를 출력해 '대여중'으로 표시한다.
- '대여중'인 값이 없다면 '대여가능'.
[배움 2] MAX의 다른 쓰임 2 - 문자열을 정렬한 뒤 가장 높은 값만 뽑을 수도 있다.
- 만약 '대여중'이 포함되어 있을 경우 '대여중'을 출력, 없다면 '대여가능' 출력.
- 정렬상 '대여 가능'이 더 위라면 MAX 대신 MIN을 쓴다.
SELECT CAR_ID, MAX(AVAILABILITY) AS AVAILABILITY
FROM (
SELECT CAR_ID,
CASE WHEN '2022-10-16' between start_date and end_date THEN '대여중'
ELSE '대여 가능' END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) A
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
[배움 3] SUM의 다른 쓰임 - IF와 함께 해당 그룹 내의 포함값을 조회하는 판별식처럼 쓸 수 있다.
SELECT CAR_ID,
CASE WHEN SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0)) = 0 THEN "대여 가능"
ELSE "대여중" END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
놀랍게도 이 외에도 WITH구문을 활용한 풀이, JOIN과 UNION을 활용한 풀이, 기타 등등 다양한 풀이법이 있다.
투입하는 시간만큼 이해도가 깊어지고 있기를!
[LV2] 진료과별 총 예약 횟수 출력하기
COUNT / DATE_FORMAT
⏰ 7:23
SELECT MCDP_CD AS 진료과코드, COUNT(APNT_NO) AS 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,'%Y-%m-%d')
BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY MCDP_CD
ORDER BY 2,1
[LV2] 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
COUNT / LIKE
⏰ 3:36
SELECT CAR_TYPE, COUNT(CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%'
OR OPTIONS LIKE '%열선시트%'
OR OPTIONS LIKE '%가죽시트%'
GROUP BY 1
ORDER BY 1
[LV3] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
SUBQUERY / WITH
⏰34:28
대여횟수가 5회 이상인 열을 뽑기 위해 넣은 WITH구문 혹은 SUBQUERY 이후에도!
본쿼리의 WHERE절에서 날짜 조건을 한 번 더 걸어줘야 한다는 걸 뒤늦게 깨달아서 오래 헤맸다.
서브쿼리 내의 구문을 고대로 WITH 구문으로 옮겨주기만 하면 같은 쿼리를 짤 수 있다.
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
GROUP BY CAR_ID
HAVING COUNT(HISTORY_ID) >= 5)
AND MONTH(START_DATE) IN (8, 9, 10)
GROUP BY 1, 2
ORDER BY 1, 2 DESC;WITH RECORD_TABLE AS (
SELECT CAR_ID, COUNT(HISTORY_ID) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
GROUP BY CAR_ID
HAVING RECORDS >= 5
)
SELECT MONTH(C.START_DATE) MONTH, C.CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
JOIN RECORD_TABLE R
USING (CAR_ID)
WHERE MONTH(C.START_DATE) IN (8,9,10)
GROUP BY 1, 2
ORDER BY 1, 2 DESC
[LV2] 성분으로 구분한 아이스크림 총 주문량
SUM / USING
⏰ 5:49
* USING 에는 괄호를 꼭 써줘야 한다.
SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
JOIN ICECREAM_INFO I
USING (FLAVOR)
GROUP BY 1
ORDER BY 2
[LV4] 식품분류별 가장 비싼 식품의 정보 조회하기
WHERE / IN / MAX
⏰ 9:12
이제 어떨 때 WHERE에서 튜플 조건과 서브쿼리를 이용해 뽑아야 하는지 조금 알 것 같다!
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY)
ORDER BY 2 DESC
[LV2] 고양이와 개는 몇 마리 있을까
COUNT
⏰ 1:23
SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_INS
GROUP BY 1
ORDER BY 1
[LV2] 동명 동물 수 찾기
IS NOT NULL / HAVING / COUNT
⏰ 4:23
SELECT NAME, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY 1
HAVING COUNT > 1
ORDER BY 1
[LV4] 년, 월, 성별 별 상품 구매 회원 수 구하기
COUNT / IS NOT NULL
⏰ 5:41
DISTINCT 안 걸어놓고 한참 헤맸다. 조건을 더 꼼꼼히 살피자!
SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, GENDER, COUNT(DISTINCT USER_ID) AS USERS
FROM ONLINE_SALE S
JOIN USER_INFO U
USING (USER_ID)
WHERE GENDER IS NOT NULL
GROUP BY 1,2,3
ORDER BY 1,2,3[LV2] 입양 시각 구하기(1)
COUNT / HOUR / HAVING
⏰ 2:30
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY 1
HAVING HOUR BETWEEN 9 AND 19
ORDER BY 1
[LV4] 입양 시각 구하기(2)
SET / RECURSIVE
⏰ 1시간 이상
......위 문제와 같은 테이블을 쓰길래 호기롭게 덤볐다가 SET / RECURSIVE라는 새로운 함수를 접하고 시간지옥에 빠졌다... 한시간 공부했지만 아직도 완벽히 이해가 되지 않는다. 적당히 문제만 풀고 넘어갈 수도 있지만 나중에 또 못 쓰면 무슨 의미가 있나. 일단 넘어가고 다음에 이어서 더 깊게 공부해봐야겠다.
+ 일주일 후 돌아온 나. RECURSIVE 함수 뚝딱 이해해내고 금방 풀었다. 지난주에 왜 멘붕이었는지 알 수가 없네..? 성장했다! 우하하
WITH RECURSIVE HOURS AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM HOURS
WHERE HOUR < 23
)
,
ANIMAL_OUTS_HOUR AS (
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY 1)
SELECT H.HOUR, IFNULL(A.COUNT,'0') AS COUNT
FROM HOURS H
LEFT JOIN ANIMAL_OUTS_HOUR A
USING (HOUR)
ORDER BY 1
[LV2] 가격대 별 상품 개수 구하기
FLOOR / TRUNCATE / COUNT
⏰ 19:34
이것도 간단한 문젠데 많이 헤맸다... 그냥 내림/버림 함수 쓰면 가격대를 구할 수 있다.
[정답 1] FLOOR 활용
SELECT floor(price / 10000) * 10000 as price_group, COUNT(*) AS PRODUCTS
from product
GROUP BY 1
ORDER BY 1 ASC
[정답 2] TRUNCATE 활용
SELECT TRUNCATE(PRICE,-4) as price_group, COUNT(*) AS PRODUCTS
from product
GROUP BY 1
ORDER BY 1 ASC
쉬운 문제부터 풀고 있지만 다른 문제들도 계속 추가해 수정 예정!
해도 해도 공부할 게 끝이 없는 재미난 세상 ~.~
'[공부] SQL' 카테고리의 다른 글
| [SQL 코딩테스트] 리트코드 Advanced String Functions / Regex / Clause 정답 (0) | 2024.01.29 |
|---|---|
| [SQL 코딩테스트] 프로그래머스 JOIN 정답 (0) | 2024.01.26 |
| [SQL 코딩테스트] 리트코드 Sorting and Grouping 정답 (1) | 2024.01.24 |
| [SQL 코딩테스트] 리트코드 Basic Aggregate Functions 정답 (4) | 2024.01.23 |
| [SQL 코딩테스트] 리트코드 Basic Joins 정답 (0) | 2024.01.22 |
