Calculating ad impression rank

Question

I am currently working on a database ad rotating system where some of the ads have higher impression (or rank) than others and should be shown more often.

What is the best way for calculating the impression rank while still keeping the appearance of "random" ads? What would the table structure for the database look like? Should the calculation be done in the database or in the code?

Answer 1

here is a great solution which allows for weighting, its coldfusion but the sql is there too article

DECLARE @girl TABLE (
id INT IDENTITY( 1, 1 ),
name VARCHAR( 50 ),
weight INT
);


INSERT INTO @girl
(
name,
weight
)(
SELECT 'Sarah', 100 UNION ALL
SELECT 'Libby', 30 UNION ALL
SELECT 'Lisa', 30 UNION ALL
SELECT 'Molly', 250 UNION ALL
SELECT 'Kit', 50
);

SELECT
g.id,
g.name,
g.weight
FROM
@girl g
INNER JOIN
(

--
--In this inner query, we need to select a random,
--weighted ID. We are doing this in the inner query
--rather than in the outter query so that our
--intermediary table doesn't need to contain so
--much information (just the ID).
--->
SELECT TOP 1
g.id
FROM
@girl g
INNER JOIN
pivot1000 p
ON
(
-- Use the weights. --->
g.weight >= p.id

--Use any additional filtering that is required by the business logic of the query criteria.

AND
g.name != 'Lisa'
)
ORDER BY
-- Select random row. --->
NEWID() ASC

) AS temp_id
ON
g.id = temp_id.id

Answer 2

Some related Questions that may be helpful as well:

• Random Weighted Choice in T-SQL
• Random weighted choice

Answer 3

We have written a MySQL function for our open source ad server AdServerBeans MyAds (http://www.adserverbeans.com) that selects a banner randomly taking into account traffic shares:

DELIMITER ;;
DROP FUNCTION if exists get_random_banner_by_traffic_share;
CREATE FUNCTION get_random_banner_by_traffic_share(valid_banners TEXT, total_traffic_share INTEGER)
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
       DECLARE pos INTEGER DEFAULT 1;
       DECLARE rnd INTEGER DEFAULT 0;
       DECLARE current_traffic_share INTEGER DEFAULT 0;
       DECLARE banner_id INTEGER;
       DECLARE banner_traffic_share INTEGER;
       SET rnd = RAND()*(total_traffic_share-1)+1;
       WHILE pos < LENGTH(valid_banners) DO       
         SET pos = POSITION(';' IN valid_banners);
         SET banner_id = CONVERT(SUBSTR(valid_banners,1,pos-1),SIGNED);
         SET valid_banners=SUBSTRING(valid_banners FROM pos+1);
         SET pos = POSITION(';' IN valid_banners);
         SET banner_traffic_share = CONVERT(SUBSTR(valid_banners,1,pos-1),SIGNED);
         SET valid_banners=SUBSTRING(valid_banners FROM pos+1);
         if(current_traffic_share < rnd and rnd <= (banner_traffic_share+current_traffic_share)) THEN
            RETURN banner_id;
         END IF;
         SET current_traffic_share=current_traffic_share+banner_traffic_share;
       END WHILE;
END;
;;
delimiter ;

MySQL stored procedures/functions do not support arrays/lists. So we had to use a string with delimiters. In this function pay attention to the line:

SET rnd = RAND()*(total_traffic_share-1)+1;

that randomly selects a value from 1 to 100. We then look for the banner that lies within that range. Please note that this algorithm is probably ok for a small amount of ads targeting the same ad place. You might have a different story.