如何根据条件删除结果以计算平均值 [英] How do I remove results based on conditions to calculate an average

问题描述

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

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

1. 鲍勃评分5/5
2. 詹姆斯的评分为1/5
3. macy评分为5/5

逻辑:

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');
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 don't understand how I can get rid of reviews if its on a block list

SELECT m.name, avg(r.rating) as avg_rating
FROM movies m
JOIN reviews r ON m.id = r.movie_id
JOIN blocked b on r.cus_id = b.blocked_cus_id
WHERE b.cus_id = 1 -- Viewing the database as bob

当我将数据库视为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 |
+-------+------------+

推荐答案

不使用exists子句我不知道如何表达您的逻辑.我可以在这里尝试:

I don't know how to express your logic without using an exists clause. I might try here:

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)
GROUP BY m.name;

请注意，我们要按电影名称进行汇总，因此在这里需要 GROUP BY .存在子句说，将从平均评价中排除，因为我们可以找到某个客户的被屏蔽条目(在这种情况下， cus_id = 1 ).

Note that we want to aggregate by movie name, so we need GROUP BY here. The exists clause says to exclude from the average rating any review for which we can find a blocked entry belonging to a certain customer (in this case, cus_id = 1).

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