문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

📌 테이블 :

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

풀이

추측) 

2021년에 가입한 사람수를 변수로 먼저 대입.

그리고 2021년에 가입한 사람들 리스트를 대상으로 온라인 주문을 join해서 일치하는 것을 조회.

년, 월 기준으로 그룹화한 다음에 같은 달에 똑같은 사람이 주문했을 경우를 생각해서 중복제거로 DISTINCT 적용하고

count로 주문회원수, 2021가입자 수 변수에서 count 비율을 구해 출력하면 될 듯!

쿼리) 

* 1차) 실패

SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');

SELECT DISTINCT
    YEAR(O.SALES_DATE) AS YEAR,
    MONTH(O.SALES_DATE) AS MONTH,
    COUNT(*) AS PUCHASED_USERS,
    ROUND(COUNT(*) / @CNT, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE AS O
INNER JOIN (SELECT *
            FROM USER_INFO
            WHERE YEAR(JOINED) = '2021') AS U
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC

* 2차) 성공

SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');

SELECT 
    YEAR, 
    MONTH, 
    COUNT(*) AS PUCHASED_USERS,
    ROUND(COUNT(*)/@CNT, 1) AS PUCHASED_RATIO
FROM (SELECT DISTINCT
        YEAR(SALES_DATE) AS YEAR,
        MONTH(SALES_DATE) AS MONTH,
        USER_ID
      FROM ONLINE_SALE) AS O                   # 년,달마다 구매한 회원 리스트  
INNER JOIN (SELECT USER_ID
            FROM USER_INFO
            WHERE YEAR(JOINED) = '2021') AS U  # 2021년에 가입한 회원 리스트
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC

리뷰) 

1차에서 중복제거랑 그룹화를 같이하면서 의도치 않은 결과가 나왔다. 

DISTINCT가 실행되는 기준을 생각해보면

1차에서는 중복이 제거되지 않은 상태로 먼저 count과 비율이 다 계산되고난 이후에 중복을 제거하는 거라,

같은 달에 또 구매한 구매자를 거르지 못했다.

이 중복을 먼저 제거하기 위해 

2차에서는 년과 달 기준으로 구매한 회원을 중복없이 리스트화 시킬 수 있게 질의해서 먼저 정리하고 

이를 기준으로 count해서 정확한 결과가 나오게 했다!

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

📌 테이블 :

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

풀이

쿼리) 

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 @변수명 = 변수값; 

으로 @(앳사인)뒤에 변수명을 정해주고 해당 변수에 값을 선언하면 된다. 그리고 이후의 SELECT문과 구분되기 위해 ;(세미콜론)은 필수다. 

이렇게 선언하고나서 SELECT문에서 @변수명으로 사용이 가능하게되고

만약 SELECT문 안에서 변수에 값을 다시 대입해야 한다면 단순히 = (이퀄)만 사용하는 게 아니라 := (콜론 + 이퀄)을 해야 변한 값이 대입이 된다. 

그래서 이를 참고해서 아래 질의문을 파악해보면 요렇게 된다.

SET @HOUR = -1; 	# @HOUR이란 변수에 -1을 대입
SELECT 
    (@HOUR := @HOUR + 1) AS HOUR,	# @HOUR에 @HOUR에서 +1한 값을 다시 대입해서 0부터 출력
    (SELECT 
        COUNT(*)	# 7라인에 일치하는 시간 것만 조회하니 전체조회 COUNT하면 그시간대 입양된 수
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME) = @HOUR) AS COUNT	# 그리고 조건에서도 이 @변수와 일치하는 조건
FROM ANIMAL_OUTS
WHERE @HOUR < 23;	# 이 조건으로 @HOUR은 22까지지만 SELECT문 안에서 +1하는 구문때문에 23까지 출력

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

📌 테이블 :

다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

USED_GOODS_FILE 테이블은 다음과 같으며 FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID는 각각 파일 ID, 파일 확장자, 파일 이름, 게시글 ID를 의미합니다.

풀이

추측) 

가장 조회수가 많이 나오는 글 ID를 먼저 골라오는 게 공수를 줄일 수 있을 듯 하다.

글이랑 첨부파일 먼저 매칭 시키면 일이 커지니까.

그래서 조회수 최대값을 조회하고 이에 해당하는 글 ID를 찾아 그에 해당하는 파일들을 찾는다.

그리고 FILE_PATH가 파일첨부 테이블의 컬럼들을 조합해서 파일경로로 만들어 출력하면 될듯하다. 

쿼리) 

* 1차) 서브쿼리 이용

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE 
WHERE BOARD_ID IN (SELECT BOARD_ID
                    FROM USED_GOODS_BOARD
                    WHERE VIEWS IN (SELECT MAX(VIEWS)
                                    FROM USED_GOODS_BOARD))
ORDER BY FILE_ID DESC

* 2차) JOIN 이용

SELECT CONCAT('/home/grep/src/', BOARD.BOARD_ID, '/', FILE.FILE_ID, FILE.FILE_NAME, FILE.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE AS FILE
LEFT JOIN USED_GOODS_BOARD AS BOARD
ON FILE.BOARD_ID = BOARD.BOARD_ID
WHERE BOARD.VIEWS IN (SELECT MAX(VIEWS) 
               		  FROM USED_GOODS_BOARD)
ORDER BY FILE.FILE_ID DESC

리뷰) 

