Консультации Портала - Консультация #172490

Задать вопрос Консультации Пользователи Форум

Задать вопрос экспертам

Консультация № 172490

22.09.2009, 17:54

0.00 руб.

0 23 3

Компьютеры Программное обеспечение Базы данных

Доброго времени суток!
Делаю систему оценки качества товаров на MySQL и PHP.
Что может быть проще, создать таблицу с голосами за товар и выводить средний рейтинг.
Табличку для хранения голосов создал с полями what и vote (ID товара и оценка соответственно). Выбрать количество голосов и подсчитать сумму всех голосов, а также вычислить среднюю оценку можно простым запросом –

Код:

SELECT * , SUM( `vote` ) AS `sum` , count( `vote` ) AS `CountVote` , SUM( `vote` ) / count( `vote` ) AS `rating` 
FROM `cms_rating` 
WHERE `what` = '2306'

Этот запрос выполняется достаточно быстро, но меня смущает то, что сумма голосов и их количество подсчитываются дважды.
Так же если нужно подсчитать рейтинг более чем для одного товара, то такой запрос не срабатывает. Если в условие WHERE перечислить все нужные идентификаторы товара, то рейтинг посчитается только для первого из них. (WHERE `what` = '1' OR `what` = '2' OR `what` = 'n').
Собственно вот и вопросы, как избавиться от двойного подсчёта полей `sum` и `CountVote`, а также как вычислить рейтинг нескольких товаров в одном запросе.

Обсуждение

Неизвестный

22.09.2009, 17:59

общий

а зачем у вас после select стоит * ?

Неизвестный

22.09.2009, 18:01

общий

попробуйте так

SELECT SUM( `vote` ) / count(`vote` ) AS `rating`
FROM `cms_rating`
Group by `what`

Неизвестный

22.09.2009, 18:21

общий

Кэр Лаэда:
Только тогда уж так:

select `what`, sum (`vote`)/count (`vote`) as `rating`
from `cms_rating`
group by `what`

Кстати, а в mySQL нет групповой функции avg - вычисление среднего?

Неизвестный

22.09.2009, 18:32

общий

это ответ

Здравствуйте, Cimus!
Чтобы избежать двойного вычисления, нужно использовать переменные. Чтобы посчитать для нескольких товаров (или всех), нужно использовать группировку.

Приложение:
SELECT `what` , @Sum:=SUM( `vote` ) AS `sum` , @CountVote:=count( `vote` ) AS `CountVote` , @Sum / @CountVote AS `rating`
FROM `cms_rating` GROUP BY `what`

Спасибо!

Неизвестный

22.09.2009, 19:02

общий

это ответ

Здравствуйте, Cimus.

SELECT what, SUM(vote) AS sum_vote , count(vote) AS count_vote , AVG( vote ) AS avg_vote
FROM cms_rating
WHERE what = '2306'
GROUP BY what

или, для множества
WHERE what IN (2306,2307,2308 и т.д.)

URL >>ОБОБЩЕНИЕ ДАННЫХ С ПОМОЩЬЮ АГРЕГАТНЫХ ФУНКЦИЙ

Спасибо за отличный ответ и за ссылку!

давно

lamed

Академик
320937
2216

22.09.2009, 20:01

общий

это ответ

Здравствуйте, Cimus.

select
what,
count(vote) as CountVote,
sum(vote) as SumVote,
avg(vote) as rating
from cms_rating
group by what

Использовать слова типа 'sum' (в апострофах) очень не советую, хотя работать-то будет, но за этим словом закреплен и зарезервированный смысл. Остальные имена не являются зарезервированными и сформированы по всем правилам имен SQL, поэтому (и в Мануале, кстати) они используются без апострофов

Спасибо!

Неизвестный

22.09.2009, 22:01

общий

Цитата: 77175

а зачем у вас после select стоит * ?

Просто в таблице есть и другие данные, которые тоже нужны.

Неизвестный

22.09.2009, 22:55

общий

Victor Pyrlik:
Добрый вечер, Victor Pyrlik!
Вы пишете:

SELECT vote, SUM(vote) AS sum_vote , count(vote) AS count_vote , AVG( vote ) AS avg_vote
FROM cms_rating
WHERE what = '2306'
GROUP BY vote

Вы группируете не по товару, а по оценке (для одного товара)
...
или, для множества
WHERE what IN (2306,2307,2308 и т.д.)
Вы группируете не по товару, а по оценке (общей для всех товаров)
Таким образом, Вы получаете сумму и количество оценок "5", "4", "3" и т.д., не привязанных к конкретным товарам.

Неизвестный

22.09.2009, 23:12

общий

Цитата: 300647

