shane shane - 2 months ago 17
MySQL Question

sql order by rand() with highest value shown more frequently

As title describes, im looking to write some SQL (mySQL DB) thats displays random records with highest value shown more frequently then others.

select CPC from advertisement ORDER BY RAND(), CPC DESC


The above code does not work as expected.

heres what im after:

I have 10 advertisers each setting their own cpc (cost per click) budget. The advertiser with the heighest budget will have his/hers advert show more frequently then the others, yet all being random.

user_id | cpc |
------------------
1 | 0.10 |
2 | 0.03 |
3 | 0.20 |
4 | 0.04 |
5 | 0.55 |
6 | 0.12 |


So user 5 will have his advert displayed more freqently then the others
5, 3, 6, 1, 4, 2 - in order of most impressions respectively.

Bit like I assume google adwords work, the higher the users budget the more impressions he/she will have.

I know no one likes RAND() due to performance but I will have no more then 100 advertisers.

Regards

Answer

I would say the best way to do this would be like so:

SELECT user_id, CPC
FROM advertisement 
ORDER BY RAND() * CPC DESC

This will make your adverts appear randomly, but user_id 5 will theoretically appear 5.5x more frequently than user_id 1, which is fair.

Comments