Ключевое слово SQLite Exists: как запросить наивысшее среднее значение?

В таблице базы данных SQLite с двумя столбцами «mID» и «звездочки» я должен вернуть «mID» с наивысшими средними значениями «звездочек».

Имея следующие данные:

  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», например

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

Если бы я просто вернул наивысшее среднее значение «звезд»,
тогда я мог бы просто взять что-то вроде select max (theAvg), а затем то, что я только что вычислил.
Но затем, чтобы получить наивысшее среднее значение » звёзды, связанные с его «средним», мне нужно было кое-что еще.

Поэтому я использовал ключевое слово «не существует», за которым следует подзапрос, который генерирует еще одну таблицу «mID» и «звездочки». Этот подзапрос сравнивается с исходной таблицей, чтобы убедиться, что для некоторого среднего значения «звездочек» из исходной таблицы 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), поскольку мы ищем наивысшее среднее из всех средних значений «звезд».

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

The desired Result:
mID  theAvg
106    4.5

Как вы думаете, в каких шагах я ошибся? Есть предложения, как бы вы это сделали?


sql sqlite average exists highest
person YShin    schedule 15.04.2012    source источник
comment
да, это действительно странно; не возражаете, если я задам вопрос с упрощенной версией вашего sql-запроса?   -  person    schedule 16.04.2012
comment
Вовсе нет, можете продолжать ... спасибо.   -  person YShin    schedule 17.04.2012
comment
stackoverflow .com / questions / 10171403 /   -  person    schedule 17.04.2012

Ответы (2)


arrow_upward
1
arrow_downward

Вы можете 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

person Community    schedule 15.04.2012
comment
Спасибо. ваш ответ был действительно полезен. Но я до сих пор не понимаю, почему мой запрос возвращает два кортежа, когда я логически подумал, что такой запрос «Не существует» должен возвращать только один кортеж ... - person YShin; 16.04.2012

arrow_upward
1
arrow_downward

Извините, я новичок в SQL и SO, но я нашел решение, которое работает, когда есть связь для наивысшего среднего (звездочки) (или, точнее, неизвестного количества связей, и в этом случае вы не можете легко установить предел для заказанного вывода). Как я уже сказал, я вроде как новичок, так что это немного беспорядочно:

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, который не вернул подзапрос. Логика очень похожа на ту, что была у вас изначально.

Что касается проблемы с двумя кортежами: я действительно не понимаю, где вы ошиблись, но я дам вам знать, если я это выясню.

person byrnesj1    schedule 21.08.2014