When you compute $$C(x_p, X_p)$$, you do the sum
$$\displaystyle C(x_p, X_p) = \frac{1}{S - 1} \sum_{s \in S} (x_p(s) - \bar{x}_p)(X_p(s) - \bar{X}_p)$$
which involves a term for each student combining their question score and rest of test score. That is, you start with an array of $$x_p(s)$$ with an array of corresponding $$X_p(s)$$, one for each $$s$$. To compute $$C_{max}(x_p, X_p)$$, you just sort these two arrays before applying the above formula. That is, for the purpose of computing $$C_{max}$$, you pretend that the first student scored the lowest $$x_p$$ and the lowest $$X_p$$, the second student scored the second lowest $$x_p$$ and the second lowest $$X_p$$, and so on to the last student, who scored the highest $$x_p$$ and $$X_p$$.
I have managed to do the bulk of the work calculating skewness, kurtosis and standard deviation of sets of grades in sql and avoided having to load the whole set of grades into memory. See here for some background of the calculations we are doing and here for the how we are calculating these in practise using sql that will work cross-db.
But both me and Tim Hunt are stumped as to how to do the $$C_{max}(x_p, X_p)$$ calc in sql.
$$\displaystyle C(x_p, X_p) = \frac{1}{S - 1} \sum_{s \in S} (x_p(s) - \bar{x}_p)(X_p(s) - \bar{X}_p)$$
Could be calculated with the following sql :
SUM((qs.grade - ?)*((qa.sumgrades - qs.grade) - ?))
Since qs.grade is selecting the grade for a question from the question state table and qa.sumgrades selects the whole attempt grade from the question attempt table.
We would fetch the averages in another query and replace the ? marks with the appropriate average. We would then divide the result by S-1 in php.
The problem - a general sql question
The problem with the $$C_{max}(x_p, X_p)$$ calculation is how to take two columns and seperately sort them. If we could do this we could feed the columns into our SUM calculation.
We thought it might be possible to use an extra table and do an INSERT ... SELECT ..... ORDER BY ... and then maybe do a JOIN to get the data out of that table. But there is no way number the rows we are inserting into the db and so to get them out in the order we are putting them in and join the values extracted and sorted seperately back into one table.
and I had to use those horizontal rulers above to mark quoted text.
