SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 05.08.2007 19:41:35

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Рейтинг пользователей

Имеется 2 таблицы.
Табличка 'pro' имеет вид:

cat char(20)
ver char(10)
sta char(14)
nazv char(30)
plat char(30)
screen char(60)
info text
avtor char(32)
ident char(10)
year char(10)
janr char(12)
kach char(9)
word char(11)
lang char(15)
artist char(25)
style char(15)

В колонку "avtor" записывается имя пользователя, который добавляет запись.

Вторая табличка 'rating':

ball int(10) - оценка
who char(20) - автор
tema char(10) - название записи

Во второй табличке 'rating' записывается рейтинг записи.
Тоесть оценка, которую дают другие пользователи...

Рейтинг по количество записей я сделал так:

SELECT avtor, COUNT(avtor) AS champ FROM pro GROUP BY avtor ORDER BY champ DESC LIMIT 5

Рейтиг по сумме баллов я сделал так:

SELECT SUM(ball), who, COUNT(who) as rati FROM rating GROUP BY who ORDER BY who DESC LIMIT 5

=======
Cледующем запросом я получаю Пятерку пользователей у кого больше всего записей в таблице 'pro'

SELECT avtor, COUNT(avtor) AS champ FROM pro GROUP BY avtor ORDER BY champ DESC LIMIT 5
while ($row = mysql_fetch_array($sql)) {

print_r($row[1]);
}

Подскажите как мне в этом запросе подключить вторую табличку 'rating' и сделать что то вроде:

COUNT(avtor) + SUM(bull), COUNT(who)

Тоесть сложить количество записей и сумму баллов каждого пользователя и потом LIMIT 5,
вывести ТОП - 5.

Неактивен

 

#2 05.08.2007 21:13:30

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

Маленький пример:

Допустим Вася зашел на сайт. Добавил новость.
Сохраняется в табличку (pro) так:
...
id | avtor | nazv
132 | Вася | Winamp
...

Потом за эту новость проголосовали Петя, Дима, Игорь
Сохраняется в табличку (rating) так:
...
ball | who | tema
3 | Вася | 132
4 | Вася | 132
1 | Вася | 132
...

Теперь мне надо подсчитать сколько статей вообще у Васи в таблице pro
и сумму всех баллов Васи в таблице rating.

В данном случаи получится так:
1 cтатья + 8 баллов = 9

Неактивен

 

#3 05.08.2007 23:18:51

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Рейтинг пользователей

Код:

SELECT COUNT(nazv)+SUM(ball)
FROM pro LEFT JOIN rating ON who=avtor
GROUP BY avtor
ORDER BY 1 DESC
LIMIT 5

Я вам, однако, советую как-то переделать структуру таблиц.
Хранение пользователей по именам - не очень удачное решение.
Во-первых, это перерасход ресурсов (т.к. имена пользователей обычно
больше 4 байт). Во-вторых, любые объединения таблиц по строкам будут
работать медленнее объединений по числам. Самый простой способ -
добавить, например, таблицу авторов
id INT,
author_name VARCHAR(50)
и хранить авторов по их номерам.

Неактивен

 

#4 06.08.2007 10:27:31

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

paulus, спасибо. Последую советом и переделаю smile
Получилось следующее:
720
44
Хотя должны быть:
49
13
Потому что у меня 2 юзера
у одного 20 тем и сумма баллов 29
У другого 4 темы и сумма балло 9

Неактивен

 

#5 06.08.2007 10:52:18

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3880

Re: Рейтинг пользователей

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

Код:

SELECT author,
  (SELECT COUNT(nazv) a FROM pro where pro.author=z.author)
  +(SELECT SUM(ball) b FROM rating r WHERE r.who=author) r
  FROM pro z group by author;

Неактивен

 

#6 06.08.2007 11:08:20

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

rgbeast, Спасибо. То что нужно.
paulus и тебе тоже спасибо

Неактивен

 

#7 03.09.2007 07:40:49

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

rgbeast, интересная штука получается...
Если в таблице rating ( где пишется оценка и автор ) пусто.
То он почему то не суммуриет.
А если допустим у Васи 4 темы и один раз проголосовали за его какую нибудь тему.
То он уже в рейтинге будет.
И все нормально суммируется.

Неактивен

 

#8 03.09.2007 09:37:13

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3880

Re: Рейтинг пользователей

Да, действительно. Если нет записей, то второй SELECT дает NULL, а любая операция с NULL дает NULL.

Предлагаю использовать функцию IFNULL(), которая поможет в этой ситуации

Код:

SELECT author,
  IFNULL((SELECT COUNT(nazv) a FROM pro where pro.author=z.author),0)
  +IFNULL((SELECT SUM(ball) b FROM rating r WHERE r.who=author),0) r
  FROM pro z group by author;

Неактивен

 

#9 03.09.2007 13:03:29

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

rgbeast, большое спасибо!
Буду знать...

Неактивен

 

#10 29.10.2007 14:47:04

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

Приветствую rgbeast,
Проблемка тут, долго очень обрабатывается запрос (который Вы мне подсказали). Где то около 17 секунд.
Я думал может быть из за того что у меня в колонке avtor было имя автора а не его ID.
Переделал, сейчас в колонке avtor находится ID автора.
Но к сожелению это не помогло. Все равно долго обрабатывается запрос.

Вот сам запрос:

Код:

SELECT avtor, IFNULL( (SELECT COUNT( nazv ) a FROM pro WHERE pro.avtor = z.avtor ) , 0 ) + 
IFNULL( ( SELECT SUM( ball ) b FROM rating r WHERE r.who = avtor ) , 0 ) r FROM pro z GROUP BY avtor ORDER BY r DESC LIMIT 7

Подскажите пожалуйста, может что нибудь можно сделать?

В Базе около 800 записей.

Заранее Спасибо.

Отредактированно rkit (29.10.2007 16:17:38)

Неактивен

 

#11 29.10.2007 23:39:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3880

Re: Рейтинг пользователей

Можно добавить индексы к таблицам, например
ALTER TABLE pro ADD KEY(avtor);
ALTER TABLE rating ADD KEY(who);
ALTER TABLE pro ADD KEY(avtor);

Неактивен

 

#12 30.10.2007 04:40:22

rkit
Участник
Зарегистрирован: 05.08.2007
Сообщений: 8

Re: Рейтинг пользователей

Вчера я это пробывал. Не помогло.
Сейчас (утром) я проверил как грузица страничка.
И загрузилось она очень быстро (меньше сек)
Может вчера был загружен сервер.

Но все равно спасибо.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson