Chapter 6-36. Mini Project : 예스24 베스트셀러 데이터 수집 후 데이터 분석하기(7/7)

 

📌 개념 정리

이번 포스팅에서는 SQL의 고급 쿼리를 사용하여 복잡한 데이터 분석을 어떻게 해결할 수 있는지에 대해 설명합니다. 주어진 예시를 통해 여러 가지 문제를 SQL 쿼리로 해결하는 방법을 단계별로 소개할 것입니다. 쿼리 작성 시 JOIN, 서브쿼리, 그룹화 등 다양한 SQL 기능을 어떻게 활용할 수 있는지 학습할 수 있습니다.

---

🚦 난이도 있는 문제

1. 출판사별 평균 판매지수가 가장 높은 저자 찾기

문제 설명: 각 출판사별로 평균 판매지수가 가장 높은 저자의 이름과 그 평균 판매지수를 조회하세요.

SELECT publisher, author, AVG(sales) as avg_sales
FROM Books
GROUP BY publisher, author
ORDER BY publisher, avg_sales DESC

설명: 이 쿼리는 **출판사(publisher)**와 **저자(author)**별로 **평균 판매지수(avg_sales)**를 계산합니다. GROUP BY publisher, author를 사용해 각 출판사와 저자에 대한 그룹을 만들고, AVG(sales)로 판매지수의 평균을 구합니다. ORDER BY로 출판사별로 평균 판매지수가 높은 저자 순으로 정렬합니다.

2. 리뷰 수가 평균보다 높으면서 가격이 평균보다 낮은 책 조회

문제 설명: 리뷰 수와 가격의 전체 평균을 계산한 후, 이보다 리뷰 수는 높고 가격은 낮은 책들을 조회하세요.

SELECT title, review, price
FROM Books
WHERE review > (SELECT AVG(review) FROM Books) AND price < (SELECT AVG(price) FROM Books);

설명: 이 쿼리는 **리뷰 수(review)**와 **가격(price)**의 평균값을 서브쿼리 (SELECT AVG(review) FROM Books)와 (SELECT AVG(price) FROM Books)를 통해 계산합니다. 그 후, 리뷰 수는 평균보다 높고, 가격은 평균보다 낮은 책을 조회하는 조건을 설정합니다.

3. 가장 많은 종류의 책을 출판한 저자 찾기

문제 설명: 서로 다른 제목의 책을 가장 많이 출판한 저자를 찾으세요.

SELECT author, COUNT(DISTINCT title) as num_books
FROM Books
GROUP BY author
ORDER BY num_books DESC
LIMIT 1;

설명: COUNT(DISTINCT title)을 사용하여 각 **저자(author)**가 출판한 서로 다른 책 제목의 수를 셉니다. GROUP BY author로 저자별로 그룹화하고, ORDER BY num_books DESC로 가장 많은 책 종류를 출판한 저자를 찾습니다. LIMIT 1은 가장 많은 책을 출판한 저자만 가져옵니다.

4. 각 저자별로 가장 높은 판매지수를 기록한 책 조회

문제 설명: 각 저자별로 가장 높은 판매지수를 기록한 책의 제목과 그 판매지수를 조회하세요.

SELECT author, MAX(sales) as max_sales
FROM Books
GROUP BY author;

설명: MAX(sales)를 사용하여 각 **저자(author)**별로 가장 높은 판매지수를 조회합니다. GROUP BY author로 저자별로 그룹화하여, 각 저자의 최고 판매지수만 조회합니다.

5. 연도별 출판된 책 수와 평균 가격 비교

문제 설명: 연도별로 출판된 책의 수와 그 해 출판된 책들의 평균 가격을 비교 분석하세요.

SELECT YEAR(publishing) as year, COUNT(*) as num_books, AVG(price) as avg_price
FROM Books
GROUP BY year;

설명: YEAR(publishing)으로 출판 연도를 추출하고, COUNT(*)로 각 연도별 책 수를 계산합니다. AVG(price)로 해당 연도의 평균 가격을 구합니다. GROUP BY year로 연도별로 데이터를 그룹화하여 연도별 책 수와 가격을 비교합니다.

6. 출판사가 같은 책들 중 평점 편차가 가장 큰 출판사 찾기

문제 설명: 같은 출판사에서 출판된 책들 중 평점 편차가 가장 큰 출판사와 그 편차를 조회하세요.

SELECT publisher, MAX(rating) - MIN(rating) as rating_difference
FROM Books
GROUP BY publisher
ORDER BY rating_difference DESC
LIMIT 1;

설명: MAX(rating) - MIN(rating)을 사용하여 각 출판사의 책들에 대한 평점 차이를 계산합니다. GROUP BY publisher로 출판사별로 그룹화하고, ORDER BY rating_difference DESC로 평점 편차가 큰 출판사를 찾습니다. LIMIT 1은 가장 큰 평점 차이를 가진 출판사만 반환합니다.

7. 특정 저자의 책들 중 판매지수 대비 평점이 가장 높은 책 찾기

문제 설명: 특정 저자의 책들 중 판매지수 대비 평점이 가장 높은 책의 제목과 그 비율을 조회하세요.

SELECT title, rating / sales as ratio
FROM Books
WHERE author = '특정 저자'
ORDER BY ratio DESC
LIMIT 1;

설명: 특정 저자에 대해 rating / sales를 계산하여 판매지수 대비 평점 비율을 구합니다. ORDER BY ratio DESC로 가장 높은 비율을 가진 책을 찾고, LIMIT 1로 가장 높은 책만 반환합니다.

---

🚦 결론

이 포스팅에서 다룬 다양한 SQL 고급 쿼리 예시들을 통해, 복잡한 데이터 분석을 어떻게 해결할 수 있는지 배웠습니다. 서브쿼리, 그룹화, 정렬 등 SQL의 고급 기능을 사용하여 더 효율적인 데이터 처리를 할 수 있습니다.

SQL을 활용한 데이터 분석은 실무에서 매우 중요한 작업이며, 각 쿼리의 목적에 맞게 적절히 활용하는 것이 중요합니다. 여러분도 이 예시들을 따라하며 자신만의 쿼리를 작성해보세요! 🚀