CODEWITHSUNDEEP
phpsqlrandom
oxo
oxo

Reputation: 4451

Weighted randomness. How could I give more weight to rows that have just been added to the database?

I'm retrieving 4 random rows from a table. However, I'd like it such that more weight is given to rows that had just been inserted into the table, without penalizing older rows much.

Is there a way to do this in PHP / SQL?

Upvotes: 3

Views: 168

Answers (3)

Nathan Lippi
Nathan Lippi

Reputation: 5237

I think an agreeable solution would be to use an asymptotic function (1/x) in combination with weighting.

The following has been tested:

SELECT *, (Rand()*10 + (1/(max_id - id + 1))) AS weighted_random 
FROM tbl1 
ORDER BY weighted_random 
DESC LIMIT 4

If you want to get the max_id within the query above, just replace max_id with:

(SELECT id FROM tbl1 ORDER BY id DESC LIMIT 1)

Examples:

Let's say your max_id is 1000 ...

For each of several ids I will calculate out the value:

1/(1000 - id + 1) , which simplifies out to 1/(1001 - id):

id: 1000

1/(1001-1000) = 1/1 = 1

id: 999

1/(1001-999) = 1/2 = .5

id: 998

1/(1001-998) = 1/3 = .333

id: 991

1/(1001-991) = 1/10 = .1

id: 901

1/(1001-901) = 1/100 = .01

The nature of this 1/x makes it so that only the numbers close to max have any significant weighting.

You can see a graph of + more about asymptotic functions here:

http://zonalandeducation.com/mmts/functionInstitute/rationalFunctions/oneOverX/oneOverX.html

Note that the right side of the graph with positive numbers is the only part relevant to this specific problem.

Manipulating our equation to do different things:

(Rand()*a + (1/(b*(max_id - id + 1/b))))

I have added two values, "a", and "b"... each one will do different things:

The larger "a" gets, the less influence order has on selection. It is important to have a relatively large "a", or pretty much only recent ids will be selected.

The larger "b" gets, the more quickly the asymptotic curve will decay to insignificant weighting. If you want more of the recent rows to be weighted, I would suggest experimenting with values of "b" such as: .5, .25, or .1.

The 1/b at the end of the equation offsets problems you have with smaller values of b that are less than one.

Note:

This is not a very efficient solution when you have a large number of ids (just like the other solutions presented so far), since it calculates a value for each separate id.

Upvotes: 2

NikiC
NikiC

Reputation: 101936

... ORDER BY (RAND() + 0.5 * id/maxId)

This will add half of the id/maxId ration to the random value. I.e. for the newest entry 0.5 is added (as id/maxId = 1) and for the oldest entry nothing is added.

Similarly you can also implement other weighting functions. This depends on how exactly you want to weight the values.

Upvotes: 1

cutsoy
cutsoy

Reputation: 10251

SELECT *, (RAND() / id) AS o FROM your_table ORDER BY o LIMIT 4

This will order by o, where as o is some random integer between 0 and 1 / id, which means, the older your row, the lower it's o value will be (but still in random order).

Upvotes: 3

Related Questions