<jupyter_start><jupyter_text>Анализ базы данных методом запросов SQL ***Цели исследования:**** Проанализировать базу данных для того чтобы сформулировать ценностное предложение для нового продукта* Изучить информацию о книгах, издательствах, авторах и пользовательских обзоров книг.***Описание данных*****Таблица `books`**Содержит данные о книгах:- `book_id` — идентификатор книги;- `author_id` — идентификатор автора;- `title` — название книги;- `num_pages` — количество страниц;- `publication_date` — дата публикации книги;- `publisher_id` — идентификатор издателя.**Таблица `authors`**Содержит данные об авторах:- `author_id` — идентификатор автора;- `author` — имя автора.**Таблица `publishers`**Содержит данные об издательствах:- `publisher_id` — идентификатор издательства;- `publisher` — название издательства;**Таблица `ratings`**Содержит данные о пользовательских оценках книг:- `rating_id` — идентификатор оценки;- `book_id` — идентификатор книги;- `username` — имя пользователя, оставившего оценку;- `rating` — оценка книги.**Таблица `reviews`**Содержит данные о пользовательских обзорах на книги:- `review_id` — идентификатор обзора;- `book_id` — идентификатор книги;- `username` — имя пользователя, написавшего обзор;- `text` — текст обзора. Содержание1&nbsp;&nbsp;Импортирование библиотек2&nbsp;&nbsp;Изучение общей информации3&nbsp;&nbsp;Задания3.1&nbsp;&nbsp;Посчитайте, сколько книг вышло после 1 января 2000 года;3.2&nbsp;&nbsp;Для каждой книги посчитайте количество обзоров и среднюю оценку;3.3&nbsp;&nbsp;Определите издательство, которое выпустило наибольшее число книг толще 50 страниц — так вы исключите из анализа брошюры;3.4&nbsp;&nbsp;Определите автора с самой высокой средней оценкой книг — учитывайте только книги с 50 и более оценками;3.5&nbsp;&nbsp;Посчитайте среднее количество обзоров от пользователей, которые поставили больше 50 оценок.  Импортирование библиотек<jupyter_code>import pandas as pd
from sqlalchemy import create_engine
# устанавливаем параметры 
db_config = {'user': 'praktikum_student', # имяпользователя
             'pwd': '<PASSWORD>', # пароль
             'host': 'rc1b-wcoijxj3yxfsf3fs.mdb.yandexcloud.net', 'port': 6432,# портподключения
             'db': 'data-analyst-final-project-db'} # название базы данных
connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],  db_config['pwd'],  
                                                         db_config['host'],  db_config['port'],  
                                                         db_config['db'])
# сохраняем коннектор
engine = create_engine(connection_string, connect_args={'sslmode':'require'})<jupyter_output><empty_output><jupyter_text>Изучение общей информации<jupyter_code>books = '''SELECT * FROM books LIMIT 5'''
pd.io.sql.read_sql(books, con = engine)
authors = '''SELECT * FROM authors LIMIT 5'''
pd.io.sql.read_sql(authors, con = engine)
ratings = '''SELECT * FROM ratings LIMIT 5'''
pd.io.sql.read_sql(ratings, con = engine)
reviews = '''SELECT * FROM reviews LIMIT 5'''
pd.io.sql.read_sql(reviews, con = engine)
publishers = '''SELECT * FROM publishers LIMIT 5'''
pd.io.sql.read_sql(publishers, con = engine)<jupyter_output><empty_output><jupyter_text>Итого, у нас есть 5 таблиц: `books`, `authors`, `ratings`, `reviews` и `publishers`. В каждой из таблиц содержится информация о книгах, издательствах, авторах и пользовательских обзоров книг. Описание данных содержится в таблице брифа.      Задания  Посчитайте, сколько книг вышло после 1 января 2000 года;<jupyter_code>task_1 = '''
SELECT COUNT(publication_date)
FROM books
WHERE publication_date >= '2000-01-01'
'''
books_released = pd.io.sql.read_sql(task_1, con = engine)
print('Количество книг, вышедших после 1 января 2000 года: {:.0f}'.format(books_released.iloc[0][0]))<jupyter_output>Количество книг, вышедших после 1 января 2000 года: 821<jupyter_text>После 1 января 2000 года вышло 821 книг.  Для каждой книги посчитайте количество обзоров и среднюю оценку;<jupyter_code>task_2 = '''
SELECT books.title AS book_title,
    AVG (ratings.rating) AS avg_rating,
    COUNT (DISTINCT reviews.review_id) AS cnt_review
FROM books 
    INNER JOIN ratings ON ratings.book_id = books.book_id 
    INNER JOIN reviews ON reviews.book_id = ratings.book_id
GROUP BY books.book_id
ORDER BY cnt_review DESC, avg_rating DESC
'''

