SQL Study 3주차 진행중인데, 매주 정말 잘했다는 생각이 든다!
각자 문제 풀이도 진행하고 매주 방향도 잡아가면서 좋은 Study가 된 느낌!
이번 3주차 계획은 프로그래머스 SQL 고득점 킷 SUM,MAX,MIN, GROUP BY 문제 풀이 진행, 데이터베이스 개론 책 구매하고 읽어보기!
🙌 1. 학습 내용(함수 및 문법구조) 정리
MAX() : 데이터타입이 INTEGER, DATETIME에도 적용 가능! 날짜의 경우 가장 최근 값 출력
MIN() : MAX와 마찬가지로 가장 늦은 날짜 값도 출력 가능
COUNT(필드명) : 해당 필드 내 NULL 제외한 ROW 개수 세기
COUNT(*) : NULL 포함하여 값 세기
COUNT(DISTINCT, 필드명) : 해당 필드명 중복 제거하고 카운팅 진행
- CASE WHEN
- WHEN, THEN은 한 쌍이어야 하고 다수가 존재할 수 있음
- ELSE가 있다면 WHEN절에 포함되지 않는 모든 조건에 해당하지 않는 반환 값 설정 가능
- ELSE가 없다면 모든 조건에 부합하지 않는 경우 NULL 출력
- END로 반드시 CASE 절 마무리 하기
- TRUNCATE(숫자, 버릴 자리수)
숫자를 버릴 자리수 아래로 전부 버린다.
HOUR() : 데이터타입이 DATETIME, 날짜 형태인 VARCHAR인 경우 >> 시 출력
MINUTE() : 데이터타입이 DATETIME, 날짜 형태인 VARCHAR인 경우 >> 분 출력
SECOND() : 데이터타입이 DATETIME, 날짜 형태인 VARCHAR인 경우 >> 초 출력
함수(필드명) AS '별칭'
위 사용 시 추후 사용되는 변수 명으로 별칭이 아닌 함수(필드명) 사용할 것!
(파이썬과 이부분이 다른 듯 하다.)변수 생성?
SET() : 어떠한 변수에 특정 값을 넣어줄 때 사용
@는 변수 앞에 붙여주어 @변수명 = K 는 변수에 K를 넣겠다는 의미
:= 대입하겠다는 의미
🙌 2. 문제 풀이
💯 가장 비싼 상품 구하기
SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT< 풀이 과정 >
주어진 문제 그대로 PRICE 컬럼 중 가장 큰 값을 선택하는 것이므로 MAX(PRICE)로 불러오기
💯 최댓값 구하기
SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS< 풀이 과정 >
DATETIME 타입에도 MAX가 적용되는구나를 알게 됌.
💯 가격이 제일 비싼 식품의 정보 출력하기
SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1< 풀이 과정 >
LIMIT 2,6 : 상위 2~6개 레코드 출력
Question) 하위 n개 레코드 출력은 어떻게?
: 정렬 순서 변경하기!
💯 최솟값 구하기
SELECT MIN(DATETIME) AS '시간'
FROM ANIMAL_INS< 풀이 과정 >
DATETIME인 데이터 타입에 MIN 함수 적용해서 가장 먼저 들어온 동물의 시간 출력
💯 동물 수 구하기
SELECT count(ANIMAL_ID) AS count
FROM ANIMAL_INS< 풀이 과정 >
테이블 내의 행 수를 구하는 문제. UNIQUE한 ANIMAL_ID 개수를 세어 해결하였다.
💯 중복 제거하기
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS< 풀이 과정 >
DISTINCT 이용하여 중복제거! COUNT는 애초에 NULL 값은 세질 않음.
단, COUNT(*) 는 NULL 포함해서 셈
💯 성분으로 구분한 아이스크림 총 주문량
SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF A
LEFT JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER< 풀이 과정 >
ICECREAM_INFO 테이블의 FLAVOR이 FIRST_HALF 테이블의 외래키라고 주어졌으므로 LEFT JOIN을 FLAVOR를 기준으로 진행하였다.
이후 ICECREAM_INFO 테이블의 INGREDIENT_TYPE을 기준으로 TOTAL_ORDER를 합산처리 하였고 오름차순 정렬하였다.
💯 즐겨찾기가 가장 많은 식당 정보 출력하기
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< 풀이 과정 >
그동안 서브쿼리를 직접 실행시켜 본 적이 없어 많이 해맸던 문제..
어느 필드, 어느 테이블, 어떻게 정렬하는 것은 알고 있으나, Favorites 수가 많은 음식 종류를 select해야 했기에 서브쿼리를 사용하여 문제를 해결하였다.
- WHERE문을 이용하여 REST_INFO 테이블에서 음식 종류를 기준으로 Favorites 수가 많은 음식 종류 select
- 이후 음식 종류 기준으로 내림차순 정렬하면 끝
💯 카테고리 별 도서 판매량 집계하기
SELECT B.CATEGORY, SUM(BS.SALES) AS TOTAL_SALES
FROM BOOK B
LEFT JOIN BOOK_SALES BS ON BS.BOOK_ID = B.BOOK_ID
WHERE BS.SALES_DATE LIKE '2022-01%'
GROUP BY CATEGORY
ORDER BY CATEGORY < 풀이 과정 >
처음엔 GROUP BY를 이용하지 않고 문제를 풀다보니 결과가 이상하게 나왔다.
GROUP BY를 이용하지 않으면 SUM(BS.SALES)한 값이 CATEGORY 1번 행에 몰아서 나왔기 때문.
- BOOK테이블의 CATEGORY, BOOK_SALES 테이브르이 SALES의 합 SELECT 후 BOOK_SALES 테이블 LEFT JOIN 진행
- WHERE 문으로 2022-01월 인 경우만 조건 달고
- CATEGORY기준 그룹화 및 오름차순 정렬 진행
💯 가격대 별 상품 개수 구하기
SELECT (CASE
WHEN PRICE < 10000 THEN 0
ELSE TRUNCATE(PRICE, -4) END) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP< 풀이 과정 >
SQL에서는 IF ELSE문을 CASE ~ WHEN ~ THEN ~ END로 다루는 사실을 알게 되었고 TRUNCATE함수도 알게 되었다.
- 가격대 기준으로 그룹핑 진행하기 위해 SELECT(CASE ~ WHEN ~ THEN ~ END)를 이용하여 PRICE_GROUP 필드 SELECT, 상품 ID 카운팅으로 PRODUCTS 필드 SELECT.
- 10000원대보다 가격대가 이하면 0으로, 이외에는 뒷자리 4개를 0으로 지운다.
- PRICE_GROUP 기준 그룹화 & 오름차순 정렬 진행
위 문제를 풀면서 느낀건데 확실히 SQL은 WHERE절에 조건을 붙여주는 것과 애초에 SELECT 할 때 조건을 달아주는 느낌이 다르다.
💯 입양 시각 구하기(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< 풀이 과정 >
지난 주차 학습했던 YEAR, MONTH 이외에도 시간대 출력인 HOUR함수 존재.
HOUR함수로 시간대 별 입양 건수 그룹화 이후 9~19 시간대를 HAVING절로 조절
💯 고양이와 개는 몇 마리 있을까
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE< 풀이 과정 >
- ANIMAL_INS 테이블로 부터 동물 종류와 개수 세어주는 COUNT 필드 SELECT
- 이때 동물종류로 그룹화 및 오름차순 정렬 수행 (Cat > Dog 순이므로)
💯 진료과별 총 예약 횟수 출력하기
SELECT MCDP_CD AS '진료과코드', COUNT(MCDP_CD) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY COUNT(MCDP_CD), MCDP_CD< 풀이 과정 >
구현하는데 어려운 문제는 아니었다. MCDP_CD를 기준으로 예약된 환자 수를 카운팅하는 문제.
학습한 부분 : AS로 별칭을 붙인 필드명을 ORDER BY, WHERE등에 사용할 경우 AS 앞부분을 이용하여 진행할 것!
ORDER BY '5월예약건수', '진료과코드'로 코드 진행 X
💯 동명 동물 수 찾기
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME< 풀이 과정 >
동물의 이름이 2번이상 나온 케이스 모두 출력하는 문제.
- NAME, COUNT(NAME) AS COUNT로 SELECT 진행
- 동물 이름 기준 GROUP BY
- 이름이 없는 동물은 집계에서 제외하므로 COUNT(*)가 아닌 COUNT(NAME) 진행!
💯 입양 시각 구하기(2)
- 실패 코드
SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)< 풀이 과정 >
위 코드로 실행해보면, 7시 ~ 19시까지만 출력되어 문제에서 요구하는 0시 ~ 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< 풀이 과정 >
SET 함수와 @로 HOUR 변수 생성 후 해당 변수에 -1값 입력!
-1부터 시작해 := 를 통해 +1을 대입하여 진행하고 HOUR값이 22일 경우 23이 마지막이 되게 출력 진행
서브쿼리를 이용하여 HOUR 변수와 같은 시간대인 DATETIME만을 출력
GROUP BY 없이 서브쿼리를 이용하여 문제를 해결할 수 있는 부분에서 배워가는 부분이 많았던 문제
💯 년, 월, 성별 별 상품 구매 회원 수 구하기
SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER, COUNT(DISTINCT U.USER_ID) AS USERS
FROM ONLINE_SALE O
INNER JOIN USER_INFO U ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE), U.GENDER
ORDER BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE), U.GENDER< 풀이 과정 >
주어진 조건을 꼼꼼히 읽어봐야 하는 문제.
구현은 그렇게 어렵지 않았다. 이전 주차에 학습된 JOIN, DISTINCT 덕분!
- YEAR, MONTH 함수로 연, 월 출력하고 성별을 SELECT
- COUNT(USER_ID)의 경우 동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다. 와 같은 조건이 있으므로 DISTINCT를 앞에 붙여주고 COUNT 진행!
- USER_INFO 테이블과 ONLINE_SALE INNER JOIN 진행
- GENDER가 NULL인 경우 제거를 위해 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 MAX_PRICE DESC< 풀이 과정 >
앞서 풀었던 "즐겨찾기가 가장 많은 식당 정보 출력하기"와 유사한 문제
- CATEGORY별 가격이 가장 높은 행만 출력하는 것이기에 WHERE절에 서브쿼리를 넣어 출력
- 서브쿼리 내에 CATEGORY가 과자, 국, 김치, 식용유인 경우의 가장 비싼 가격에 판매 중인 행만 뽑아서 본 쿼리에 추가
- 마지막으로 식품 가격 기준 내림차순 정렬
💯 저자 별 카테고리 별 매출액 집계하기
SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE*BS.SALES) AS TOTAL_SALES
FROM BOOK B
INNER JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
INNER JOIN BOOK_SALES BS ON BS.BOOK_ID = B.BOOK_ID
WHERE BS.SALES_DATE LIKE '2022-01%'
GROUP BY B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY
ORDER BY B.AUTHOR_ID, B.CATEGORY DESC< 풀이 과정 >
테이블이 3개여서 조인을 어떻게 하지?라는 고민과 함께 일단 그냥 해보자 하고 시도했더니 해결한 문제.
음 말로 해석하려 하니 좀 힘든데 간략하게 얘기하면 BOOK_SALES 테이블의 날짜가 2022년 1월인 것을 기준으로 AUTHOR 테이블, BOOK 테이블의 카테고리를 그룹핑하여 총 판매량을 계산하는 문제!
- 각 테이블에서 필요한 필드만을 추출하여 SELECT 진행
- INNER JOIN 2회 실행 BOOK & AUTHOR , BOOK & BOOK_SALES
- WHERE 절로 2022년 1월만 출력
- 저자 별 카테고리 별 그룹핑 하여 SUM()인 총 판매량 계산하기
- 저자 ID, 카테고리 내림차순 정렬
🙌 3. 참고
서브쿼리 이용한 문제 풀이 - 즐겨찾기가 가장 많은 식당 정보 출력하기
https://velog.io/@nn1co1/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4%EC%A6%90%EA%B2%A8%EC%B0%BE%EA%B8%B0%EA%B0%80-%EA%B0%80%EC%9E%A5-%EB%A7%8E%EC%9D%80-%EC%8B%9D%EB%8B%B9-%EC%A0%95%EB%B3%B4-%EC%B6%9C%EB%A0%A5%ED%95%98%EA%B8%B0
입양 시각 구하기 (2)
https://velog.io/@ljs7463/MySQL-%EC%9E%85%EC%96%91-%EC%8B%9C%EA%B0%81-%EA%B5%AC%ED%95%98%EA%B8%B02
위 문제들은 다른 작성자 분의 코드를 보고 이해하며 작성해보았습니다!
🙌 4. 3주차 SQL 문제 풀이 회고
확실히 이전 SELECT 문에 비해 난이도가 확 올라간 느낌.. 파이썬 코테에 비해 코드가 상당히 직관적이어서 눈에는 쉽게 들어오고 문제 풀이도 수월하다.
지난 주차까지만 해도 서브쿼리가 안좋다고 생각해서 자세히 문법 공부를 하진 않았는데 음.. GROUP BY를 해보면서 특정 필드의 MAX, SUM 등의 값들은 서브쿼리를 이용하여 해결할 수 있음을 깨달았다.
또한 확실히 함수가 다양하구나를 문제를 풀 때마다 느낀다. 지난주는 UNION, 이번주는 CASE ~ WHEN ~ THEN ~ END, TRUNCATE, DISTINCT, SET 등등.
꾸준하게 매주 문제를 풀고 SQL도 다룰 줄 아는 Skill을 보유하길...✍️🙌
