SQL查询、AVG和COUNT在多个表上

I need a query, the query that i used doesn't work the way i want for some reason

我需要一个查询，由于某些原因，我使用的查询不能以我想要的方式工作

Here's all the tables involved in the query.

下面是查询中涉及的所有表。

Here's the query i want : Show a list of books with their average ratings and its number of recommendations

下面是我想要的查询:显示一份图书列表，列出它们的平均评级和推荐数量

result should be like this :

结果应该是这样的:

What i already tried :

我已经尝试过的:

SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings], COUNT(recommend.isbn) AS [Number of recommendation]
FROM            book INNER JOIN
                         recommend ON book.isbn = recommend.isbn INNER JOIN
                         ratings ON book.isbn = ratings.isbn
GROUP BY book.isbn

But it didn't work, somehow the the AVG rating works great, but the # of recommendations does not, it conflicts with the ratings table.

但是它不起作用，AVG的评级工作得很好，但是推荐的#没有，它与评级表相冲突。

here's what the result is:

结果是:

However when i try each one alone, everything works great like this :

然而，当我独自一人尝试时，一切都很好:

for AVG ratings :

AVG评级:

SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings]
FROM            book INNER JOIN
                         ratings ON book.isbn = ratings.isbn
GROUP BY book.isbn

Here's the result :

结果:

And for the # of recommendations :

关于建议的#:

SELECT        book.isbn, COUNT(recommend.isbn) AS [Number of recommendation]
FROM            book INNER JOIN
                         recommend ON book.isbn = recommend.isbn
GROUP BY book.isbn

Here's the result :

结果:

So i want a query to combine the two views into one view

因此，我希望查询将两个视图合并到一个视图中。

4 个解决方案

#1

Well, you could combine the two views into one..

你可以把这两种观点合并成一种。

Here, this should work the way you want

在这里，这应该是你想要的方式

SELECT l.isbn, v.rating, r.rec
FROM book l, 
    (SELECT isbn, AVG(rating) AS rating FROM ratings GROUP BY isbn) v,
    (SELECT isbn, COUNT(isbn) AS rec FROM recommend GROUP BY isbn) r
WHERE l.isbn=v.isbn AND l.isbn=r.isbn

Hope this helps.

希望这个有帮助。

Best regards!

最好的问候!

#2

If you want to get accurate results, then you need to do the aggregations before the join:

如果您想获得准确的结果，那么您需要在连接之前进行聚合:

SELECT b.isbn, r.AvgRating, re.NumRecommendation
FROM  book b LEFT JOIN
      (SELECT r.isbn, AVG(r.rating) as AvgRating
       FROM rating r
       GROUP BY r.isbn
      ) r
      ON b.isbn = r.isbn LEFT JOIN
      (SELECT r.isbn, COUNT(*) as NumRecommendation
       FROM recommendation r
       GROUP BY r.isbn
      ) re
      on b.isbn = r.isbn ;

Note that I also switched to left outer joins, so you will get results for all books, even those that are missing either ratings or recommendations.

注意，我还切换到左外连接，因此您将获得所有图书的结果，即使是那些没有评级或推荐的图书。

#3

SELECT 
  book.isbn, 
  IFNULL(AVG(ratings.rating),"Not yet rated") AS [AVG Ratings],
  IFNULL(COUNT(DISTINCT recommend.iduse),0) AS [Number of recommendation]
FROM
  book
  LEFT JOIN recommend ON book.isbn = recommend.isbn 
  LEFT JOIN ratings ON book.isbn = ratings.isbn
GROUP BY book.isbn

Should to the trick:

应的诀窍:

• Counting the distinct users recommending a book will fix the cartesian product issue
• 计算推荐一本书的不同用户将解决笛卡尔产品的问题
• Left joins with the usual IFNULL() plumbing will make it work on books that have either no recommendations or no ratings
• 使用常规的IFNULL()管道将使它在没有推荐或没有评级的书籍上工作

#4

If you are using SQL Server you can use the over clause:

如果您正在使用SQL Server，您可以使用over子句:

SELECT 
  B.isbn, 
  AVG(ra.rating) OVER (PARTITION BY B.isbn) AS [AVG RATE],
  COUNT(re.isbn) OVER (PARTITION BY B.isbn) AS [RECOMMEND COUNT]
FROM Book B
LEFT JOIN recommend re ON B.isbn = re.isbn 
LEFT JOIN ratings ra ON B.isbn = ra.isbn