avg_rating = pd.io.sql.read_sql(task_2, con = engine)
avg_rating.head(10)
# task_2_1 = '''
# SELECT title, avg_rating, cnt_review 
# FROM books
# LEFT JOIN (SELECT ratings.book_id,
#     AVG (ratings.rating) AS avg_rating
#     FROM ratings
#     GROUP BY ratings.book_id) AS sub_1 ON books.book_id = sub_1.book_id     
# LEFT JOIN (SELECT reviews.book_id,
#     COUNT (reviews.text) AS cnt_review
#     FROM reviews
#     GROUP BY reviews.book_id) AS sub_2 ON books.book_id = sub_2.book_id 
# ORDER BY cnt_review DESC LIMIT 10
# '''

# avg_rating_2 = pd.io.sql.read_sql(task_2_1, con = engine)
# avg_rating_2.head(10)<jupyter_output><empty_output><jupyter_text>Наибольшее количество обзоров вышло по книге Twilight - 7. При этом рейтинг у этой книги ниже остальных с большим количеством обзоров - всего 3,66.  Определите издательство, которое выпустило наибольшее число книг толще 50 страниц — так вы исключите из анализа брошюры;<jupyter_code>task_3 = '''
SELECT publisher AS publisher_name,
    COUNT (books.book_id) AS cnt_books
FROM books 
    INNER JOIN publishers ON books.publisher_id = publishers.publisher_id
WHERE books.num_pages > 50
GROUP  BY publisher
ORDER  BY cnt_books DESC
'''

publisher_most_books = pd.io.sql.read_sql(task_3, con = engine)
publisher_most_books
print('Издательство', publisher_most_books.iloc[0, 0], 'выпустило наибольшее число книг -', 
      publisher_most_books.iloc[0, 1], 'шт. толще 50 страниц.'  )<jupyter_output>Издательство Penguin Books выпустило наибольшее число книг - 42 шт. толще 50 страниц.<jupyter_text>Лидирующим издательством, которое выпустило наибольшее число книг толще 50 страниц  является издательство Penguin Books - на его счету 42 выпущенные книги.   Определите автора с самой высокой средней оценкой книг — учитывайте только книги с 50 и более оценками;<jupyter_code>task_4 = '''
SELECT authors.author AS author_name,
AVG(r.average_rating) as avg_rating
FROM books 
INNER JOIN (SELECT book_id, COUNT(rating) AS cnt,
             AVG(rating) AS average_rating
      FROM ratings
      GROUP BY book_id) AS r
ON r.book_id = books.book_id
INNER JOIN authors ON authors.author_id = books.author_id
WHERE r.cnt >= 50
GROUP BY author_name
ORDER BY avg_rating DESC
LIMIT 1
'''

auth_books_avg_rating = pd.io.sql.read_sql(task_4, con = engine)
print('Автор -', auth_books_avg_rating.iloc[0,0], 'с самой высокой средней оценкой книг:',
      auth_books_avg_rating.iloc[0,1] )<jupyter_output>Автор - <NAME>/<NAME> с самой высокой средней оценкой книг: 4.283844038868471<jupyter_text>Автор - <NAME>/<NAME> является авторос с самой высокой средней оценкой книг: 4.28.  Посчитайте среднее количество обзоров от пользователей, которые поставили больше 50 оценок.<jupyter_code>task_5 = '''
SELECT
    AVG(sub.cnt_text)
FROM
(SELECT 
   reviews.username,
   COUNT(DISTINCT reviews.text) AS cnt_text,
   COUNT(DISTINCT ratings.book_id) AS cnt_book
FROM reviews
     INNER JOIN ratings ON ratings.username = reviews.username
GROUP BY
   reviews.username
HAVING
   COUNT(DISTINCT ratings.book_id)>50) AS sub
'''

avg_cnt_reviews = pd.io.sql.read_sql(task_5, con = engine)
avg_cnt_reviews.head(10)
print('Среднее количество обзоров от пользователей, которые поставили больше 50 оценок равно:', 
      avg_cnt_reviews.iloc[0][0])<jupyter_output>Среднее количество обзоров от пользователей, которые поставили больше 50 оценок равно: 24.333333333333332
{'fraction_non_alphanumeric': 0.10027205596579868, 'fraction_numerical': 0.023189532322839747, 'mean_word_length': 5.012461059190031, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '54469', 'license': 'MIT', 'n_tokens_mistral': 2993, 'n_tokens_neox': 3267, 'n_words': 737, 'path': 'projects/14)SQL_project.ipynb', 'repo_name': 'Ksyuwish/YandexPracticumDA', 'size': '7749'}