MySQL для подсчета количества первых 10 баллов, которые имеет пользователь.

У меня есть таблица MySQL с викторинами и таблица, в которой хранятся результаты этих викторин. Строка результата имеет идентификатор пользователя, идентификатор викторины (редактирование: временные метки) и числовой балл. Пользователи могут пройти тест более одного раза, но мы учитываем только первый раз, когда они проходят каждый тест.

Я пытаюсь сделать запрос, который скажет мне, сколько раз каждый пользователь появлялся в первой десятке викторины (при условии, что викторина закрыта), и ранжирую этих пользователей по тому, сколько раз они были в этом диапазоне. Это позволило бы мне отображать рейтинг за все время в зависимости от того, как они финишировали.

Любые идеи о том, как я могу это сделать?

Вот соответствующие части схемы.

Контрольный опрос:

  1. ID
  2. Открыто (отметка времени)
  3. Закрыть (отметка времени)

Пользователь:

  1. ID
  2. Электронная почта, имя и т. д. в качестве информации профиля

Результат викторины:

  1. ID
  2. Идентификатор викторины
  3. ID пользователя
  4. Время начала (отметка времени)
  5. Конец времени (отметка времени)
  6. Оценка (целое число)

Моя первая попытка была такой:

SELECT
distinct least(user_id, time_end) as user_score, result_id, quiz_id, user_id, score, time_end - time_start as duration
FROM results
WHERE time_end != 0 AND user_id != 1 AND user_id != 0
GROUP BY quiz_id, user_score
ORDER BY user_id, time_end ASC;

Это несколько полезно, но я понял, что иду в совершенно неправильном направлении, когда понял, что мне нужно подсчитать, сколько раз пользователь появлялся в первой десятке. точка (конструктор, здесь).


mysql sql
person Jonathan Stegall    schedule 11.09.2015    source источник
comment
Пожалуйста, опубликуйте соответствующие части вашей схемы и, что более важно, что вы пробовали до сих пор.   -  person Dave Zych    schedule 11.09.2015
comment
Объясните, пожалуйста, откуда мы знаем, какой балл они получили при первом прохождении конкретного теста, поскольку у нас есть только идентификатор пользователя, идентификатор теста и числовой балл.   -  person devlin carnate    schedule 12.09.2015
comment
Извините, отредактировано, чтобы уточнить это. В результате есть временные метки, и я также добавил пример схемы. У меня была и предыдущая попытка, но я не уверен, как двигаться в правильном направлении на данный момент.   -  person Jonathan Stegall    schedule 12.09.2015

Ответы (1)


arrow_upward
1
arrow_downward

  1. Во-первых, вам нужно установить, какие баллы получил каждый пользователь с первой попытки. Это проблема, известная как групповой минимум. , что можно решить, только найдя (quiz, user, time) каждой первой попытки и соединив его обратно с таблицей results; затем этот набор результатов можно отсортировать (по тесту и баллам), чтобы получить ранжирование первых попыток для каждого теста:

    SELECT   quiz_id, user_id, score
FROM     results NATURAL JOIN (
           SELECT   quiz_id, user_id, MIN(time_end) time_end
           FROM     results
           WHERE    time_end > 0
           GROUP BY quiz_id, user_id
         ) firstAttempts
ORDER BY quiz_id, score DESC

  2. Недостаточно взять «10 лучших» из каждой из этих групп, поскольку несколько пользователей могут разделить 10-й наивысший балл, и было бы несправедливо выбирать только некоторых из них. Поэтому мы должны найти этот 10-й наивысший балл, а затем найти каждого пользователя, который набрал по крайней мере столько же баллов с первой попытки.

    К сожалению, MySQL не поддерживает аналитические функции и, следовательно, не имеет удобного способа найти «10-ю запись» в каждой группе. Вместо этого мы можем использовать его пользовательские переменные для подсчета ранга как приведенный выше набор результатов создается, а затем фильтруется для желаемого ранга во внешнем запросе:

    SELECT quiz_id, score
FROM   (
         SELECT   quiz_id, score,
                  @rank    := IF(quiz_id = @quiz_id, @rank + 1, 1) AS rank,
                  @quiz_id := quiz_id
         FROM     (SELECT @rank := NULL, @quiz_id := NULL) init,
                  results NATURAL JOIN (
                    SELECT   quiz_id, user_id, MIN(time_end) time_end
                    FROM     results
                    WHERE    time_end > 0
                    GROUP BY quiz_id, user_id
                  ) firstAttempts
         ORDER BY quiz_id, score DESC
       ) rankedFirstAttempts
WHERE  rank = 10

  3. Теперь мы можем объединить этот набор из 10 самых высоких результатов с первыми попытками (которые мы можем сгенерировать снова, как в пункте 1 выше), затем сгруппировать по пользователям и отсортировать по количеству их появления:

    SELECT   user_id, COUNT(*) numberOfTimesInTop10
FROM     results NATURAL JOIN (
           SELECT   quiz_id, user_id, MIN(time_end) time_end
           FROM     results
           WHERE    time_end > 0
           GROUP BY quiz_id, user_id
         ) firstAttempts JOIN (
           SELECT   quiz_id, score,
                    @rank    := IF(quiz_id = @quiz_id, @rank + 1, 1) AS rank,
                    @quiz_id := quiz_id
           FROM     (SELECT @rank := NULL, @quiz_id := NULL) init,
                    results NATURAL JOIN (
                      SELECT   quiz_id, user_id, MIN(time_end) time_end
                      FROM     results
                      WHERE    time_end > 0
                      GROUP BY quiz_id, user_id
                    ) firstAttempts
           ORDER BY quiz_id, score DESC
         ) rankedFirstAttempts
        ON rankedFirstAttempts.rank     = 10
       AND rankedFirstAttempts.quiz_id  = results.quiz_id
       AND rankedFirstAttempts.score   <= results.score
GROUP BY user_id
ORDER BY numberOfTimesInTop10 DESC

Довольно ужасно, да?

person eggyal    schedule 11.09.2015
comment
Это интенсивно. Я запускаю ошибки, когда я запускаю его. Есть ли проблемы совместимости с PHP? Я пытался искать, но нахожу несколько противоречивые результаты. - person Jonathan Stegall; 12.09.2015
comment
@JonathanStegall: Какие ошибки вы видите? Очевидно, вам нужно будет изменить имена таблиц/столбцов, чтобы отразить вашу фактическую схему (те, которые использовались в вашем вопросе, были непоследовательными). - person eggyal; 12.09.2015
comment
Конечно. Я убедился, что имена столбцов/таблиц были правильными. Я получаю эту ошибку в Sequel Pro, а также в PHP (но на данный момент я больше пользуюсь Sequel Pro). Я меняю символы at на [at] ради StackOverflow: у вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, для правильного синтаксиса для использования рядом с '[at]rank := IF(nid = [at]nid, [at]rank + 1, 1) AS rank, [at]nid : = ni' в строке 9 - person Jonathan Stegall; 13.09.2015
comment
Это гениально и вуду одновременно! - person Jonathan Stegall; 13.09.2015