SQLite Exists关键字:如何查询最高平均值? [英] SQLite Exists keyword : How to query the highest average?

问题描述

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

In an SQLite database table with two columns 'mID', and 'stars', I have to return 'mID's with highest average values of 'stars'.

具有以下数据:

  Rating
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"进行分组，例如

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"的平均星"值表.

As a result, I would get the table of average 'stars' values for each 'mID'.

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

如果我只想返回星星"的最高平均值，
那么我本来可以选择select max(theAvg)之类的，然后再加上我刚才计算的内容.
但是，为了获得与其"mID"相关联的最高平均星级"，我需要其他一些东西.

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.

因此，我使用了不存在"关键字，后跟一个子查询，该子查询生成了另一个"mID"和"stars"表. 此子查询与原始表进行比较，以验证对于原始表R1中的某些平均星级"值，不存在新表R2的平均星级"值大于R1的平均星级"

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)，因为我们正在寻找星星"所有平均值中的最高平均值.

I thought as a result of this query, I would get the highest average stars and it's mID, but instead what I get is two tuples ('mID':106, 'theAvg':4.5) and ('mID':107, 'theAvg':4.0), when the desired answer is only one tuple ('mID':106, 'theAvg':4.5), since we are looking for the highest average of all averages of 'stars'.

The result of my query(Wrong):
mID  theAvg
106    4.5
107    4.0

The desired Result:
mID  theAvg
106    4.5

您认为我错了什么步骤?有什么建议你会怎么做吗?

What steps do you think I got wrong? Any suggestion how you'd do it?

推荐答案

您可以平均使用order by desc，并添加一个limit子句，如下所示:

You can order by desc on the average, and add a limit clause as shown here:

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

这篇关于SQLite Exists关键字:如何查询最高平均值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！