Запрос из 4-х таблиц

[Сергей Добросклонов]

Имеются таблицы: users,videos,photos,articles. Необходимо выбрать информацию о всех пользователях, а так же подсчитать рейтинг каждого по выложенным им материалам, а именно: по видео, фото и статьям. Таблицы videos,photos,articles содержат id,id_who (это поле связующее с id в таблице users),voting(сколько раз голосовали),ballet (сумма всех оценок).
Для начала я сделал так:

Код:

$join = mysql_query("SELECT users.id,users.niсk,users.photo,SUM(videos.ballet)/SUM(videos.voting) AS rat_vid, SUM(photos.ballet)/SUM(photos.voting) AS rat_ph, SUM(articles.ballet)/SUM(articles.voting) AS rat_art FROM users LEFT JOIN videos ON videos.id_who=users.id LEFT JOIN photos ON photos.id_who=users.id LEFT JOIN articles ON articles.id_who=users.id GROUP BY users.id",$db);

if ($join && mysql_num_rows($join)>0)
{
	$join_res = mysql_fetch_array($join);
	do
	{
		echo $join_res['niсk'];
		echo "<img src='".$join_res['photo']."'><br>";
		echo "Рейтинг по видео: ".$join_res['rat_vid']."<br>";
		echo "Рейтинг по фото: ".$join_res['rat_ph']."<br>";
		echo "Рейтинг по статьям: ".$join_res['rat_art']."<br>";
	}
	while($join_res = mysql_fetch_array($join));
}

Всё отлично работает, считает корректно.
Затем сделал так:

Код:

$join = mysql_query("SELECT users.id,users.niсk,users.photo,((SUM(videos.ballet)+SUM(photos.ballet)+SUM(articles.ballet))/(SUM(videos.voting)+SUM(photos.voting)+SUM(articles.voting))) AS rating FROM users LEFT JOIN videos ON videos.id_who=users.id LEFT JOIN photos ON photos.id_who=users.id LEFT JOIN articles ON articles.id_who=users.id GROUP BY users.id",$db);

if ($join && mysql_num_rows($join)>0)
{
	$join_res = mysql_fetch_array($join);
	do
	{
		echo $join_res['niсk'];
		echo "<img src='".$join_res['photo']."'><br>";
		echo "по рассказам: ".$join_res['rating']."<br>";
	}
	while($join_res = mysql_fetch_array($join));
}

И получил нечто странное: показывается рейтинг только первого пользователя и то он не соответствует реальным данным, а у других вообще пустота...
Помогите, пожалуйста, составить верный запрос, очень устал от этой проблемы.

[Kivals]

На первый взгляд вроде все ок...

Попробуй может вместо

Код:

((SUM(videos.ballet)+SUM(photos.ballet)+SUM(articles.ballet))/(SUM(videos.voting)+SUM(photos.voting)+SUM(articles.voting)))

написать

Код:

((SUM(videos.ballet+photos.ballet+articles.ballet))/(SUM(videos.voting+photos.voting+articles.voting)))

[PooH]

Код: (SQL)

GROUP BY users.id, users.nick, users.photo

вот пример:

Код: (SQL)

SQL> WITH users AS (
  2  SELECT 1 id, 'nick1' nick, 'photo1' photo FROM dual UNION ALL
  3  SELECT 2 id, 'nick2' nick, 'photo2' photo FROM dual),
  4  videos AS (
  5  SELECT 1 id, 1 id_who , 1 ballet, 1 voting FROM dual UNION ALL
  6  SELECT 2 id, 2 id_who , 2 ballet, 1 voting FROM dual),
  7  photos AS (
  8  SELECT 1 id, 1 id_who , 1 ballet, 1 voting FROM dual UNION ALL
  9  SELECT 2 id, 2 id_who , 4 ballet, 1 voting FROM dual),
 10  articles AS (
 11  SELECT 1 id, 1 id_who , 1 ballet, 1 voting FROM dual UNION ALL
 12  SELECT 2 id, 2 id_who , 5 ballet, 1 voting FROM dual)
 13  SELECT users.id,
 14         users.nick,
 15         users.photo,
 16         ((SUM(videos.ballet) + SUM(photos.ballet) + SUM(articles.ballet)) /
 17         (SUM(videos.voting) + SUM(photos.voting) + SUM(articles.voting))) AS rating
 18    FROM users
 19    LEFT JOIN videos ON videos.id_who = users.id
 20    LEFT JOIN photos ON photos.id_who = users.id
 21    LEFT JOIN articles ON articles.id_who = users.id
 22   GROUP BY users.id, users.nick, users.photo
 23  /

        ID NICK  PHOTO      RATING
