CODEWITHSUNDEEP
mysqlsql
shavit
shavit

Reputation: 1063

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.

Upvotes: 0

Views: 126

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

While I'm glad the answer Gordon Linoff provides works for you, you should understand it's quite specific. ORDER BY, per the SQL standard, has no effect on how a query is processed, and SQL does not recognize "iteration" in a SELECT statement. So the idea of "reducing a variable on each iteration", where the iteration order is governed by ORDER BY has no basis in standard SQL. You might want to check if it's guaranteed by MySQL, just for your own edification.

To achieve the effect you want in a standard way, proceed as follows.

  1. Create a table Percentiles( Percentile int not null, Factor float not null )
  2. Populate that table with your factors (20 rows).
  3. Write a view or CTE that ranks your points in descending order. Let us call the rank column rank.

Then join your view to Percentiles: 

SELECT auth, sum(points * factor) as weight
FROM "your view" as t join percentiles as p
ON r.rank = percentile
WHERE points > 0.0 
GROUP BY auth

That query is simple, and its intent obvious. It might even be faster. Most important, it will definitely work, and doesn't depend on any idiosyncrasies of your current DBMS.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 3

Related Questions