Как в mysql запрашивать значения выше, выше и ниже для каждой оценки в нескольких тестах? Бонусные баллы, если есть рассчитанный процентильный рейтинг

tbl Scores: (csv; я не знаю, как форматировать схему таблицы здесь)

test_id, student_id, score   
1, 1, 75  
1, 2, 84  
1, 3, 60  
2, 1, 82  
1, 4, 90  
1, 5, 75  
2, 2, 70  
2, 3, 90  
2, 5, 76

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

Я использовал запрос для успешного подсчета количества студентов по каждому баллу:

SELECT test_id, score, COUNT(student_id) as num_students FROM Scores GROUP BY test_id, score

На данный момент я пытаюсь получить #в, #ниже и #выше для каждой оценки в каждом тесте, чтобы я мог попытаться вычислить процентиль для каждого.

Будет ли это какая-то сводная таблица?


mysql sql pivot-table subquery
person user2134017    schedule 05.03.2013    source источник

Ответы (2)


arrow_upward
0
arrow_downward

В MySQL вам нужно сделать это с помощью коррелированного подзапроса. Ниже приведен пример количества учащихся:

 SELECT test_id, score, COUNT(student_id) as num_students,
        (select count(*) from scores s2 where s2.test_id = s.test_id and s2.score < s.score
        ) as below
 FROM Scores s
 GROUP BY test_id, score
person Gordon Linoff    schedule 05.03.2013
comment
На самом деле это примерно 1 миллион записей для ~ 300 тестов для ~ 4 тыс. уникальных оценок. Это для отчетов и доступ только несколько раз в год. Это кажется довольно дорогим запросом, почти сродни грубому форсированию в php (хотя определение проще в sql). Ради обучения, есть ли более быстрый способ сделать это? БЛАГОДАРНОСТЬ - person user2134017; 05.03.2013
comment
Не могли бы вы уточнить, почему должен быть коррелированный подзапрос? - person Jakub Kania; 05.03.2013
comment
@JakubKania . . . Потому что MySQL не предлагает никакого другого механизма для кумулятивных сумм. Вы можете выразить ту же идею с помощью неравноправного соединения, но это по существу эквивалентно. - person Gordon Linoff; 05.03.2013
comment
@ Гордон Линофф, кстати, это подводит меня к следующему шагу, спасибо. На моем полном наборе данных я, вероятно, смогу пойти пообедать, пока буду запускать это, но я смогу вывести необходимые данные в одном запросе! - person user2134017; 05.03.2013

arrow_upward
0
arrow_downward

Он не обязательно должен быть коррелирован, но, вероятно, он будет работать хуже из-за перекрестного соединения и отдельных я не уверен, будет ли он работать лучше или хуже:

SELECT s.test_id, 
       s.score, 
       Count(DISTINCT s.student_id) AS num_students, 
       Count(DISTINCT z.student_id) AS below 
FROM   scores s 
       LEFT JOIN scores z 
              ON s.test_id = z.test_id 
                 AND s.score > z.score 
GROUP  BY s.test_id, 
          s.score 
ORDER  BY s.test_id, 
          s.score;

Y http://sqlfiddle.com/#!2/fd824/13

person Jakub Kania    schedule 05.03.2013