You need to use the running total to perform the same summation you described, but in SQL.

Assuming you have tables like this:

CREATE TABLE items( id int(10) auto_increment, score int(10), PRIMARY KEY(id) ); CREATE TABLE purchases( id int(10) auto_increment, item_id int(10), PRIMARY KEY(id), FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE ); 

The query you're looking for is something like this:

SELECT a.id, SUM(i.score) as running_total FROM purchases a, purchases b JOIN items i ON i.id = b.item_id WHERE b.id <= a.id GROUP BY a.id HAVING SUM(i.score) >= (SELECT FLOOR(RAND() * (SELECT SUM(i.score) from purchases a JOIN items i ON i.id = a.item_id))) ORDER BY a.id LIMIT 1; 

I create the running total by joining the table on itself, using ID for ordering. b.id <= a.id makes sure that running total only takes previous purchases.

Then I apply a filter SUM(i.score) >= (SELECT FLOOR(RAND() * (SELECT SUM(i.score) from purchases a JOIN items i ON i.id = a.item_id))) and LIMIT 1 to get just one result.

See SQLFiddle for step-by-step explanation.

The only concern is performance, you have to try it on your actual data. If it's bad, I believe there is a way to optimize this. Replacing HAVING with another WHERE condition is probably a good way to start.

Related article: NoSQL? No, SQL! – How to Calculate Running Totals

You need to use the running total to perform the same summation you described, but in SQL.

Assuming you have tables like this:

CREATE TABLE items( id int(10) auto_increment, score int(10), PRIMARY KEY(id) ); CREATE TABLE purchases( id int(10) auto_increment, item_id int(10), PRIMARY KEY(id), FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE ); 

The query you're looking for is something like this:

SELECT a.id, SUM(i.score) as running_total FROM purchases a, purchases b JOIN items i ON i.id = b.item_id WHERE b.id <= a.id GROUP BY a.id HAVING SUM(i.score) >= (SELECT ROUND(RAND() * (SELECT SUM(i.score) from purchases a JOIN items i ON i.id = a.item_id))) ORDER BY a.id LIMIT 1; 

I create the running total by joining the table on itself, using ID for ordering. b.id <= a.id makes sure that running total only takes previous purchases.

Then I apply a filter SUM(i.score) >= (SELECT ROUND(RAND() * (SELECT SUM(i.score) from purchases a JOIN items i ON i.id = a.item_id))) and LIMIT 1 to get just one result.

See SQLFiddle for step-by-step explanation.

The only concern is performance, you have to try it on your actual data. If it's bad, I believe there is a way to optimize this. Replacing HAVING with another WHERE condition is probably a good way to start.

Related article: NoSQL? No, SQL! – How to Calculate Running Totals