Сергей Добросклонов
|
|
« : 16-06-2011 12:23 » |
|
Имеются таблицы: 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
|
|
« Ответ #1 : 16-06-2011 16:04 » |
|
На первый взгляд вроде все ок... Попробуй может вместо ((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
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #2 : 16-06-2011 17:25 » |
|
GROUP BY users.id, users.nick, users.photo вот пример: 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 даёт делать группировку только по одному полю - Оракл таких вольностей не позволяет
|
|
« Последнее редактирование: 16-06-2011 17:28 от PooH »
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
RXL
|
|
« Ответ #3 : 16-06-2011 17:57 » |
|
Позволяет, но выдача негруппируемых полей непредсказуема. По этому делать этого не следует. Зато MySQL позволяет ссылаться в GROUP BY, HAVING и ORDER BY на алиасы - скрыто производит перезапись запроса с разворачиванием алиасов. Увидеть это можно посредством "EXPLAIN EXTENDED <запрос>" c последующим просмотром сообщений: SHOW WARNINGS.
Все запросы надо отлаживать перед внедрением в программу. И внимательно читать предупреждения.
|
|
« Последнее редактирование: 16-06-2011 18:03 от RXL »
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
Kivals
|
|
« Ответ #4 : 16-06-2011 18:01 » |
|
Так тут суть не в правильности заполнения полей, а в неверном количестве записей Еще не понятно как отработает СУМ() и "+" в случае NULL аргументов...
|
|
|
Записан
|
|
|
|
RXL
|
|
« Ответ #5 : 16-06-2011 18:07 » |
|
Если под SUM() попадают только поля с NULL, то результат - NULL. Если есть хоть одно значение, то NULL эквивалентно нулю. Т.е. результат опять же не предсказуем.
Сперва надо бы увидеть скрипт таблицы: SHOW CREATE TABLE tablename.
|
|
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #6 : 16-06-2011 18:16 » |
|
"Т.е. результат опять же не предсказуем." Ну почему же? 0 или NULL, разве нет?
|
|
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
RXL
|
|
« Ответ #7 : 16-06-2011 18:18 » |
|
Почему я говорю, что непредсказуем: в одном запросе будет NULL, в другом - число. А тут еще и формула. Интересно, есть ли исключение "делением на NULL"...
|
|
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #8 : 16-06-2011 18:18 » |
|
"Зато MySQL позволяет" И что это дает?
Добавлено через 46 секунд: "в одном запросе будет NULL, в другом - число" Ну так если есть данные то, будет число, нет данных - будет NULL. Так ведь?
|
|
« Последнее редактирование: 16-06-2011 18:19 от PooH »
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
Kivals
|
|
« Ответ #9 : 16-06-2011 18:26 » |
|
PooH, Да, но поскольку результат SUM() у нас операнд математической операции - то какой результат будет: NULL / Число Число / NULL NULL / NULL ?
|
|
|
Записан
|
|
|
|
Sla
|
|
« Ответ #10 : 16-06-2011 18:27 » |
|
надо просто убрать left join
|
|
|
Записан
|
Мы все учились понемногу... Чему-нибудь и как-нибудь.
|
|
|
RXL
|
|
« Ответ #11 : 16-06-2011 18:27 » |
|
"Зато MySQL позволяет" И что это дает?
Упрощение записи. Это я софтопил. (А еще это не позволяет Oracle.)
|
|
« Последнее редактирование: 16-06-2011 18:33 от RXL »
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
Kivals
|
|
« Ответ #12 : 16-06-2011 18:30 » |
|
надо просто убрать left join
? И получить декартово произведение 4х таблиц?
|
|
|
Записан
|
|
|
|
RXL
|
|
« Ответ #13 : 16-06-2011 18:30 » |
|
PooH[/b], не.... Например: (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
|
|
« Ответ #14 : 16-06-2011 18:36 » |
|
Kivals, нет использовать inner join
|
|
|
Записан
|
Мы все учились понемногу... Чему-нибудь и как-нибудь.
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #15 : 16-06-2011 18:44 » |
|
ааа, понял, ну значит так же как в Ораклине я просто не правильно понял твою фразу "Если есть хоть одно значение, то NULL эквивалентно нулю."
|
|
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #16 : 16-06-2011 18:45 » |
|
зачем тут какие-то джоины? вон я привел корректный пример выше. с NULL бороться просто при помощи IFNULL.
|
|
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
Kivals
|
|
« Ответ #17 : 16-06-2011 18:46 » |
|
Sla, тогда мы потеряем записи, для которых нет эквивалентов хотя бы в одной из таблиц videos,photos,articles Я все-таки больше склоняюсь к варианту RXL: использовать IFNULL()
|
|
|
Записан
|
|
|
|
RXL
|
|
« Ответ #18 : 16-06-2011 18:47 » |
|
ааа, понял, ну значит так же как в Ораклине я просто не правильно понял твою фразу "Если есть хоть одно значение, то NULL эквивалентно нулю." Это для случая: id val 1 1 2 2 3 3 4 NULL SUM(val) даст 6, а при WHERE id = 4 даст NULL.
|
|
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
Kivals
|
|
« Ответ #19 : 16-06-2011 18:49 » |
|
PooH, так у тебя тоже LEFT JOIN
|
|
|
Записан
|
|
|
|
RXL
|
|
« Ответ #20 : 16-06-2011 18:50 » |
|
И надо учесть деление на ноль - оно тоже дает NULL.
|
|
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #21 : 16-06-2011 18:51 » |
|
ну вот я и говорю, что все с джоинами тут нормально, никаких других не надо Добавлено через 43 секунды:RXL, тут по логике не может быть число/null, только null/null
|
|
« Последнее редактирование: 16-06-2011 18:51 от PooH »
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
RXL
|
|
« Ответ #22 : 16-06-2011 18:53 » |
|
Может быть и "0/0". В обеих случаях всю формулу тоже стоит обернуть IFNULL().
|
|
|
Записан
|
... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
|
|
|
Kivals
|
|
« Ответ #23 : 16-06-2011 18:55 » |
|
Offtopic: Развели мы тут дискуссию, а автора нет
|
|
|
Записан
|
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #24 : 16-06-2011 19:01 » |
|
да автору сразу дали ответ, а остальное разглагольствования на тему тюнинга и "предпросмотра" граблей - очень полезные вещи, кстати. Автор думаю будет не в обиде
|
|
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
Сергей Добросклонов
|
|
« Ответ #25 : 17-06-2011 08:46 » |
|
Автор тут. Просто у автора интернет 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'а.
|
|
« Последнее редактирование: 17-06-2011 10:56 от Барин »
|
Записан
|
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #26 : 17-06-2011 12:51 » |
|
давай скрипты для создания и заполнения таблиц. Вон вверху пример, все считается корректно надо смотреть на твои данные и твой запрос.
|
|
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
Сергей Добросклонов
|
|
« Ответ #27 : 17-06-2011 14:13 » |
|
Вот скрипт и база. Я скопировал нужные таблицы из своей старой БД в новую и написал скрипт на отдельной странице с подключением к новой базе. Теперь у первого пользователя рейтинг 4.3333 (вместо 4.4, которых должно быть и вместо 3.6154, которых выводит из старой базы с аналогичными данными).... Не пойму как так может быть...
|
|
|
Записан
|
|
|
|
McZim
|
|
« Ответ #28 : 17-06-2011 14:43 » |
|
"Зато MySQL позволяет" И что это дает?
Упрощение записи. Это я софтопил. (А еще это не позволяет Oracle.) Дело в том что, чтобы пользоваться алиасами в group by нужно быть уверенным в том что алиас объявлен до того когда он используется. В пределах одного запроса этого нельзя сделать, поэтому если необходимо использовать алиасы в группировке, можно воспользоваться подзапросом. SELECT COUNT(*), (SELECT * FROM....) AS "alias" FROM TABLE GROUP BY (SELECT * FROM....) Как это можно переписать с ипользованием алиасов. SELECT COUNT, alias FROM (SELECT COUNT(*) AS "count", (SELECT * FROM....) AS "alias" FROM TABLE) GROUP BY "alias" Но еслиговорить о упрощении записи, то лучше оставлять без подзапросов и не использовать алиасов в группировке. Это касаемо Oracle.
|
|
« Последнее редактирование: 17-06-2011 14:49 от McZim »
|
Записан
|
The CBO without stats is like a morning without coffee. (c) T.Kyte.
|
|
|
PooH
Глобальный модератор
Offline
Пол:
... и можно без хлеба!
|
|
« Ответ #29 : 17-06-2011 15:00 » |
|
какая интересная архитектура лефт джоинами просто с таблицами тут не обойтись... ща раскапаем Добавлено через 42 минуты и 4 секунды:ну собственно вот: 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
|
|
« Последнее редактирование: 17-06-2011 15:45 от PooH »
|
Записан
|
Удачного всем кодинга! -=x[PooH]x=-
|
|
|
|