SQLite的Exists关键字：如何查询最高平均值？

Question

在一个SQLite数据库表中，有两列'mID'和'stars'。我需要返回具有最高平均值'stars'的'mID'。

以下是数据： 评分 mID stars 101 2 101 4 106 4 103 2 108 4 108 2 101 3 103 3 104 2 108 4 107 3 106 5 107 5 104 3

我首先要通过按'mID'分组来计算每个'mID'的'stars'的平均值，例如：

select mID, avg(stars) theAvg
from Rating
group by mID;

因此，我将获得每个'mID'的平均“星级”值表。

mID  avg(stars)
101     3.0
103     2.5
104     2.5
106     4.5
107     4.0
108     3.33333333333

如果我只返回最高的“星级”平均值， 那么我可以像选择max(theAvg)一样接着我刚刚计算的内容。 但是，为了获得与其'mID'相关联的最高平均“星级”，我需要其他东西。

因此，我使用了'not exists'关键字，后跟生成另一个'mID'和'stars'表的子查询。 这个子查询与原始表进行比较，以验证对于来自原始表R1的某个平均“星级”值，不存在新表R2的平均“星级”值大于R1的平均“星级”值。

select mID, theAvg
from (select mID, avg(stars) theAvg
from Rating
group by mID) as R1
where not exists(select * from 
(select mID, avg(stars) theAvg
from Rating
group by mID) as R2
where R2.theAvg > R1.theAvg);

我原本以为这个查询可以得到所有电影的平均评分和它们的mID，但实际上我得到的是两个元组 ('mID':106, 'theAvg':4.5) 和 ('mID':107, 'theAvg':4.0)，而期望的答案只有一个元组 ('mID':106, 'theAvg':4.5)，因为我们要找的是所有'stars'的平均值中最高的一个。
我的错误查询结果： mID theAvg 106 4.5 107 4.0 期望的查询结果： mID theAvg 106 4.5

您认为我做错了哪些步骤？您有什么建议吗？

- YShin

---

是的，这确实很奇怪；您介意我用您的SQL查询的简化版本来提问吗？ - user610650

完全没有问题，你可以继续...谢谢。 - YShin

https://dev59.com/vWkw5IYBdhLWcg3wDWPL - user610650

Answer 1

您可以按照平均值进行desc排序，并像这样添加一个limit子句：

select mID, avg(stars) theAvg
from Rating
group by mID
order by theAvg desc limit 1;

应该给你这个：

sqlite> create table Rating (mID INT, stars INT);
sqlite> 
sqlite> insert into Rating values (101, 2);
sqlite> insert into Rating values (101, 4);
sqlite> insert into Rating values (106, 4);
sqlite> insert into Rating values (103, 2);
sqlite> insert into Rating values (108, 4);
sqlite> insert into Rating values (108, 2);
sqlite> insert into Rating values (101, 3);
sqlite> insert into Rating values (103, 3);
sqlite> insert into Rating values (104, 2);
sqlite> insert into Rating values (108, 4);
sqlite> insert into Rating values (107, 3);
sqlite> insert into Rating values (106, 5);
sqlite> insert into Rating values (107, 5);
sqlite> insert into Rating values (104, 3);
sqlite> 
sqlite> select mID, avg(stars) theAvg
   ...> from Rating
   ...> group by mID
   ...> order by theAvg DESC LIMIT 1;
106|4.5

文档在这里：http://www.sqlite.org/lang_select.html#orderby

- user610650

---

谢谢。你的回答非常有用。但是我仍然不明白为什么我的查询会返回两个元组，当我认为这样一个“不存在”的查询逻辑上应该只返回一个元组... - YShin

Answer 2

抱歉，我对SQL和SO还有点陌生，但是我找到了一个解决方案，适用于最高平均值(stars)存在并列的情况（或者更具体地说，存在未知数量的并列情况，这种情况下无法轻易地设置有序输出的限制）。就像我说的，我有点新手，所以代码有点凌乱：

select title, avg(stars)
from movie join rating using(mID)
where mID not in (select R1.mID  
from (select avg(stars) theAvg, mID, ratingDate from Rating group by mID) 
as R1
join (select avg(stars) theAvg, mID, ratingDate from Rating group by mID)
as R2 
where R1.theAvg < R2.theAvg)
group by mID;

子查询返回任何平均评分低于其他电影平均评分的电影的mID，而主查询的where语句获取子查询未返回的任何mID。逻辑与您最初的逻辑非常相似。

至于 “两个元组” 问题：我不是很理解您错在哪里，但如果我弄清楚了，我会告诉您的。

- byrnesj1