지난 글에 프로그래머스 SQL 고득점 KIT 공부했던 내용 이어 정리 ..!
1에서 정리한 내용은 제일 기본 중 기본의 내용이고, 지금 정리하는 부분부터가 조금 더 난이도가 있는 내용이다. (실제로 봤던 SQL테스트에도 JOIN, GROUP BY, DATE를 다 사용해야 하는 내용들이 많이 출제되었다.)
SQL 고득점 KIT
GROUP BY
가격대 별 상품 개수 구하기(lev2)
SELECT (CASE WHEN PRICE < 10000 THEN 0
ELSE TRUNCATE (PRICE, -4)
END) as PRICE_GROUP
, COUNT(PRODUCT_ID) as PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1- TRUNCATE(column, 숫자): 숫자를 버릴 자릿수 아래로 버림 (숫자가 음수일 경우, 정수에 뒤에서부터 지정된 자릿수까지 0으로 처리)
진료과별 총 예약 횟수 출력하기(lev2)
SELECT MCDP_CD as '진료과코드'
, COUNT(PT_NO) as '5월예약건수'
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = '05' # DATE_FORMAT(APNT_YMD, '%Y-%m') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 2, 1성분으로 구분한 아이스크림 총 주문량(lev2)
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) as TOTAL_ORDER
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I
on F.FLAVOR = I.FLAVOR
GROUP BY 1
ORDER BY 2고양이와 개는 몇 마리 있을까(lev2)
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as COUNT
FROM ANIMAL_INS
GROUP BY 1
ORDER BY 1입양 시각 구하기(1)(lev2)
SELECT HOUR(DATETIME) as HOUR
, COUNT(ANIMAL_ID) as COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY 1
ORDER BY 1- HOUR(날짜): 날짜 중 시간대만 반환 (9시 -> 9)
동명 동물 수 찾기(lev2)
SELECT NAME, COUNT(NAME) as COUNT
FROM ANIMAL_INS
GROUP BY 1
HAVING COUNT(NAME) > 1
ORDER BY 1자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기(lev2)
SELECT CAR_TYPE, COUNT(CAR_ID) as CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '시트'
# WHERE OPTIONS LIKE '%시트%'
# WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY 1
ORDER BY 1-
REGEXP '가|나|다|라': 컬럼에 '가' 또는 '나' 또는 '다' 를 포함한 레코드 모두 출력
(= where column like '%가%' or column like '%나%' or column like '%다%') -
REGEXP '가': 컬럼에 '가'를 포함한 모든 레코드 출력
(= where column like '%가%')
카테고리 별 도서 판매량 집계하기(lev3)
SELECT CATEGORY, SUM(SALES) as TOTAL_SALES
FROM BOOK B
LEFT JOIN BOOK_SALES BS
on B.BOOK_ID = BS.BOOK_ID
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") LIKE '2022-01'
GROUP BY 1
ORDER BY 1즐겨찾기가 가장 많은 식당 정보 출력하기(lev3)
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT *, RANK () OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RK
FROM REST_INFO) AS R
WHERE R.RK = 1
ORDER BY 1 DESC- RANK() OVER (PARTITION BY 그룹핑 column ORDER BY 정렬 column): 순위 구할 때 사용하는 함수로, PARTITION BY는 특정 컬럼 기준으로 그룹핑한다는 의미, ORDER BY는 특정 컬럼 기준으로 정렬한다는 의미
대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(lev3)
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 MONTH(START_DATE) BETWEEN 8 and 10
GROUP BY 1
HAVING COUNT(HISTORY_ID) >= 5)
AND MONTH(START_DATE) BETWEEN 8 and 10
GROUP BY 1, 2
ORDER BY 1, 2 DESC- 참고: WHERE 서브쿼리 사용해 8월부터 10월까지 총 대여 횟수가 5회 이상인 CAR_ID만 추출
식품분류별 가장 비싼 식품의 정보 조회하기(lev4)
SELECT CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME
FROM (SELECT *, RANK () OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RK
FROM FOOD_PRODUCT) R
WHERE CATEGORY IN ('과자','국','김치','식용유')
AND RK = 1
ORDER BY 2 DESCSELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
AND PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
ORDER BY 2 DESC- 참고: RANK 함수 사용하거나, 혹은 WHERE 절에 GROUP BY 사용하는 방법
년, 월, 성별 별 상품 구매 회원 수 구하기(lev4)
SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH, GENDER, COUNT(DISTINCT OS.USER_ID) as USERS
FROM ONLINE_SALE OS
LEFT JOIN USER_INFO UI
on UI.USER_ID = OS.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3저자 별 카테고리 별 매출액 집계하기(lev4)
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES
FROM (SELECT B.BOOK_ID, CATEGORY, B.AUTHOR_ID, AUTHOR_NAME, (PRICE*SALES) AS TOTAL_SALES
FROM BOOK B
JOIN AUTHOR A
on A.AUTHOR_ID = B.AUTHOR_ID
JOIN BOOK_SALES BS
on B.BOOK_ID = BS.BOOK_ID
WHERE MONTH(SALES_DATE) = 1
) BOOK_TBL
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESCWITH TBL as (
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, (SALES*PRICE) as TOTAL_SALES
FROM BOOK_SALES BS
LEFT JOIN (SELECT BOOK_ID, CATEGORY, A.AUTHOR_ID, AUTHOR_NAME, PRICE
FROM BOOK B
LEFT JOIN AUTHOR A
on B.AUTHOR_ID = A.AUTHOR_ID) BA
on BS.BOOK_ID = BA.BOOK_ID
WHERE MONTH(SALES_DATE) = 1
)
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES
FROM TBL
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC- 참고: 서브 쿼리로 해결 가능 혹은 WITH문 사용 가능
입양 시각 구하기(2)(lev4)
SET @HOUR = -1;
WITH TIME_TBL as (
SELECT (@HOUR := @HOUR +1) as HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23
)
SELECT TT.HOUR, IFNULL(COUNT, 0) as COUNT
FROM TIME_TBL TT
LEFT JOIN (SELECT HOUR(DATETIME) as HOUR, COUNT(ANIMAL_ID) as COUNT
FROM ANIMAL_OUTS
GROUP BY 1
ORDER BY 1) AO
ON TT.HOUR = AO.HOUR- SET: 사용자 정의 변수로, SET @변수이름 = 대입값; 으로 변수 지정 (위 문제에서는 변수 HOUR를 -1으로 지정하고 HOUR을 -1부터 +1씩 증가시킨다는 뜻(0~23))
WITH RECURSIVE HOUR as (
SELECT 0 as HOUR
UNION ALL
SELECT HOUR+1 FROM HOUR WHERE HOUR<23
)
SELECT HOUR, COUNT(ANIMAL_ID) as COUNT
FROM HOUR H
LEFT JOIN ANIMAL_OUTS AO
on H.HOUR = HOUR(AO.DATETIME)
GROUP BY 1- WITH RECURSIVE: 재귀쿼리로, 이를 이용해 실제 테이블을 생성하지 않아도 가상 테이블 생성 가능 (메모리 상에 가상의 테이블 저장하는 방법)
- 형식
WITH RECURSIVE table as( SELECT 초기값 as column1 UNION ALL SELECT column1의 계산식 FROM table WHERE 제어문 ) - 반드시 UNION 사용
- 반복되는 마지막 문장의 경우 WHERE 뒤에 항상 정지조건 요구됨
- 형식
JOIN
상품 별 오프라인 매출 구하기(lev2)
SELECT PRODUCT_CODE, (PRICE*SUM(SALES_AMOUNT)) AS SALES
FROM PRODUCT P
INNER JOIN OFFLINE_SALE O
on P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY 1
ORDER BY 2 DESC, 1- INNER JOIN: 교집합을 의미, 두 테이블을 매치할 때 겹치지 않는 행이 존재할 경우 그 행은 결과에서 제외됨
조건에 맞는 도서와 저자 리스트 출력하기(lev2)
SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B
LEFT JOIN AUTHOR A
on A.AUTHOR_ID = B.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY 3 - LEFT JOIN: 왼쪽 테이블 기준으로 오른쪽의 테이블 매치
없어진 기록 찾기(lev3)
SELECT AO.ANIMAL_ID, AO.NAME
FROM ANIMAL_OUTS AO
LEFT JOIN ANIMAL_INS AI
on AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE INTAKE_CONDITION IS NULL
ORDER BY 1, 2오랜 기간 보호한 동물(1)(lev3)
SELECT AI.NAME, AI.DATETIME
FROM ANIMAL_INS AI
LEFT JOIN ANIMAL_OUTS AO
on AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AO.DATETIME IS NULL
ORDER BY 2
LIMIT 3있었는데요 없었습니다(lev3)
SELECT AI.ANIMAL_ID, AI.NAME
FROM ANIMAL_INS AI
LEFT JOIN ANIMAL_OUTS AO
on AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.DATETIME > AO.DATETIME
ORDER BY AI.DATETIME보호소에서 중성화한 동물(lev4)
SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME
FROM ANIMAL_INS AI
LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE != AO.SEX_UPON_OUTCOME주문량이 많은 아이스크림들 조회하기(lev4)
SELECT FH.FLAVOR
FROM FIRST_HALF FH
LEFT JOIN JULY J
on J.FLAVOR = FH.FLAVOR
GROUP BY 1
ORDER BY FH.TOTAL_ORDER + SUM(J.TOTAL_ORDER) DESC
LIMIT 3그룹별 조건에 맞는 식당 목록 출력하기(lev4)
WITH MEMBER_COUNT as(
SELECT M.MEMBER_ID, MEMBER_NAME, COUNT(REVIEW_ID) as REVIEW_COUNT
FROM MEMBER_PROFILE M
LEFT JOIN REST_REVIEW R
on R.MEMBER_ID = M.MEMBER_ID
GROUP BY 1, 2
ORDER BY 3 DESC
)
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM REST_REVIEW R
LEFT JOIN MEMBER_COUNT M
on M.MEMBER_ID = R.MEMBER_ID
WHERE REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MEMBER_COUNT)
ORDER BY 3, 15월 식품들의 총매출 조회하기(lev4)
SELECT FP.PRODUCT_ID, PRODUCT_NAME, PRICE*SUM(AMOUNT) as TOTAL_SALES
FROM FOOD_ORDER FO
INNER JOIN FOOD_PRODUCT FP
on FP.PRODUCT_ID = FO.PRODUCT_ID
WHERE DATE_FORMAT(PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY 1, 2
ORDER BY 3 DESC, 1상품을 구매한 회원 비율 구하기 (lev5)
WITH UI as(
SELECT USER_ID
FROM USER_INFO UI
WHERE YEAR(JOINED) = '2021'
)
SELECT YEAR(SALES_DATE) as YEAR
, MONTH(SALES_DATE) as MONTH
, COUNT(DISTINCT OS.USER_ID) as PUCHASED_USERS
, ROUND(COUNT(DISTINCT OS.USER_ID)/(SELECT COUNT(USER_ID) FROM UI), 1) as PUCHASED_RATIO
FROM UI
INNER JOIN ONLINE_SALE OS
on OS.USER_ID = UI.USER_ID
GROUP BY 1, 2
ORDER BY 1, 2String, Date
자동차 대여 기록에서 장기/단기 대여 구분하기(lev1)
SELECT HISTORY_ID
, CAR_ID
, DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE
, DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE
, (CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'
ELSE '단기 대여'
END) as RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') = '2022-09'
ORDER BY 1 DESC- DATEDIFF(날짜1, 날짜2): 날짜 차이 계산하는 함수
카테고리 별 상품 개수 구하기(lev2)
SELECT SUBSTR(PRODUCT_CODE, 1, 2) as CATEGORY, COUNT(PRODUCT_ID)
FROM PRODUCT
GROUP BY 1
ORDER BY 1-
SUBSTR(문자열, 시작 위치값): 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽으라는 뜻
-
SUBSTR(원본 문자열,시작 위치값,가져올 길이값): 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽고, 가져올 길이값 (3이라면 3글자를) 가져오라는 뜻
DATETIME에서 DATE로 형 변환(lev2)
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') as '날짜'
FROM ANIMAL_INS
ORDER BY 1- DATE_FORMAT(날짜, 형식): 날짜를 해당 형식으로 출력
- 참고 %Y: 네 자리 년도, %m: 숫자 월, %d: 숫자 일
중성화 여부 파악하기(lev2)
SELECT ANIMAL_ID
, NAME
, (CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' or SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
ELSE 'X' END) as '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID- CASE: 조건에 따라서 값 지정해주는 구문
- 형식
CASE WHEN 조건1 THEN 결과값1 WHEN 조건2 THEN 결과값2 ELSE 결과값3 END
- 형식
이름에 el이 들어가는 동물 찾기(lev2)
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME- LIKE: 특정 문자 포함되어 있는 것만 추출
- 참고 hi로 시작하는 데이터 검색: like 'hi%', hi로 끝나는 데이터 검색: like '%hi', hi가 들어가는 데이터 검색: '%hi%'
루시와 엘라 찾기(lev2)
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')- IN: WHERE 조건에 사용할 수 있으며, WHERE 뒤에 쓴 컬럼과 IN에 나열한 조건들 중 일치하는 행을 가져온다는 뜻 (여기서 나열한 조건들은 OR조건으로 검색: 조건1이거나 조건2이거나 조건3이거나 해당 조건에 일치하는 모든 내용을 추출)
조건별로 분류하여 주문상태 출력하기(lev3)
SELECT ORDER_ID
, PRODUCT_ID
, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') as OUT_DATE
, (CASE WHEN DATE_FORMAT(OUT_DATE, '%Y-%m-%d') <= '2022-05-01' then '출고완료'
WHEN OUT_DATE IS NULL THEN '출고미정'
ELSE '출고대기' END) as '출고여부'
FROM FOOD_ORDER
ORDER BY 1오랜 기간 보호한 동물(2)(lev3)
SELECT AI.ANIMAL_ID, AI.NAME
FROM ANIMAL_OUTS AO
INNER JOIN ANIMAL_INS AI
on AI.ANIMAL_ID = AO.ANIMAL_ID
ORDER BY DATEDIFF(AO.DATETIME, AI.DATETIME) DESC
LIMIT 2취소되지 않은 진료 예약 조회하기(lev4)
SELECT APNT_NO, PT_NAME, A.PT_NO, A.MCDP_CD, DR_NAME, APNT_YMD
FROM APPOINTMENT A
LEFT JOIN PATIENT P
on P.PT_NO = A.PT_NO
LEFT JOIN DOCTOR D
on D.DR_ID = A.MDDR_ID
WHERE APNT_CNCL_YN = 'N'
and DATE_FORMAT(APNT_YMD, '%Y-%m-%d') = '2022-04-13'
and A.MCDP_CD = 'CS'
ORDER BY 6자동차 대여 기록 별 대여 금액 구하기(lev4)
WITH CAR_HISTORY as (
SELECT HISTORY_ID
, CH.CAR_ID
, DATEDIFF(END_DATE, START_DATE)+1 as DURATION
, DAILY_FEE
, (CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 AND DATEDIFF(END_DATE, START_DATE)+1 <30 THEN '7일 이상'
WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 AND DATEDIFF(END_DATE, START_DATE)+1 <90 THEN '30일 이상'
WHEN DATEDIFF(END_DATE, START_DATE)+1 >=90 THEN '90일 이상'
ELSE '7일 미만' END) as DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CH
LEFT JOIN CAR_RENTAL_COMPANY_CAR CC
on CH.CAR_ID = CC.CAR_ID
WHERE CAR_TYPE = '트럭'
)
SELECT HISTORY_ID
, (CASE WHEN CH.DURATION_TYPE = '7일 미만' THEN DAILY_FEE*DURATION
ELSE CAST((DAILY_FEE*(1-DISCOUNT_RATE*0.01))*DURATION as SIGNED INTEGER) END) AS FEE
FROM CAR_HISTORY CH
LEFT JOIN (SELECT *
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN CP
WHERE CAR_TYPE = '트럭') CP
on CH.DURATION_TYPE = CP.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC
- 참고: WITH문으로 먼저 대여 기록 테이블 생성 후(1-3), 이를 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 테이블 조인해 데이터 추출(4-5)
1> 대여 기록 정보 테이블과 자동차 정보 테이블 조인
2> 대여 기록 기간(대여 종료일-대여 시작일+1) 뜻하는 DURATION 컬럼 생성
3> 대여 기록 기간에 따라 DURATION_TYPE(7일 이상, 30일 이상, 90일 이상, 7일 미만) 컬럼 생성
4> 이후 이 테이블을 할인 정책 정보 테이블과 조인
5> DURATION_TYPE이 7일 미만이면 일별 비용x기간으로 비용 계산, 이외는 DURATION_TYPE에 따라 할인율 적용해 (일별 비용x할인율)x기간으로 비용 계산
성장 일기