Рейтинг, проблема с GROUP BY по пользователям

[GoaMind]

Рейтинг, проблема с GROUP BY по пользователям

Есть такая табличка:[sql]CREATE TABLE `pictures_votes`(
`pid` bigint(20) unsigned NOT NULL default '0',
`uid` bigint(20) unsigned NOT NULL default '0',
`sum` int(11) NOT NULL default '0',
`num` int(11) NOT NULL default '0',
UNIQUE KEY `pid` (`pid`),
) [/sql]где pid - номер картинки, uid - пользователь, sum - сумма голосов, num - количество голосов

Рейтинг каждой картинки есть sum / num. Группировать надо по пользователю (uid) и получить pid соответствующий картинки с максимальным рейтингом для данного пользователя (uid).

Запрос: [sql]SELECT *, sum/num AS sn FROM `pictures_votes` GROUP BY uid ORDER BY sn DESC[/sql] не выдаёт pid соответствующий требованиям, как я понимаю он выдаёт случайный (первый) pid для каждого пользователя.

Я думаю это распространённая проблема c GROUP BY, но к сажелени на форуме ответа мне найти не удалось. Если я что-то пропустил извиняюсь. Прошу направить в правильном напровлении.
----------------------------------------
И вот ещё... целесообразно вычислять sum/num всё время или лучше сделать ещё один столбец и при апдейте просто записывать в него рейтинг?

 

[zerkms]

GoaMind
кроме как делать подзапрос либо выборку в temporary таблицу с последующим джоином - я решения иного не вижу

 

[chira]

SELECT uid
	,MAX(sum/num) as sn
	,SUBSTRING( MAX( CONCAT(LPAD(FORMAT(sum/num,4),30,'0'),pid) ), 31) AS pid
FROM pictures_votes
GROUP BY uid
ORDER BY sn DESC

 

[zerkms]

очень интересный подход, нужно будет запомнить

 

[GoaMind]

chira
Это 5! Запрос рабочий! Щас сижу его разбираю. Огромное спасибо.

------------------------------------------------------------------


А всётаки как на счёт целесообразности?

 

[zerkms]

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

 

[chira]

идея из MySQL Help подправленная для данного случая

 

[zerkms]

тема уже не актуальна, но думаю что решения будут интересны:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
думаю что самое элегантное решение для подобной задачи, которая часто возникает и с завидным постоянством спрашивается тут (выборка максимального/минимального/любого_другого_выражения и записи, в которой оно находится. имена полей - в контексте задачи по ссылке)

SELECT shop.*
FROM shop LEFT JOIN shop AS s2
ON shop.article=s2.article AND shop.price<s2.price
WHERE s2.article IS NULL

(решение от Csaba Gabor)

буквально сегодня совершенно случайно придумал ещё один выход из ситуации:

[SQL]
SELECT * FROM `shop` `outer`
WHERE EXISTS (
SELECT MAX(`price`) AS `maxprice` FROM `shop` `inner`
WHERE `inner`.`article` = `outer`.`article` GROUP BY `article`
HAVING `outer`.`price` = `maxprice` )
[/SQL]

(мой вариант)

по быстродействию оба вроде как одинаковы - explain показал что в 1 части запроса юзается индекс (на поле article в данном случае), во 2-ой - естественно нет....

так что если производительность не совсем критична - а хочется всё и в 1 запрос - то 2 решения выше (хотя хз насколько эти решения медленнее временной таблицы и извращения с CONCAT - если вообще медленнее конечно)