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