2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요. (링크)
처음 코드는 아래와 같았다
분명 정확하게 짰다고 생각하는데 계속 오답 처리가 되었고, 몇분을 더 들여다봐도 도저히 내가 놓친 부분을 모르겠어서 다른 사람들의 답안과 비교해보기로 했다
SELECT
b.author_id
, a.author_name
, b.category
, b.price * s.sales as 'total_sales'
FROM
author a
INNER JOIN book b ON a.author_id = b.author_id
INNER JOIN book_sales s ON b.book_id = s.book_id
WHERE
(YEAR(s.sales_date)=2022)
and (MONTH(s.sales_date)=1)
GROUP BY
1
, 3
ORDER BY
1
, 3 desc
;이유는 한 부분이었는데,
매출액에 sum()을 붙이지 않았던 것.
SELECT
b.author_id
, a.author_name
, b.category
, sum(b.price * s.sales) as 'total_sales'
FROM
author a
INNER JOIN book b ON a.author_id = b.author_id
INNER JOIN book_sales s ON b.book_id = s.book_id
WHERE
(YEAR(s.sales_date)=2022)
and (MONTH(s.sales_date)=1)
GROUP BY
1
, 3
ORDER BY
1
, 3 desc
;문제에서 '매출액 합계'가 아닌 '매출액'이라고 언급해서 sum을 생각하지 않았는데,
문제에 적혀있는 바가 아니라 문제의 의미를 생각해보면 당연히 sum을 붙여야하는 것이 맞았다
'2022년 1월 매출액'이라는 의미는 2022년 1월 매출의 합계를 의미하는 것이기에...
문제의 의미를 자꾸 놓쳐서 제대로 된 정답을 적지 못하는 것이 아쉽다.
적혀있는 글자 그대로가 아니라 그 의미를 읽어내는 것이 필요할텐데
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요. (링크)
초안은 아래와 같다
'총 대여 횟수가 5회 이상인 자동차들'이라는 조건과 '특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외'를 충족시키지 못했다
SELECT
MONTH(start_date) as 'c_month'
, car_id
, count(1) as 'records'
FROM
car_rental_company_rental_history
WHERE
(YEAR(start_date) = 2022)
and (MONTH(start_date) between 8 and 10)
GROUP BY
1
, 2
HAVING
count(1) > 0
ORDER BY
1
, car_id desc
;2안.
먼저 서브쿼리를 통해 2022년 8, 9, 10월의 car_id를 뽑아내면서 시작했다
SELECT
a.c_month
, c.car_id
, count(1) as 'records'
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY c
INNER JOIN (
SELECT
car_id
, MONTH(start_date) as 'c_month'
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE #2022년 8, 9, 10월 제한
(YEAR(start_date) = 2022)
and (MONTH(start_date) in (8, 9, 10))
GROUP BY
2
HAVING # 월로 group
count(car_id) >= 5
) a ON c.car_id = a.car_id
GROUP BY
1
, 2
ORDER BY
1
, 2 desc
;최종안.
2안에서 놓쳤던 부분은, 서브쿼리에서 car_id를 뽑아내서 INNER JOIN을 한 것까지는 좋았지만,
정작 메인쿼리에서 WHERE절을 생략했다는 점이다
처음에는 "서브쿼리에서 기간 제한하고 뽑았으니 괜찮겠지"라고 생각했으나,
다시 생각해보니 서브쿼리에서는 car_id만 반환하고 있으므로, 메인쿼리는 그 car_id만 참고할 뿐 날짜 관련 제한 조건이 없으니, 그 car_id와 관련해 8,9,10월 이외의 데이터도 조회하고 있던 것이다
그래서 메인쿼리에도 서브쿼리에 적었던 것과 동일하게 WHERE절을 작성했다
SELECT
MONTH(c.start_date) as 'c_month'
, c.car_id
, count(1) as 'records'
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY c
INNER JOIN (
SELECT
car_id
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE #2022년 8, 9, 10월 제한
(YEAR(start_date) = 2022)
and (MONTH(start_date) in (8, 9, 10))
GROUP BY
1
HAVING # 기간 내 총 대여 횟수가 5회 이상
count(car_id) >= 5
) a ON c.car_id = a.car_id
WHERE
(YEAR(start_date) = 2022)
and (MONTH(start_date) in (8, 9, 10))
GROUP BY
1
, 2
ORDER BY
1
, 2 desc
;WHERE절에도 서브쿼리를 활용할 수 있다
마지막으로 다른 사람들 답안 중 참고할만한 쿼리 하나
SELECT Month(start_date) MONTH,
car_id,
Count(*) RECORDS
FROM car_rental_company_rental_history
WHERE car_id IN(SELECT car_id
FROM car_rental_company_rental_history
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY car_id
HAVING Count(*) >= 5)
AND start_date BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY month,
car_id
ORDER BY 1,
2 DESCFROM에 서브쿼리를 작성한 나와 다르게 WHERE에 서브쿼리를 작성한 케이스
뭐가 더 낫다라는 것보다는, 서브쿼리를 사용한 이유가 '주어진 날짜에서 5회 이상 판매된 차량'을 찾는 것이었기에, 조건에 서브쿼리를 사용할 수 있다는 점을 스스로 상기시키고 넘어가고 싶었다
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요. (링크)
이게 맞나 의심스러운 초안
일단 정답으로는 판정되었다
SELECT
m.member_name
, r.review_text
, date_format(r.review_date, '%Y-%m-%d') as 'n_review_date'
FROM
MEMBER_PROFILE m
INNER JOIN (
SELECT
*
FROM
(
SELECT
# rest_review에서 count(1)의 ranking 반환
rank() over (order by aa.mem_cnt desc) as 'ranking'
, member_id
FROM
(
SELECT
member_id
, count(1) as 'mem_cnt'
FROM
rest_review
GROUP BY
member_id
) aa
) bb
WHERE
ranking = 1
) cc ON m.member_id = cc.member_id
INNER JOIN REST_REVIEW r ON cc.member_id = r.member_id
ORDER BY
r.review_date
, r.review_text
;서브쿼리 aa에서 member_id별로 개수를 구하고
서브쿼리 bb에서 여기에 rank() 함수로 순위를 부여
그렇게 나온 cc 테이블에서 ranking=1로 가장 개수가 많은 경우만 조회
그리고 주어진 2개 테이블과 가상 테이블 cc를 INNER JOIN 해주면 cc 덕분에 가장 개수가 많은 케이스만 남게 되고
이제 편하게 SELECT만 하면 된다
이렇게 돌아돌아 코드를 쓰게 된 이유는,
WHERE 절에 집계함수를 쓸 수 없었기 때문이다
SELECT
member_id
, count(1) as 'cnt'
FROM
REST_REVIEW
GROUP BY
1
ORDER BY
2 desc처음에 이렇게 코드를 짰었는데,
이걸 WHERE절 서브쿼리로 넣어서 WHERE count(1) = max (서브쿼리)로 하려고 했었다
그런데 SQL의 작동 순서를 떠올려보면 WHERE절 이후에 GROUP BY가 있기 때문에,
WHERE 절에는 집계함수를 넣어도 정상적으로 작동될 리가 없다 (...)
그래서 뭐...
어떻게든 돌아 돌아 해결은 하긴 했는데,
정말 이게 최선일까? 보다 깔끔하고 단순하게 풀 수 있는 방법은 없었을까?
미리 쿼리를 작성해두고 가져다 쓴다! WITH 구문
다른 사람들 답안을 보다가 괜찮은 코드가 있어서 가져와보았다
WITH MAX_REVIEW AS ( -- 회원이 작성한 가장 많은 리뷰 수
SELECT COUNT(*) COUNT
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY 1 DESC
LIMIT 1
),
MAX_REVIEW_MEMBER AS ( -- 가장 많은 리뷰를 작성한 회원(들)
SELECT MEMBER_ID
FROM REST_REVIEW R
GROUP BY MEMBER_ID
HAVING COUNT(*) = (SELECT * FROM MAX_REVIEW)
)
SELECT P.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_PROFILE P JOIN MAX_REVIEW_MEMBER M
ON P.MEMBER_ID = M.MEMBER_ID
JOIN REST_REVIEW R ON R.MEMBER_ID = M.MEMBER_ID
ORDER BY 3, 2
# 출처: https://20240228.tistory.com/269일단 이를 위해서는 WITH 구문을 알아야 하는데,
쉽게 말하면 서브쿼리를 정의하고 사용하게 해주는 구문이다. (참고)
Python의 def: 를 생각하면 될 듯.
위의 코드를 분석해보면,
1) 먼저 MAX_REVIEW를 정의하면서 '가장 개수가 많은 리뷰의 수'를 반환하고
2) (1)과 동일한 수의 리뷰 개수를 작성한 회원 id를 조회한다
3) 그 후 이렇게 뽑아낸 회원 id를 통해 문제에서 제시하는 칼럼들을 조회하면 끝!
또한 이 방법을 rank()함수를 사용하면 더 축약할 수 있는데,
WITH MEMBER_RANK AS (
SELECT MEMBER_ID, RANK() OVER (ORDER BY COUNT(MEMBER_ID) DESC) RNK
FROM REST_REVIEW
GROUP BY MEMBER_ID
)
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_RANK RA JOIN MEMBER_PROFILE P
ON RA.MEMBER_ID = P.MEMBER_ID
JOIN REST_REVIEW R ON R.MEMBER_ID = P.MEMBER_ID
WHERE RA.RNK = 1
ORDER BY 3, 2
# 출처: https://20240228.tistory.com/269...위와 같다.
슬슬 한 눈에 보기에는 어지럽군...
WITH MEMBER_COUNT_RANK AS (
SELECT
member_id
, rank() over (order by count(member_id) desc) as 'rnk'
FROM
REST_REVIEW
GROUP BY
member_id
)
SELECT
p.member_name
, r.review_text
, date_format(r.review_date, '%Y-%m-%d') as 'review_date'
FROM
MEMBER_PROFILE p
INNER JOIN MEMBER_COUNT_RANK cr ON p.member_id = cr.member_id
INNER JOIN REST_REVIEW r on p.member_id = r.member_id
WHERE
cr.rnk = 1
ORDER BY
r.review_date
, r.review_text
;MEMBER_COUNT_RANK에서 rank() 쓰고 나서 group by 써줘야 했다. 따로 partition이 없다보니 그런듯
