如何根据条件删除结果以计算平均电影和特定电影 [英] How do I remove results based on conditions to calculate an average and specific movie

问题描述

我有以下架构.一个简短的解释是:

I have the schema below. A quick explanation of it is:

1. 鲍勃把这部电影评为5/5
2. 詹姆斯把这部电影评为1/5
3. macy对电影的评价为5/5
4. 没有人给电影复仇者定级.

逻辑:

1. 如果我是personA，请查找我阻止的每个人.
2. 查找所有电影评论.
3. 离开电影评论且personA已阻止的任何人，将其从计算中删除.
4. 计算电影的平均评分.

---

CREATE TABLE movies (
  id integer AUTO_INCREMENT primary key,
  name varchar(100) NOT NULL
);

CREATE TABLE customer (
  id integer AUTO_INCREMENT primary key,
  name varchar(100) NOT NULL
);

CREATE TABLE reviews (
  id integer AUTO_INCREMENT primary key,
  rating integer NOT NULL,
  cus_id integer NOT NULL,
  movie_id integer NOT NULL,
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (movie_id) REFERENCES movies(id)
);

CREATE TABLE blocked(
  id integer AUTO_INCREMENT primary key,
  cus_id integer NOT NULL, -- This is the person blocking
  blocked_cus_id integer NOT NULL, -- This is the person who is blocked
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (blocked_cus_id) REFERENCES customer(id)
);

INSERT INTO movies (id, name) VALUES (1, 'up'), (2, 'avengers');
INSERT INTO customer (id, name) VALUES (1, 'bob'), (2, 'james'), (3, 'macy');
INSERT INTO reviews (id, rating, cus_id, movie_id) VALUES (1, 5, 1, 1), (2, 1, 2, 1), (3, 5, 3, 1);
INSERT INTO blocked (id, cus_id, blocked_cus_id) VALUES (1, 1, 2);

我在这里收到有关此问题的帮助:

I received some help with this question here: How do I remove results based on conditions to calculate an average (and the statement was correct) but when I want to find the rating for a specific movie the statement it only shows the movie if it had a rating. I want it to show the movie regardless if it has a rating or not. If it doesn't have a rating it should just say 0. Below, the movie avengers has no ratings and no results are shown.

SELECT m.name, AVG(r.rating) AS avg_rating
FROM movies m
INNER JOIN reviews r ON m.id = r.movie_id
WHERE NOT EXISTS (SELECT 1 FROM blocked b
                  WHERE b.blocked_cus_id = r.cus_id AND b.cus_id = 1)
AND m.id = 2
GROUP BY m.name;

上面的select语句应显示:

The above select statement should show:

+----------+------------+
| movie    | avg_rating |
+----------+------------+
| avengers |          0 |
+----------+------------+

当我将数据库视为bob时，我应该得到:

When I view the database as bob, I should get:

+-------+------------+
| movie | avg_rating |
+-------+------------+
| up    |          5 |
+-------+------------+

当我将数据库视为梅西百货时，我应该得到:

When I view the database as macy, I should get:

+-------+------------+
| movie | avg_rating |
+-------+------------+
| up    |       3.67 |
+-------+------------+

推荐答案

是否要左联接?从您当前的查询开始，应该是:

Do you want a left join? Starting from your current query, that would be:

SELECT m.name, AVG(COALESCE(r.rating, 0)) AS avg_rating
FROM movies m
LEFT JOIN reviews r 
    ON m.id = r.movie_id
    AND NOT EXISTS (
        SELECT 1 
        FROM blocked b
        WHERE b.blocked_cus_id = r.cus_id AND b.cus_id = 1
    )
WHERE m.id = 2
GROUP BY m.id, m.name;

这篇关于如何根据条件删除结果以计算平均电影和特定电影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！