Вы группируете не по товару, а по оценке (общей для всех товаров)
..

Да, это так. Я замтил этот нюанс и подправил. Гланое логика верна..
Впрочем функция AVG подсчитывает среднюю оценку как SUM( `vote` ) / count( `vote` ) AS `rating`и получается, что в предложенных вариантах подсчёт сумм и количества оценок дублируется.
Один раз явно - SUM( `vote` ), count( `vote` ) и один раз в функции AVG.
Вывод команды EXPLAIN для предложенных запросов просто ужасный.

Неизвестный

22.09.2009, 23:24

общий

Cimus:
Все-таки не совсем понял, в чем состоит дублирование.
Допустим, есть таблица cms_rating

what vote
=======
'2305' 5
'2305' 5
'2306' 3
'2306' 5
'2307' 5

Запрос
select what, count(vote) as CountVote, sum(vote) as SumVote, avg(vote) as rating
from cms_rating
group by what

вернет таблицу
what CountVote SumVote Rating
=======================
'2305' 2 8 4
'2306' 2 8 4
'2307' 1 5 5

Этот результат - не то, что Вы хотите получить? Тогда, можете выложить тестовую таблицу и ту выдачу, которая должна быть?

Неизвестный

22.09.2009, 23:35

общий

Результат верный.
Давай разберём строку запроса
select what, count(vote) as CountVote, sum(vote) as SumVote, avg(vote) as rating.
В этом запросе есть 3 функции
count(vote) - подсчитывает кол-во записей (голосов)
sum(vote) - подсчитывает сумму голосов
avg(vote) - подсчитывает сумму голосов, их количество и делит сумму на количество и возвращает результат от деления.
Получется, что MySQL подсчитывает количество голосов в count(vote) и avg(vote) и сумму этих голосов в sum(vote) и avg(vote).
Или я не прав?

Неизвестный

22.09.2009, 23:37

общий

Cimus:
Кажется, понял. Вы имеете в виду, что физически функция для каждого столбца считается отдельно? Так это еще не самые большие потери времени.
Я запустил запрос с 60-ю столбцами SUM в одном SELECT. Да, несколько медленней, но не в 60 раз точно. Правда, у меня сервер FireBird, а не MySQL

Неизвестный

22.09.2009, 23:46

общий

Вы верно меня поняли. Только вот планируется в таблице хранить голоса для 8 тысяч товаров (потом скорей всего будет больше), вот я и думаю, как быстро и с какой нагрузкой будет выполнятся данный заппрос.
Если взять примерно 5 голосов на 1 товар, то получается 40 000 записей (8000*5).

Неизвестный

22.09.2009, 23:49

общий

Cimus:
Вообще-то надо разработчиков сервера спрашивать, как работает такой запрос.
Предлагаю Вам такой эксперимент. У Вас есть сервер MySQL.
Вы создаете очень длинную таблицу с десятками и сотнями тысяч записей, потом начинаете выполнять запросы

select
sum(vote)
from cvs_rating,

select
sum(vote), sum(vote) , sum(vote) , sum(vote), sum(vote) , sum(vote) , sum(vote), sum(vote) , sum(vote) ................
from cvs_rating,

сравниваете время выполнения

Неизвестный

22.09.2009, 23:52

общий

Скорей всего так и поступлю, забью пару сотен тысяч записей и буду смотреть время выполнения

Неизвестный

22.09.2009, 23:54

общий

Cimus:
40000 записей - это очень маленькая база данных. Нагрузка зависит от числа обращений, то есть от количества юзеров. Скорость будет низкая на полнотекстовом поиске, на Blob-полях, на формировании отчетов (как правило, по куче связанных таблиц)

Неизвестный

22.09.2009, 23:55

общий

Cimus:
Поделитесь результатом, плиз :)

Неизвестный

23.09.2009, 02:34

общий

Результат тестирования.
Заполнил таблицу случайным образом, забил 1 миллион записей (в реальной таблице будет гораздо меньше).
Отправил тысячу запросов

Код:

SELECT * , SUM( `vote` ) AS `sum_vote` , count( `vote` ) AS `count_vote` , round( AVG( `vote` ) ) AS `avg_vote` 
FROM `cms_rating` 
WHERE `what` 
IN ( 2306, 123, 255, 268, 2000, 5698, 9000, 8569, 758, 6851, 111, 459, 6892, 9856, 5, 888, 12000 ) 
GROUP BY `what`