---------- ----- ------ ----------
         2 nick2 photo2 3,66666666
         1 nick1 photo1          1

Это пример из Oracle, странно, что MySQL даёт делать группировку только по одному полю - Оракл таких вольностей не позволяет

[RXL]

Позволяет, но выдача негруппируемых полей непредсказуема. По этому делать этого не следует.
Зато MySQL позволяет ссылаться в GROUP BY, HAVING и ORDER BY на алиасы - скрыто производит перезапись запроса с разворачиванием алиасов. Увидеть это можно посредством "EXPLAIN EXTENDED <запрос>" c последующим просмотром сообщений: SHOW WARNINGS.

Все запросы надо отлаживать перед внедрением в программу. И внимательно читать предупреждения.

[Kivals]

Так тут суть не в правильности заполнения полей, а в неверном количестве записей
Еще не понятно как отработает СУМ() и "+" в случае NULL аргументов...

[RXL]

Если под SUM() попадают только поля с NULL, то результат - NULL. Если есть хоть одно значение, то NULL эквивалентно нулю. Т.е. результат опять же не предсказуем.

Сперва надо бы увидеть скрипт таблицы: SHOW CREATE TABLE tablename.

[PooH]

"Т.е. результат опять же не предсказуем."
Ну почему же? 0 или NULL, разве нет?

[RXL]

Почему я говорю, что непредсказуем: в одном запросе будет NULL, в другом - число. А тут еще и формула. Интересно, есть ли исключение "делением на NULL"...

[PooH]

"Зато MySQL позволяет"
И что это дает?

Добавлено через 46 секунд:
"в одном запросе будет NULL, в другом - число"
Ну так если есть данные то, будет число, нет данных - будет NULL. Так ведь?

[Kivals]

PooH, Да, но поскольку результат SUM() у нас операнд математической операции - то какой результат будет:
NULL / Число
Число / NULL
NULL / NULL
?

[Sla]

надо просто убрать left join

[RXL]

"Зато MySQL позволяет"
И что это дает?

Упрощение записи.
Это я софтопил.
(А еще это не позволяет Oracle.)

[Kivals]

надо просто убрать left join

?
И получить декартово произведение 4х таблиц?

[RXL]

PooH[/b], не....

Например:

Код: (MySQL)

(SUM(videos.ballet)+SUM(photos.ballet)+SUM(articles.ballet))/(SUM(videos.voting)+SUM(photos.voting)+SUM(articles.voting))

Если строк таблицы videos будет ноль, то результат всего выражения - NULL.
В такиз случаях я делаю IFNULL(SUM(field), 0).

[Sla]

Kivals, нет
использовать inner join

[PooH]

ааа, понял, ну значит так же как в Ораклине
я просто не правильно понял твою фразу "Если есть хоть одно значение, то NULL эквивалентно нулю."

[PooH]

зачем тут какие-то джоины? вон я привел корректный пример выше.  с NULL бороться просто при помощи IFNULL.

[Kivals]

Sla, тогда мы потеряем записи, для которых нет эквивалентов хотя бы в одной из таблиц videos,photos,articles
Я все-таки больше склоняюсь к варианту RXL: использовать IFNULL()

[RXL]

ааа, понял, ну значит так же как в Ораклине
я просто не правильно понял твою фразу "Если есть хоть одно значение, то NULL эквивалентно нулю."

Это для случая:
id  val
1  1
2  2
3  3
4  NULL

SUM(val) даст 6, а при WHERE id = 4 даст NULL.

[Kivals]

PooH, так у тебя тоже LEFT JOIN

[RXL]

И надо учесть деление на ноль - оно тоже дает NULL.

[PooH]

ну вот я и говорю, что все с джоинами тут нормально, никаких других не надо

Добавлено через 43 секунды:
RXL, тут по логике не может быть число/null, только null/null

[RXL]

Может быть и "0/0".
В обеих случаях всю формулу тоже стоит обернуть IFNULL().

[Kivals]

Offtopic:

