Mysql RAND() function based on some other values

255
January 29, 2017, at 2:07 PM

I have this table structure:

  id | product | value
-----+---------+--------
  1  |  test1  |  10
-----+---------+--------
  2  |  test2  |  30
-----+---------+--------
  3  |  test3  |  25
-----+---------+--------
  4  |  test4  |  20
-----+---------+--------
  5  |  test5  |  15
-----+---------+--------
  6  |  test6  |  35
-----+---------+--------

This are some products that I want to recommend to my users, and I am intrested to show 4 products on my homepage (not all of them), the most valuable products with one exception.

So normally I want to display all the time this products: test6, test2, test3, test4. The only problem is that I want from time to time to display some of the other products instead, but with a lower rate. So if I didn't want this I would probably order by value desc. But the thing is that I don't want my users to get bored to see the same 4 products all the time, I want from time to time to insert one or two other products in my list.

Now I know that mysql has a RAND() function but I don't know how I could use that to make a query to do exactly what I want.

Matematically it should work like this:

We add all the values togheter 10+30+25+20+15+35 = 135, we apply the rule of thirds to see the random rate for each product.

135 = 100%
------------
10  = 7.4%
30  = 22.2%
25  = 18.5%
20  = 14.8%
15  = 11.1%
35  = 25.9%

So considering this percentages I want to make a query which will return a list of 4 products using random but also considering which product has more chances to apear based on those percentages.

Don't ask me what I tried yet because I am not an mysql expert, I know how to do this in PHP but I want to know if this is even possible using only mysql, and how I can do this.

Answer 1

You can do this with variables. Take the cumulative sum, normalize it, and then use rand():

select t.*
from (select t.*, (@v := @v + value) as running_value
      from t cross join
           (select @v := 0) params
     ) t cross join
     (select sum(value) as sum_value
      from t
     ) total
order by (rand() between (t.running_value - t.value)/total.sum_value and t.running_value / total.sum_value) desc,
         rand();

The above can be simplified. Also, the rand() in the order by may be re-computed a lot of times. I think this is better:

select t.*
from (select t.*, (@v := @v + value) as running_value, rand() as rnd
      from t cross join
           (select @v := 0) params
     ) t 
order by (rnd between (t.running_value - t.value)/@v and t.running_value/@v) desc
READ ALSO
Creating JSON from Mysql with JDBC

Creating JSON from Mysql with JDBC

I have the below data in my database and I fetch it using JDBC:

251
Delete from multiple tables with SQL in PHP

Delete from multiple tables with SQL in PHP

So I am trying to deleting rows from multiple tablesI have looked at the following questions but none of them seem to solve my issue

248
Not inserting value to vb.net using mysql

Not inserting value to vb.net using mysql

I am trying to insert values to my database but it doesn't seem to work

203