SQL 4주차 스터디 정리

Jihoon·2022년 12월 18일

groupby sql subquery

DB & SQL Study

목록 보기

2/8

✨ SUM, MAX, MIN

🎈 가격이 제일 비싼 식품의 정보 출력하기 (MAX 유형)

# 나의 답 (오답)
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, MAX(PRICE) AS PRICE
FROM FOOD_PRODUCT

# 정답
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE)
               FROM FOOD_PRODUCT)

서브 쿼리를 사용하는 이유
→ 해당 쿼리는 가격이 최대인 상품과 최대 가격이 매칭 되지 않음

가장 빠른 시간 이런건 시간만 뽑아내면 되는데, 이렇게 전체적으로 컬럼들을 매칭이 되도록 뽑으려면 서브쿼리 이용하는 것과 같은 맥락

🎈중복 제거하기

# 나의 답 (오답)
SELECT SUM(COUNT(NAME)) as count
FROM ANIMAL_INS
WHERE NAME IN (SELECT *
        FROM ANIMAL_INS
        GROUP BY NAME
        HAVING NAME IS NOT NULL)


# 정답 
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

DISTINCT
→ 컬럼 내의 중복된 값 제거 함수!

NAME IS NOT NULL
→ NULL인 이름을 제외시켜주는 역할!

✨ GROUP BY

📍동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

📍 입양 시각 구하기(1)

# 나의 답 (오답)
SELECT DATE_FORMAT(DATETIME, '%h') as HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR

# 정답 1
SELECT HOUR(DATETIME) as HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR

# 정답 2
SELECT DATE_FORMAT(DATETIME, '%H') as HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR

'h' 와 'H'의 차이
→ h : 0 ~ 12 , H : 0 ~ 24

📍 입양 시각 구하기(2)

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;

Python의 반복문을 SQL에서는 이와 같이 사용한다고 생각

WHERE절에서 만족할 때 까지 SELECT문에서 한 번 반복이 돌때마다 해당 조건에 맞게 로우가 하나씩 생김

SET, SELECT, FROM, WHERE

🌟 사용자 정의 변수 선언 및 초기화

SET @변수이름 = 대입값 ; or SET @변수이름 := 대입값
SELECT @변수이름 = 대입값; (X)
SELECT @변수이름 := 대입값; (X)

SET 이 외의 명령문에서는 = 가 비교연산자!

변수 선언 및 값 대입 시에는 := 사용

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

🌟 DF 3개에 대해 Join 2번 이용

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

WHERE MONTH(BS.SALES_DATE) = 1
GROUP BY  A.AUTHOR_ID, B.CATEGORY
ORDER BY AUTHOR_ID, B.CATEGORY DESC

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

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

이런게 중요하지 않을까?

년, 월, 성별을 통해서 Groupby를 진행하여 그룹을 형성

생성된 그룹에 대해서 Target이 되는 User_id는 중복이 될 수 있으니까, User_id 컬럼에 대해서 중복을 제거하는 DICSTINCT 사용

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

# 나의 답 (오답)
SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) AS FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

# 정답 코드
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

WHY ? 왜 틀렸을까?

SUB QUERY 및 집계 함수 및 GROUP BY 활용 알고리즘의 이해 부족

그러니까, 위에서 틀렸던 문제와 같은 맥락임

음식 종류에 따른 즐겨찾기수(FAVORITES)의 값들은 맞췄지만, 나머지 컬럼들인 식당 번호 및 이름이 맞지가 않는 문제가 발생함

Why? 위에 나의 답을 보면 GROUP BY로 묶어서 MAX값을 취하면 값에 대해서만 적용이 됌, 그 값에 맞는 나머지 컬럼들을 가진 ROW, 즉 DATA를 갖고오는 게 아니라는 말임

따라서, 서브쿼리를 통해 그 값을 불러와서 WHERE절에 조건으로 넣고, 그 조건에 맞는 애들을 메인 쿼리를 통해서 테이블에서 조건에 매칭되는 ROW를 불러오는 형태를 만들어야 함

값을 불러오는 문제와 해당 값을 만족하는 ROW(DATA)를 불러오는 문제는 다른 문제임을 인식하고, 문제 풀 것

✨ Study 회고

이번 스터디를 진행하기 전에 푸는 문제 수와 난이도가 갑자기 높아져서 상대적으로 시간을 많이 할애하긴 했지만, 이제야 SQL이 어떤 것인지 조금 알 거 같다

GROUP BY, (SUM,MAX,MIN)집계 함수를 같이 하면 시너지 효과가 좋을 것 같아서, 이와 같이 진행하자고 했는데 팀원들이 잘 따라와줘서 고맙다

SUB QUERY를 왜 쓰나? 했는데, GROUP BY를 이용하거나 특정 값을 가져올 때(쿼리 문 두개 짜야할 때? 아직 확실히 감은 안옴) 많이 사용되서 이번에 써봤는데, 앞으로 자주 쓸 것 같다

이제 DB 책도 사서 같이 해야하는데, 이력서도 써야하고, 모델 관련해서 공부도 많이해야 하고, 대회 집중 및 최종프로젝트 진행도 해야되고, 코테도 해야되고 어우..

완벽히 하려고 하지 말고, 할 수 있는 정도를 정해서 거기서 성취감을 느끼는 식으로 하자. 어짜~피 다 못한다. 이 정도양은 죽어도. 그러니, DB책은 일단 사고, 스케줄 조정을 잘하자!

다음 스터디는 크리스마스. JOIN이 어렵다는데, 고득점 KIT 다 풀어 버리고, DB 공부를 언능 들어가고 싶다. FIGHTING!