조금 궁금한게 서브쿼리 2번으로 필터링하는거랑..

BOARD랑 FILE이랑 매칭하고 WHERE로 최대값 걸러내면 뭐가 더 성능적으로 좋은걸까?

쿼리문 실행 순서가 아래와 같은 걸로 아는데 

from [ > on > join ] > where > group by [ > having ] > select > order by > limit

WHERE로 필터링 하기 전에 ON하고 JOIN해서 두 테이블을 매칭하면

서브쿼리로 조회글 아이디 하나 찾아오는 거 보다 더 공수가 들거 같은데.. 

라는 내생각과 다르게 찾아보니 충격적인 결과가!

서브쿼리의 성능적 문제는 서브쿼리와 컬럼을 비교할때마다 내부 쿼리가 실행된다는 점이다. (😧‼️)

쿼리를 반복해서 날리니 성능이 낮아질 수 밖에 없고,

그런 문제때문에 최근 MySQL은 사용자가 서브쿼리문을 사용하면 자체적으로 조인문으로 변환시켜 실행하도록 업데이트되어 결국 둘다 실행계획이 같았던 것. 내부적으로 변환해줘도 꼭 필요한 경우가 아니라면 서브쿼리는 남용하지 않는 것이 좋다고 한다. 주의하도록!!!

참고자료

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

📌 테이블 :

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

풀이

추측) 

예약한 날의 조건에 맞게 먼저 필터링해서 ROW수를 줄이고,

그 3개월 안에 5번 이상인 CAR_ID를 질의한 후에 

그 CAR_ID 대상으로 ROW수를 줄인 결과에서 해당 기간 동안을 달기준, 차기준으로 GROUP화 시켜 그룹 함수 카운트로달마다 몇번 렌트가 됬는지 질의하면 될 듯하다. 

쿼리) 

* 1차) 

WITH F AS 
(
SELECT HISTORY_ID, CAR_ID, MONTH(START_DATE) AS MONTH
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE DATE_FORMAT(START_DATE,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
)

SELECT MONTH, CAR_ID, COUNT(CAR_ID) AS RECORDS
FROM F
WHERE CAR_ID IN (SELECT CAR_ID 
                 FROM F 
                 GROUP BY CAR_ID
                 HAVING COUNT(CAR_ID) >= 5)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC

* 2차) 서브쿼리를 JOIN으로 교체

WITH F AS 
(
SELECT HISTORY_ID, CAR_ID, MONTH(START_DATE) AS MONTH
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE DATE_FORMAT(START_DATE,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
)

SELECT F.MONTH, F.CAR_ID, COUNT(F.CAR_ID) AS RECORDS
FROM F
INNER JOIN (SELECT CAR_ID 
            FROM F 
            GROUP BY CAR_ID
            HAVING COUNT(CAR_ID) >= 5) AS A
ON F.CAR_ID = A.CAR_ID
GROUP BY F.MONTH, F.CAR_ID
ORDER BY F.MONTH ASC, F.CAR_ID DESC

리뷰) 

하다 보니 "2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력" 이분에서 8월부터 10월까지의 결과를 두번 써야할 일이 생겼다.

3개월간 총 5회 렌트된 차를 검색할 때 한번

3개월간 기록을 달마다 차마다 렌트 카운트 해줄 때 한번

그래서 이번엔 임시 테이블을 써서 2번 반복안하고 한번의 결과물로 SELECT문에 두번 쓰는 걸로!

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

📌 테이블 :

다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 저자 정보(AUTHOR) 테이블입니다.

BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

AUTHOR 테이블은 도서의 저자의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

BOOK_SALES 테이블은 각 도서의 날짜 별 판매량 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

풀이

추측) 

일단 BOOK_SALES에서 2022년 1월 조회건 수만 출력하여 ROW수를 줄임

그 판매내역을 각각 BOOK_ID로 그룹화해 책마다의 판매고를 먼저 계산

그리고 나서 책정보와 작가 정보를 INNNER JOIN해서 연결하는 방식으로 하면 효율적일 듯 하다.

또, 전에 봤던 임시 테이블을 한번 적용해보면 좋을지도!

쿼리) 

* 1차) 

WITH S AS 
(
    SELECT BOOK_ID, SUM(SALES) AS SALES
    FROM BOOK_SALES 
    WHERE SALES_DATE LIKE '2022-01%'
    GROUP BY BOOK_ID
)

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(S.SALES * B.PRICE) AS TOTAL_SALES
FROM S
INNER JOIN BOOK AS B ON B.BOOK_ID = S.BOOK_ID
INNER JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC

리뷰) 

미리 테이블을 임시로 만들어 둠으로써 가독성이 굉장히 좋아졌다. 

근데 임시 테이블이 성능적으로는 기존에 FROM안에 쓰는 것과 어떻게 다를까 싶어 찾아보니

이 임시테이블은 temp라는 임시 테이블에 저장되기 때문에 한번만 사용하는 테이블일 경우 오히려 오버헤드 요소!

임시테이블을 쓰는 것은 동일한 SQL이 반복되어서 사용될 때 성능을 높일수 있다.

이 점을 기억해서 사용하자!