где условие WHERE `what` заполнено разными идентификаторами товаров (в каждом запросе разные значения).
В итоге - среднее время выполнения запроса 0.0053 сек Результат меня очень порадовал!!!
Выполнил команду EXPLAIN к этому запросу и немного был в замешательстве.
Дело в том, что когда в таблице было всего несколько десятков записей, то MySQL совершенно не использовал индекс, а просто перебирал все данные, собственно это меня и пугало.
При заполненной таблице, сервер БД уже не так глупо выполнял запрос, а смотрел в индекс и время на обработку уходило меньше, чем при почти пустой таблице.
Была идея отказаться от функции AVG, поместить результат функций count и SUM в переменные и выполнить деление для вычисления рейтинга(предложил vladisslav). Этот приём позволил бы избавить БД от повторного вычисления этих значений. Но как оказалось данный приём работал несколько медленнее - 0.0065 сек. Странно, но факт!
Всем спасибо за терпение и советы!

Неизвестный

23.09.2009, 07:08

общий

Cimus:

SELECT * , SUM( `vote` ) AS `sum_vote` , count( `vote` ) AS `count_vote` , round( AVG( `vote` ) ) AS `avg_vote`
................
GROUP BY `what`

честно говоря смущает.. или, Вы просто '*' поставили для нас, как не имеющее значение (что действительно так), или, это "не есть хорошо"..
Группировка должна выполняться по всем полям не указанным в агрегатных функциях, даже, если СУБД и позволяет такие вольности, надо понимать что сервер в этом случае сам примет решение, порой далекое от желаемого.
Уберите * или укажите точно те поля что Вам нужны (может и скорость выборки изменится

Неизвестный

23.09.2009, 07:28

общий

Если требуется выбрать из таблицы все поля то * позволяет сократить запись запроса (что приятней читать) и увеличить скорость выборки(из личных наблюдений).

В данном случае в таблице несколько больше полей, чем указанно в запросе, поэтому я поставил *.
СУБД MySQL вполне позволяет такие вольности так почему бы и не воспользоваться ими?

P.S.
Попробовал убрать * и явно задать список других полей - скорость не измнилась.

Неизвестный

23.09.2009, 16:16

общий

Цитата: 300647

Вы группируете не по товару, а по оценке (для одного товара)
...
или, для множества
WHERE what IN (2306,2307,2308 и т.д.)
Вы группируете не по товару, а по оценке (общей для всех товаров)
Таким образом, Вы получаете сумму и количество оценок "5", "4", "3" и т.д., не привязанных к конкретным товарам.

Конечно обычно не делают группировку по параметру агрегатной функции (смысл теряется) - это только пример, автор заметил это и исправил, в ответе, для тех кто это не заметит, я так же исправил.

PS: приятно, когда люди видят и думают

Неизвестный

23.09.2009, 16:28

общий

Цитата: 11958

Если требуется выбрать из таблицы все поля то * позволяет сократить запись запроса (что приятней читать) и увеличить скорость выборки(из личных наблюдений).

В данном случае в таблице несколько больше полей, чем указанно в запросе, поэтому я поставил *.
СУБД MySQL вполне позволяет такие вольности так почему бы и не воспользоваться ими?

Получить значение агрегатной функции можно, если сгруппировать другие поля не входящие в эту функцию.. Таким образом, Вы делаете группировку по всем полям... (не явно).
Я к тому, что надо стараться избегать таких вот скрытых потенциальных ошибок.. Да и согласитесь, далеко не всегда нужны все поля.. уж точно не нужны те, что обрабатываются функциями.

PS: как это делается: обычный цикл, в котором проверятся / обрабатывается каждая запись (нижний уровень), каждая запись в свою очередь обрабатывается по каждому полю... индексы влияют на.. индексы того самого цикла.. Чем эффективней индекс, тем меньше итераций в цикле (в приделе стремится к 1). Важно помнить это..

Неизвестный

23.09.2009, 16:40

общий

Цитата: 11958

Вы верно меня поняли. Только вот планируется в таблице хранить голоса для 8 тысяч товаров (потом скорей всего будет больше), вот я и думаю, как быстро и с какой нагрузкой будет выполнятся данный запрос.
Если взять примерно 5 голосов на 1 товар, то получается 40 000 записей (8000*5).

У меня только в одной табличке сейчас более 10 млн. записей.. размер 1 БД 30 Гб/мес.. И используются там, далеко не просто sum и т.д. а часто ранжирование и т.д. т.е. аналитические функции.. И редко что работает больше 1 сек.. (это если отчет).
Так что, Ваши сомнения излишне

Да и не считает СУБД, как Вы думаете, каждую функцию отдельным селектом.. (иначе, базы данных были бы самыми тормозными вещами в мире и мы так и жили бы в 1 гигабайтном пространстве)...

Форма ответа

Отправка постов/ответов доступна только зарегистрированным и подтвержденным пользователям.

Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.