SQLite 存在关键字 : How to query the highest average?

Question

在具有两列“mID”和“stars”的 SQLite 数据库表中，我必须返回具有最高平均值“stars”的“mID”。

有以下数据:

  RatingmID  stars101     2101     4106     4103     2108     4108     2101     3103     3104     2108     4107     3106     5107     5104     3

I would first take average of 'stars' of each 'mID' by grouping it by 'mID', such as

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

结果，我会得到每个“mID”的平均“星级”值表。

mID  avg(stars)101     3.0103     2.5104     2.5106     4.5107     4.0108     3.33333333333

If I were to just return the highest average value of 'stars',
then I could have just taken something like select max(theAvg) followed by what I just calculated.
But then, to get the highest average 'stars' associated with its 'mID', I needed something else.

So I used 'not exists' keyword followed by a subquery that generates another table of 'mID' and 'stars'.This subquery compares with the original table to verify that for some average 'stars' value from the original table R1, there exists no new table R2's average 'stars' value that is greater than R1's averaged 'stars' value

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' 平均值的最高平均值。

我的查询结果(错误):
平均中间值
106 4.5
107 4.0

想要的结果:
平均中间值
106 4.5

你认为我做错了什么步骤？你有什么建议吗？

Answer 1

您可以 order by 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