Задача в SQL

Question

Задача в SQL

Viki Ученик (87), на голосовании 1 год назад

Надо вывести топ семь пользователей по суммарному показателю написанных ревью и поставленных оценок, указывая суммарное количество страниц в книгах, на которые они отреагировали.
Мой неправильный код:

 SELECT   
 
    reviews.username,   
 
    COUNT(DISTINCT reviews.review_id) AS total_reviews,   
 
    COUNT(DISTINCT ratings.rating_id) AS total_ratings,   
 
    SUM(books.num_pages) AS total_pages,  
 
    COUNT(DISTINCT review_id) + COUNT(DISTINCT rating_id) AS total_indicator  
 
FROM reviews  
 
LEFT JOIN books ON reviews.book_id = books.book_id  
 
LEFT JOIN ratings ON reviews.book_id = ratings.book_id  
 
GROUP BY reviews.username  
 
ORDER BY total_reviews DESC, total_ratings DESC, total_pages DESC  
 
LIMIT 7;

Я пока только учусь и понимаю что все вроде как на поверхности, но после многочисленных попыток все окончательно зашло в тупик. A именно надо исправить:

1) total_indicator посчитан правильно и именно по нему и нужно сортировать таблицу 2) не у всех книг есть обзоры, то есть присоединяя левым способом таблицы с книгами и оценками к таблице с обзорами мы теряем книги, а значит и общее количество реакций для пользователя расчитываем неверно, часть оценок просто не попадает в расчет 3) мы считаем оценки и обзоры по пользователям, то есть нам важно объединить данные не только по книге, но и по пользователю, то есть таблицы reviews и ratings мы объединяем учитывая сразу 2 поля - reviews.book _id = ratings.book _id AND reviews.username = ratings.username или более компактно можно написать USING(book_id, username) 4) Так как не у всех книг есть обзоры, то часть reviews.username будет иметь значение NaN, поэтому нужно взять ratings.username и группировать тоже по нему

Помогите разобраться пожалуйста, oчень нужна помощь со стороны

Answer 1

GPT

 SELECT 
    COALESCE(reviews.username, ratings.username) AS username, 
    COUNT(DISTINCT reviews.review_id) AS total_reviews, 
    COUNT(DISTINCT ratings.rating_id) AS total_ratings, 
    SUM(books.num_pages) AS total_pages, 
    COUNT(DISTINCT reviews.review_id) + COUNT(DISTINCT ratings.rating_id) AS total_indicator 
FROM 
    (SELECT DISTINCT username, book_id FROM reviews 
     UNION 
     SELECT DISTINCT username, book_id FROM ratings) AS all_user_actions 
LEFT JOIN reviews ON all_user_actions.username = reviews.username AND all_user_actions.book_id = reviews.book_id 
LEFT JOIN ratings ON all_user_actions.username = ratings.username AND all_user_actions.book_id = ratings.book_id 
LEFT JOIN books ON all_user_actions.book_id = books.book_id 
GROUP BY COALESCE(reviews.username, ratings.username) 
ORDER BY total_indicator DESC 
LIMIT 7;

В этом запросе используется подзапрос, который объединяет уникальные пары (пользователь, книга) из таблицы обзоров и оценок. Затем я присоединяю этот подзапрос к обзорам и оценкам, используя LEFT JOIN, чтобы учесть случаи, когда у пользователя есть только обзоры или только оценки.

Также, используется функцию COALESCE, чтобы объединить имена пользователей из обзоров и оценок в одну колонку username. Это позволяет учесть случаи, когда у пользователя есть обзоры, но нет оценок, и наоборот.

Наконец, сортируются результаты по total_indicator в убывающем порядке и использую LIMIT 7, чтобы получить топ-7 пользователей по суммарному показателю написанных обзоров и поставленных оценок.