[코테준비]SQL-SQL 고득점 kit - GroupBy Lv4
프로그래머스 SQL 고득점 Kit 연습풀이입니다.
모든 문제의 저작권은 프로그래머스에게 있으며 문제가 됐을 시 삭제하겠습니다.
https://school.programmers.co.kr/learn/courses/30/parts/17044
SQL 고득점 Kit - GroupBy lv4 문제들입니다.
문제 풀이는 다음과 같습니다
1) 저자별 카테고리별 매출액 집계하기 - LV4
2) 식품분류별 가장 비싼 식품의 정보 조회하기- LV4
3) 년,월, 성별별 상품 구매 회원 수 구하기- LV4
4) 입양 시각 구하기(2)- LV4
5) 언어별 개발자 구하기- LV4
6) 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기- LV4
문제이름: 저자별 카테고리별 매출액 집계하기
문제풀이
1) TOTAL_SALES 를 SALES * PRICE(판매랑 * 판매가) 공식을 이용해서 구하고, SUM을 해준다. 아니면 맨 앞에 나온다.
2) 3개의 테이블을 병합해준다. 2개 병합하는 것처럼 INNER JOIN을 반복해서 쓴다
3) 2022년 1월 책은 WHERE 조건절에 작성한다.
4) SUM을 씀으로 GROUP BY를 써야 한다는 직감이 온다. 이때 저자별, 카테고리별 2개의 묶음이므로 연이어 써준다.
5) ORDER BY도 동일하다
전체코드
SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(SALES*PRICE) AS TOTAL_SALES
FROM BOOK AS A
INNER JOIN AUTHOR AS B ON A.AUTHOR_ID = B.AUTHOR_ID
INNER JOIN BOOK_SALES AS C ON A.BOOK_ID = C.BOOK_ID
WHERE YEAR(SALES_DATE)=2022 AND MONTH(SALES_DATE)=1
GROUP BY A.AUTHOR_ID, CATEGORY
ORDER BY A.AUTHOR_ID, CATEGORY DESC
문제이름 : 식품분류별 가장 비싼 식품의 정보 조회하기
문제풀이
1) 처음에는 간단히 서브 쿼리 절이 아니라고 생각했다. SELECT 절에 MAX 값을 넣고, WHERE 절에 IN ("과자","국","김치","식용유") 조건을 넣고 카테고리별 GROUP BY를 이용하였다
1) 처음에는 간단히 서브 쿼리 절이 아니라고 생각했다. SELECT 절에 MAX 값을 넣고, WHERE 절에 IN ("과자","국","김치","식용유") 조건을 넣고 카테고리별 GROUP BY를 이용하였다SELECT CATEGORY, MAX(PRICE) MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ("과자","국","김치","식용유")
GROUP BY CATEGORY
ORDER BY MAX(PRICE) DESC
==> 이 문제는 GROUP BY로 뽑아 낸 후에 가장 큰 값을 가져와야 한다. 하지만 저렇게 작성하면 PRODUCT_NAME은 PRODUCT_ID 순서로 첫번째 값을 가져오고, MAX값과 PRODUCT_NAME 열이 안맞는다..
2) 따라서 서브쿼리절로 GROUP BY 시킨 MZX 값의 카테고리, PRICE, PRODUCT_NAME을 가져오는 식으로 수정한다
3) 추가적으로 WHERE는 그룹화 전의 개별 행을 필터링하므로 HAVING절로 구해야 한다.
전체코드
SELECT CATEGORY, PRICE AS MAX_PRICE,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY,PRICE) IN (SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ("과자","국","김치","식용유"))
ORDER BY PRICE DESC
문제이름 : 년,월, 성별별 상품 구매 회원 수 구하기
문제풀이
1) 4단계 문제중에 쉬운 문제에 속한다. 우선 FROM절에 2개의 테이블을 공통항에 따라 묶어준다.
2) 성별이 없는 경우는 WHERE 절을 통해 제거해준다.
3) 년, 월, 성별 기준으로 GROUP BY 이어서 진행해준다.
4) ORDER BY도 순서대로 작성한다.
5) SELECT 절에서 날짜함수 YEAR, MONTH을 통해 년 월을 추출한다. 이때 ID가 중복되지않으므로 DISTINCT를 써주고, 구매한 회원수를 구해야하기 때문에 COUNT(*)함수를 써준다.
전체코드
SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH,
GENDER, COUNT(DISTINCT(A.USER_ID)) AS USERS
FROM USER_INFO AS A JOIN ONLINE_SALE AS B ON A.USER_ID = B.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER
문제이름 : 입양 시각 구하기(2)
문제풀이
1) hour를 만드느데 실패해서 검색을 좀 하였다.
date(hour) + 조인 쿼리를 하면 쿼리문이 복잡해지고 길어진다
이때 SET을 사용한다 EX) SET @새로운 변수 = 초기값
2) hour := hour+1 >> for문처럼 hour를 한시깐 씩 늘력가면서 생성. where 조건절에 마지막 반복되는 문단 생성
전체코드
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;
문제이름 : 언어별 개발자 구하기
문제풀이
1) 비트연산자 구분 기호는 & 이다. 포함한다면 1이상의 값이 나와야 하고 일치하지 않으면 다 0이 나오기 때문이다
SKILL_CODE랑 조건의 SKILL CODE랑 구분해서 0이상인 값을 찾으면 된다! 이때, 파이썬인것은 하나밖에 없지만
카테고리는 CODE가 다양하므로 SUM이 필수다.
2) 이 조건은 없으므로 새로운 열을 만들어줘야 한다. 이때 쓰이는 문법은
CASE WHEN 조건1 THEN "결과1" WHEN 조건2 THEN "결과2" ..... END AS 새로운 열 이름
3) GRADE가 존재 하는열만 뽑아야 한다. 이때 CASE-WHEN절에 맞는 건 WHERE 절에 못쓰므로 HAVING절에 추가한다.
전체코드
SELECT
CASE
WHEN SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python') > 0
AND SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') > 0 THEN 'A'
WHEN SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#') > 0 THEN 'B'
WHEN SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') > 0 THEN 'C'
END AS GRADE, ID, EMAIL
FROM DEVELOPERS
HAVING GRADE IS NOT NULL #CASE-WHEM으로 만든건 WHERE 절에서 못씀
ORDER BY GRADE, ID
문제이름 : 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
문제풀이
1) 위의 문제와 비슷하다. GRADE, SAL이 없기 대문에 CASE-WHEN-THEN절로 새롭게 만들어준다
이때 점수가 높은 애들부터 해야 한다. 아니면 다 B등급으로 설정이 되고 바뀌지 않는다.
2) 처음엔 AVG를 설정을 안해놨는데,, 이렇게 하면 동명의 칼럼이 두개씩 나오는 오류가 나서 문제를 자세히 봤더니
전반기/하반기로 나뉘어져있었다. 따라서 AVG를 설정해야한다..!
전체코드
##동명의 컬럼이 두개씩 나오는 오류가 나왔는데 문제를 자세히 살펴보니 반기로 나뉘어 있었다. 상반기, 하반기로 나뉘어서 두 번이 출력이 되는 오류 발견
SELECT
E.EMP_NO,
E.EMP_NAME,
CASE
WHEN AVG(G.SCORE) >= 96 THEN 'S'
WHEN AVG(G.SCORE) >= 90 THEN 'A'
WHEN AVG(G.SCORE) >= 80 THEN 'B'
ELSE 'C'
END AS GRADE,
CASE
WHEN AVG(G.SCORE) >= 96 THEN E.SAL * 0.2
WHEN AVG(G.SCORE) >= 90 THEN E.SAL * 0.15
WHEN AVG(G.SCORE) >= 80 THEN E.SAL * 0.1
ELSE E.SAL * 0
END AS BONUS
FROM HR_DEPARTMENT AS D
INNER JOIN HR_EMPLOYEES AS E ON D.DEPT_ID = E.DEPT_ID
INNER JOIN HR_GRADE AS G ON E.EMP_NO = G.EMP_NO
GROUP BY E.EMP_NO
ORDER BY E.EMP_NO;
프로그래머스 GROUP BY 끝!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!