StackOverflow's Popularity algorithm in MySQL
How w开发者_StackOverflow中文版ould you write SO's Popularity algorithm in MySQL?
The algorithm is detailed here: Popularity algorithm.
thanks!
It's relatively simple.
t = (time of entry post) - (Dec 8, 2005)
You would convert the date values to timestamps (you can use unix_timestamp
), which gives you an integer that can be used in the rest of the comparisons.
x = upvotes - downvotes
This one should be pretty easy... obviously MySQL supports subtraction.
y = {1 if x > 0, 0 if x = 0, -1 if x < 0)
z = {1 if x < 0, otherwise x}
For these, take a look at MySQL's case
statement.
log(z) + (y * t)/45000
MySQL has a log
function, so this one should be easy too, just simple math.
And, you tie it all together with a select
statement. You can store intermediate calculations in your select
statement using user-defined variables. For example:
select @x := (upvotes - downvotes) as x,
(@x > 4) as isXGreaterThanFour
精彩评论