Развели мы тут дискуссию, а автора нет

[PooH]

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

[Сергей Добросклонов]

Автор тут. Просто у автора интернет 64 кбит/сек, поэтому часто не захожу Всем спасибо, что так живо отреагировали! Как испробую ваши советы отпишусь получилось или нет

Добавлено через 2 часа, 10 минут и 6 секунд:
Вот это:

Код:

GROUP BY users.id, users.nick, users.photo

ничего не дало, но я оставил на всякий случай.
Это:

В такиз случаях я делаю IFNULL(SUM(field), 0).

и это:

В обеих случаях всю формулу тоже стоит обернуть IFNULL().

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

Пользователь 1
Видео: 5 голосов - 24 балла
Фото: 4 голоса - 18 баллов
Статьи: 1 голос - 2 балла
Итого:   (24+18+2)/(5+4+1) = 4.4
Результат запроса: 3.6154

Пользователь 2
0 - 0
4 - 16
0 - 0
Итого: 16/4 = 4
Результат запроса: 3.6667


Пользователь 3
4 - 19
0 - 0
3 - 13
Итого: 4.5714
И результат тоже 4.5714 !!!

Почему так понять не могу. Одну вещь вижу: у третьего пользователя нет фото и считается правильно, у других фотографии есть и какой-то косяк. Но, полагаю, что это совпадение, хотя, возможно, странная работа left join'а.

[PooH]

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

[Сергей Добросклонов]

Вот скрипт и база. Я скопировал нужные таблицы из своей старой БД в новую и написал скрипт на отдельной странице с подключением к новой базе. Теперь у первого пользователя рейтинг 4.3333 (вместо 4.4, которых должно быть и вместо 3.6154, которых выводит из старой базы с аналогичными данными).... Не пойму как так может быть...

[McZim]

"Зато MySQL позволяет"
И что это дает?

Упрощение записи.
Это я софтопил.
(А еще это не позволяет Oracle.)

Дело в том что, чтобы пользоваться алиасами в group by нужно быть уверенным в том что алиас объявлен до того когда он используется. В пределах одного запроса этого нельзя сделать, поэтому если необходимо использовать алиасы в группировке, можно воспользоваться подзапросом.

Код: (SQL)

SELECT COUNT(*), (SELECT * FROM....) AS "alias"
FROM TABLE
GROUP BY (SELECT * FROM....)

Как это можно переписать с ипользованием алиасов.

Код: (SQL)

SELECT COUNT, alias
FROM
  (SELECT COUNT(*) AS "count", (SELECT * FROM....) AS "alias"
  FROM TABLE)
GROUP BY "alias"

Но еслиговорить о упрощении записи, то лучше оставлять без подзапросов и не использовать алиасов в группировке. Это касаемо Oracle.

[PooH]

какая интересная архитектура лефт джоинами просто с таблицами тут не обойтись... ща раскапаем

Добавлено через 42 минуты и 4 секунды:
ну собственно вот:

Код: (SQL)

SELECT u.id,
       u.nick,
       u.photo,
       v.b,
       p.b,
       a.b,
       v.v,
       p.v,
       a.v,
       ifnull((ifnull(v.b, 0) + ifnull(p.b, 0) + ifnull(a.b, 0)) / (ifnull(v.v, 0) + ifnull(p.v, 0) + ifnull(a.v, 0)), 0)
  FROM users AS u
  LEFT JOIN (SELECT id_who, SUM(ballet) b, SUM(voting) v FROM videos t GROUP BY id_who) AS v ON v.id_who = u.id
  LEFT JOIN (SELECT id_who, SUM(ballet) b, SUM(voting) v FROM photos t GROUP BY id_who) AS p ON p.id_who = u.id
  LEFT JOIN (SELECT id_who, SUM(ballet) b, SUM(voting) v FROM articles t GROUP BY id_who) AS a ON a.id_who = u.id

получаем

Код:

id	nick	photo	b	b	b	v	v	v	rating
1	Пользователь 1	1.jpg	24	14	7	5	3	2	4.50
2	Пользователь Х	1.jpg	NULL	NULL	NULL	NULL	NULL	NULL	0.00
3	Пользователь 2	1.jpg	NULL	11	0	NULL	3	0	3.67
44	Пользователь 3	1.jpg	19	NULL	NULL	4	NULL	NULL	4.75