jeudi 7 avril 2022

Applying a tolerance when using 'ORDER BY RAND()' in MySQL?

I have a MySQL table of companies, each company has a popularity score, (the higher the integer, the more popular the company). I'm wanting to select 10 random companies then order by most popular to least. However, I want to favor companies that have a higher popularity score, but not totally exclude companies with a lower popularity score. So in other words, I'm looking for some kind of tolerance system that favors more popular companies.

This is the solution I've come up with so far:

SELECT n.company FROM (
    
    (
        SELECT company, popularity FROM companies
        WHERE popularity >= (
            SELECT ROUND(AVG(popularity) * 0.8) FROM companies
        )
        ORDER BY RAND() LIMIT 5
    )
    
    UNION
    
    (
        SELECT company, popularity FROM companies
        WHERE popularity >= (
            SELECT ROUND(AVG(popularity) * 0.6) FROM companies
        )
        AND popularity < (
            SELECT ROUND(AVG(popularity) * 0.8) FROM companies
        )
        ORDER BY RAND() LIMIT 3
    )
    
    UNION
    
    (
        SELECT company, popularity FROM companies
        WHERE popularity < (
            SELECT ROUND(AVG(popularity) * 0.6) FROM companies
        )
        ORDER BY RAND() LIMIT 2
    )
    
) AS n ORDER BY n.popularity DESC;

What I'm doing is combining three different selects.

  • The first is selecting 5 random companies whose popularity score is equal to or greater than the top 20% of the average popularity score.

  • The second is selecting 3 random companies whose popularity score is equal to or greater than the top 40% of the average popularity score, but lower than the top 20% of average popularity score.

  • And finally the third is selecting 2 random companies whose popularity score is lower than the top 40% of the average popularity score.

This works, but it just seems a bit clunky and over-engineered. Can anybody suggest a more efficient way of including a tolerance system?




Aucun commentaire:

Enregistrer un commentaire