How to select n% random rows with specific condition in SQL?

Question

I have two tables.

Table1 has two columns: brand and review_counter.

Table2 also has two columns: brand and review.

There are several reviews for each brand. Is there any way in SQL to randomly select around 10% of reviews for each brand, without using "top n" command?

For example for 'Sony' there are 2,005 reviews, and I need to select 10% of them, 200 reviews.

Thank you in advance.

Answer 1

Possible method using user counters. Compared to a solution similar to that above this is likely to bring back a more accurate 10% of reviews per brand, but it also likely to be slower (neither solution will be fast as both rely on using RAND() on every row on the tables).

This gets all the rows, ordered by brand and then RAND(). It uses that as a sub query and adds a sequence number, resetting back to 1 for the first record for each brand. Then that in turn is used as the source for a query which eliminates records where the generated sequence number is <= to a tenth of the reviews for that brand.

SELECT sub1.brand, sub1.review
FROM
(
    SELECT sub0.brand, sub0.reviews_wanted, sub0.review, @cnt:=IF(@brand = brand, @cnt+1, 1) AS cnt, @brand := brand
    FROM
    (
        SELECT Table1.brand, (Table1.review_counter * 0.1) AS reviews_wanted, Table2.review
        FROM Table1 
        INNER JOIN Table2 
        ON Table1.brand = Table2.brand
        ORDER BY Table1.brand, RAND()
    ) sub0
    CROSS JOIN (SELECT @cnt:=0, @brand:='') sub2
) sub1
WHERE cnt <= sub1.reviews_wanted

EDIT.

This might be a bit more memory efficient (although probably slower).

This has a sub query that gets the unique id of all the reviews for a brand in a random order, along with a count that is 1 tenth of the number of reviews for the brand. It then uses the count with SUBSTRING_INDEX to get the ids of the first random 10%, and joins that using FIND_IN_SET with the reviews table.

SELECT sub0.brand, Table2.review
FROM
(
    SELECT Table1.brand, CEIL(Table1.review_counter * 0.1) AS reviews_wanted, GROUP_CONCAT(Table2.id ORDER BY RAND()) AS id
    FROM Table1 
    INNER JOIN Table2 
    ON Table1.brand = Table2.brand
    GROUP BY Table1.brand, reviews_wanted
) sub0
INNER JOIN Table2
ON FIND_IN_SET(Table2.id, SUBSTRING_INDEX(sub0.id, ',', reviews_wanted))

You might be able to do something a bit more efficient using one of the solutions here:-

How can i optimize MySQL's ORDER BY RAND() function?

Answer 2

RAND() generates random values between 0 and 1. Why don't you try this?

UPDATED2

SELECT review
FROM 
( 
    SELECT review, (review_counter * RAND()) / review_counter AS rand
    FROM Table1 INNER JOIN Table2 ON Table1.brand = Table2.brand
) t
WHERE rand < 0.1