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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `advertisement` ADD INDEX `advertisement_idx_cpc` (`CPC`);
SELECT advertisement.CPC FROM advertisement ORDER BY RAND(), advertisement.CPC DESC