Database

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

Chansman 2025. 3. 25. 17:01

 

📌 개념 정리

이번 포스팅에서는 MySQL을 사용하여 데이터베이스에서 다양한 조인, 그룹화, 서브쿼리, 데이터 수정 및 관리 기능을 활용하는 방법을 설명합니다. 각 쿼리 예시와 설명을 통해 복잡한 데이터베이스 작업을 효율적으로 처리하는 방법을 배우게 됩니다.

🚦 조인 및 관계

저자별로 출판한 책의 수를 조회하세요

SELECT author, COUNT(*) FROM Books GROUP BY author;
  • 설명: 이 쿼리는 저자별로 출판한 책의 개수를 계산합니다. GROUP BY author로 저자별로 책을 그룹화하고, COUNT(*)로 각 그룹의 책 수를 계산합니다.

가장 많은 책을 출판한 출판사를 찾으세요

SELECT publisher, COUNT(*) AS num_books FROM Books GROUP BY publisher ORDER BY num_books DESC LIMIT 1;
  • 설명: 이 쿼리는 출판사별로 책을 그룹화하고, 각 출판사의 책 수를 계산한 후 가장 많은 책을 출판한 출판사를 찾습니다. ORDER BY를 사용해 내림차순으로 정렬하고, LIMIT 1로 가장 많은 책을 출판한 출판사만 반환합니다.

가장 높은 평균 평점을 가진 저자를 찾으세요

SELECT author, AVG(rating) AS avg_rating FROM Books GROUP BY author ORDER BY avg_rating DESC LIMIT 1;
  • 설명: 저자별로 평균 평점을 계산하여, 가장 높은 평균 평점을 가진 저자를 찾습니다. ORDER BY avg_rating DESC로 내림차순 정렬하여, LIMIT 1로 가장 높은 평점을 가진 저자를 반환합니다.

국내도서랭킹이 1위인 책의 제목과 저자를 조회하세요

SELECT title, author FROM Books WHERE ranking = 1;
  • 설명: 국내도서랭킹이 1위인 책을 조회합니다. WHERE ranking = 1로 조건을 지정하여 1위 책의 제목과 저자를 출력합니다.

판매지수와 리뷰 수가 모두 높은 상위 10개의 책을 조회하세요

SELECT title, sales, review FROM Books ORDER BY sales DESC, review DESC LIMIT 10;
  • 설명: 판매지수와 리뷰 수가 높은 상위 10개의 책을 조회합니다. ORDER BY sales DESC, review DESC로 판매지수와 리뷰 수를 기준으로 내림차순 정렬합니다.

가장 최근에 출판된 5권의 책을 조회하세요

SELECT title, publishing FROM Books ORDER BY publishing DESC LIMIT 5;
  • 설명: 가장 최근에 출판된 5권의 책을 조회합니다. ORDER BY publishing DESC로 출판일을 기준으로 내림차순 정렬하여, LIMIT 5로 가장 최근 5권의 책을 반환합니다.

🚦 집계 및 그룹화

저자별 평균 평점을 계산하세요

SELECT author, AVG(rating) FROM Books GROUP BY author;
  • 설명: 저자별로 평균 평점을 계산합니다. GROUP BY를 사용하여 저자별로 그룹화하고, AVG(rating)을 사용하여 각 저자의 평균 평점을 계산합니다.

출판일별 출간된 책의 수를 계산하세요

SELECT publishing, COUNT(*) FROM Books GROUP BY publishing;
  • 설명: 출판일별로 출간된 책의 수를 계산합니다. GROUP BY publishing으로 출판일을 기준으로 그룹화하고, COUNT(*)를 사용하여 각 출판일에 출간된 책의 수를 계산합니다.

책 제목별 평균 가격을 조회하세요

SELECT title, AVG(price) FROM Books GROUP BY title;
  • 설명: 책 제목별로 평균 가격을 계산합니다. GROUP BY title로 제목별로 그룹화하고, AVG(price)를 사용하여 각 책 제목의 평균 가격을 계산합니다.

리뷰 수가 가장 많은 상위 5권의 책을 찾으세요

SELECT title, review FROM Books ORDER BY review DESC LIMIT 5;
  • 설명: 리뷰 수가 가장 많은 상위 5권의 책을 조회합니다. ORDER BY review DESC로 리뷰 수를 기준으로 내림차순 정렬하여, LIMIT 5로 상위 5권의 책을 반환합니다.

국내도서랭킹 별 평균 리뷰 수를 계산하세요

SELECT ranking, AVG(review) FROM Books GROUP BY ranking;
  • 설명: 국내도서랭킹별로 평균 리뷰 수를 계산합니다. GROUP BY ranking으로 국내도서랭킹별로 그룹화하고, AVG(review)로 각 랭킹에 대한 평균 리뷰 수를 계산합니다.

