shavit shavit - 3 months ago 8
SQL Question

Usage of weighting in pure SQL

My front-end (SourcePawn) currently does the following:

float fPoints = 0.0;
float fWeight = 1.0;

while(results.FetchRow())
{
fPoints += (results.FetchFloat(0) * fWeight);
fWeight *= 0.95;
}


In case you don't understand this code, it goes through the resultset of this query:

SELECT points FROM table WHERE auth = 'authentication_id' AND points > 0.0 ORDER BY points DESC;


The resultset is floating numbers, sorted by
points
from high to low.

My front-end takes the 100% of the first row, then 95% of the second one, and it drops by 5% every time. It all adds up to
fPoints
that is my 'sum' variable.

What I'm looking for, is a solution of how to replicate this code in pure SQL and receive the sum which is called
fPoints
in my front-end, so I will be able to run it for a table that has over 10,000 rows, in one query instead of 10,000.

I'm very lost. I don't know where to start and guidance of any kind would be very nice.

Answer

You can do this using variables:

SELECT points,
       (points * (@f := 0.95 * @f) / 0.95) as fPoints
FROM table t CROSS JOIN
     (SELECT @f := 1.0) params
WHERE auth = 'authentication_id' AND points > 0.0
ORDER BY points DESC;

A note about the calculation. The value of @f starts at 1. Because we are dealing with variables, the assignment and the use of the variable need to be in the same expression -- MySQL does not guarantee the order of evaluation of expressions.

So, the 0.95 * @f reduces the value by 5%. However, that is for the next iteration. The / 0.95 undoes that to get the right value for this iteration.

Comments