Как сделать выборку фотографий из БД у которых более 3-х оценок?

Тема в разделе "Базы данных", создана пользователем verfaa, 6 дек 2013.

Модераторы: latteo
  1. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    375
    Симпатии:
    41
    Итак, есть таблица top100_votes, в которой хранятся оценки пользователей за фотографии (похоже на аналог топ-100 мамбы, лавплэнет и т.д.). Она имеет такой вид:

    id | id_foto | id_voter | mark | data

    id - id записи, auto_increment
    id_foto - айди фотографии за которую проголосовали
    id_voter - айди пользователя, который проголосовал за фото
    mark - оценка, которую он поставил, от 1 до 6
    data - время голосования

    Задача:
    Нужно сделать выборку фотографий у которых есть минимум 5 оценок, рассчитать среднюю оценку (т.е. сумму оценок разделить на количество голосов) и отсортировать в порядке убывания.
    Помогите написать SQL запрос пожалуйста, БД MySQL, тип таблицы MyISAM
     
  2. esche

    esche

    Регистр.:
    9 авг 2009
    Сообщения:
    360
    Симпатии:
    243
    Так примерно:
    Код:
    SELECT id_foto, COUNT(id_voter) as cnt, AVG(mark) as mark_avg
    FROM `top100_votes`
    GROUP by id_foto
    HAVING COUNT(id_voter) >= 5
    ORDER BY mark_avg DESC
     
    Последнее редактирование модератором: 7 янв 2014
    verfaa нравится это.
  3. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    375
    Симпатии:
    41
    Спасибо esche! Я добавил FROM tbl_name и все работает.
    А теперь вопрос намного сложнее:
    Есть ещё одна таблица fotos хранящая информацию о фотографиях юзеров, в ней кроме других полей есть поля:
    id_foto - айди фотографии
    id_user - айди пользователя, владельца фотографии

    И ещё одна таблица users с полями:
    id_user - айди пользователя
    gender - пол пользователя
    country - страна пользователя

    Допустим в скрипте у нас уже имеется пол и страна пользователя, например:
    Код:
    $country_id = 251;
    $gender = 2;
    Помогите сделать выборку фотографий из таблицы top100_votes, принадлежащих пользователям из страны $country_id, пола $gender у которых есть минимум 5 оценок, рассчитать среднюю оценку (т.е. сумму оценок разделить на количество голосов) и отсортировать в порядке убывания.
     
  4. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    375
    Симпатии:
    41
    Написал SQL запрос, который вроде работает правильно, но при тестировании обнаружил, что функция AVG считает среднее число неправильно!
    Вот такой SQL запрос у меня получился:
    Например для фото с ID 34950 в БД имеются следующие оценки:
    [​IMG]

    Но для фото ID 34950 скрипт выводит среднюю оценку 4.5 хотя, если посчитать средняя оценка 3.5
    Для других фото бывает выводит и 7, хотя максимальная оценка, которую можно поставить 6
    Помогите найти ошибку или переписать SQL запрос правильно.
    Тип поля mark - ENUM с возможными значениями 'skip','1','2','3','4','5','6'
     
  5. Prometeus

    Prometeus Постоялец

    Регистр.:
    9 дек 2013
    Сообщения:
    57
    Симпатии:
    12
    Возможных причин может быть несколько. Например, ограничение по пользователям только одной страны s.id_country = '44'.
    Или вот этот момент проверьте user_upload.id = top100_votes.id_foto, нам отсюда не видна структура таблиц и корретность связей.
    Приведите результат выполнения такого запроса, тогда всё можно будет расставить по своим местам:
    Код:
    SELECT a.id_foto, e.id_user AS id_owner, s.gender, s.id_country, a.id_user, a.mark AS mark_avg
    FROM top100_votes a
    JOIN user_upload e ON a.id_foto=e.id -- user_upload.id = top100_votes.id_foto ?
    LEFT JOIN user s ON e.id_user=s.id
    WHERE s.id_country = '44' and a.id_foto = 34950
     
    verfaa нравится это.
  6. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    375
    Симпатии:
    41
    окей спасибо, приду вечером домой и попробую.
    Кстати, вчера, в качестве отладки решил вывести значения COUNT(a.mark) и SUM(a.mark)
    И что интересно, COUNT(a.mark) вывело правильно - 6, а вот SUM(a.mark) - неправильно, что-то ок 30-33.
     
  7. Шёпот

    Шёпот Писатель

    Регистр.:
    29 ноя 2013
    Сообщения:
    9
    Симпатии:
    2
    COUNT - количество чисел, SUM - сумма этих чисел
     
  8. Prometeus

    Prometeus Постоялец

    Регистр.:
    9 дек 2013
    Сообщения:
    57
    Симпатии:
    12
    И? Скриншот видишь? Почему 30-33 вместо 21?
     
  9. Шёпот

    Шёпот Писатель

    Регистр.:
    29 ноя 2013
    Сообщения:
    9
    Симпатии:
    2
    И? Может в момент теста использовались другие данные, не?
     
  10. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    375
    Симпатии:
    41
    проблема уже решилась давно, а заключалась она в том, что использовался тип поля mark - ENUM
    У меня изначально были сомнения по поводу типа поля, поэтому в теме я указывал, что тип поля mark - ENUM
    На sql.ru сразу верный ответ дали:
    странно что тут никто не догадался, обычно самые толковые ответы дают именно на нулледе)
    Сделал новую таблицу, для поля mark выставил TINYINT, а если юзер пропускает фото просто заношу в БД 0.
    И все отлично стало работать.