🚦 서브쿼리 및 고급 기능

평균 평점보다 높은 평점을 받은 책들을 조회하세요

SELECT title, rating FROM Books WHERE rating > (SELECT AVG(rating) FROM Books);
  • 설명: 서브쿼리를 사용하여 평균 평점보다 높은 평점을 가진 책들을 조회합니다. 서브쿼리 (SELECT AVG(rating) FROM Books)는 평균 평점을 계산하고, WHERE rating >로 그보다 높은 평점을 가진 책들을 찾습니다.

평균 가격보다 비싼 책들의 제목과 가격을 조회하세요

SELECT title, price FROM Books WHERE price > (SELECT AVG(price) FROM Books);
  • 설명: 서브쿼리 (SELECT AVG(price) FROM Books)를 사용하여 평균 가격을 계산하고, 그보다 비싼 책들을 조회합니다.

가장 많은 리뷰를 받은 책보다 많은 리뷰를 받은 다른 책들을 조회하세요

SELECT title, review FROM Books WHERE review > (SELECT MAX(review) FROM Books);
  • 설명: 서브쿼리 (SELECT MAX(review) FROM Books)를 사용하여 가장 많은 리뷰를 받은 책을 찾아, 그보다 더 많은 리뷰를 받은 책들을 조회합니다.

평균 판매지수보다 낮은 판매지수를 가진 책들을 조회하세요

SELECT title, sales FROM Books WHERE sales < (SELECT AVG(sales) FROM Books);
  • 설명: 서브쿼리 (SELECT AVG(sales) FROM Books)를 사용하여 평균 판매지수를 계산하고, 그보다 낮은 판매지수를 가진 책들을 조회합니다.

가장 많이 출판된 저자의 책들 중 최근에 출판된 책을 조회하세요

SELECT title, publishing FROM Books WHERE author = (SELECT author FROM Books GROUP BY author ORDER BY COUNT(*) DESC LIMIT 1) ORDER BY publishing DESC LIMIT 1;
  • 설명: 서브쿼리에서 가장 많은 책을 출판한 저자를 찾고, 그 저자의 가장 최근에 출판된 책을 조회합니다.

🚦 데이터 수정 및 관리

특정 책의 가격을 업데이트하세요

UPDATE Books SET price = 30000 WHERE title = 'New Book Title';
  • 설명: UPDATE 쿼리를 사용하여 Books 테이블에서 특정 책의 가격을 변경합니다.

특정 저자의 책 제목을 변경하세요

UPDATE Books SET title = 'Updated Title' WHERE author = '홍길동';
  • 설명: UPDATE 쿼리를 사용하여 특정 저자의 책 제목을 변경합니다.

판매지수가 가장 낮은 책을 데이터베이스에서 삭제하세요

DELETE FROM Books WHERE sales = (SELECT MIN(sales) FROM Books);
  • 설명: DELETE 쿼리를 사용하여 판매지수가 가장 낮은 책을 삭제합니다.

특정 출판사가 출판한 모든 책의 평점을 1점 증가시키세요

UPDATE Books SET rating = rating + 1 WHERE publisher = '민음사';
  • 설명: 특정 출판사가 출판한 모든 책의 평점을 1점 증가시킵니다.

🚦 데이터 분석 예제

저자별 평균 평점 및 판매지수를 분석하여 인기 있는 저자를 확인합니다

SELECT author, AVG(rating) as avg_rating, AVG(sales) as avg_sales FROM Books GROUP BY author;
  • 설명: 저자별로 평균 평점과 평균 판매지수를 계산하여 인기 있는 저자를 확인합니다.

출판일에 따른 책 가격의 변동 추세를 분석합니다

SELECT publishing, AVG(price) as avg_price FROM Books GROUP BY publishing;
  • 설명: 출판일별로 책 가격의 변동 추세를 분석합니다.

출판사별 출간된 책의 수와 평균 리뷰 수를 비교 분석합니다

SELECT publisher, COUNT(*) as num_books, AVG(review) as avg_review FROM Books GROUP BY publisher;
  • 설명: 출판사별로 책의 수와 평균 리뷰 수를 분석합니다.

국내도서랭킹과 판매지수의 상관관계를 분석합니다

SELECT ranking, AVG(sales) as avg_sales FROM Books GROUP BY ranking;
  • 설명: 국내도서랭킹별로 평균 판매지수를 분석하여, 랭킹에 따른 판매지수의 상관관계를 확인합니다.

가격 대비 리뷰 수와 평점의 관계를 분석하여 가성비 좋은 책을 찾습니다

SELECT price, AVG(review) as avg_review, AVG(rating) as avg_rating FROM Books GROUP BY price;
  • 설명: 가격 대비 리뷰 수와 평점의 관계를 분석하여 가성비가 좋은 책을 찾습니다.