0

I want to query the DB and pick a random item weighted by the count. Something like so:

SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --)
    ORDER BY ct * RAND() LIMIT 1

I'm thinking the following might be a good approach. I have a table that looks something like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 3   | item c | 4     |

How can I query the database so that 1 row is shown per count? I.e. like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 2   | item b | 2     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |

In case it helps, I've created a DB fiddle: https://www.db-fiddle.com/f/wRZgBYkDM18c5fk7tgkBkA/0

I'm using MariaDB 10.0.

2
  • What is the purpose of the resulting set of rows?
    – Rick James
    Commented Nov 2, 2018 at 4:57
  • @RickJames I want to query the DB and pick a random item weighted by the count. Something like so: SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --) ORDER BY RAND() LIMIT 1
    – Zaki Aziz
    Commented Nov 2, 2018 at 15:39

2 Answers 2

0

Instead of proliferating the number of rows, let's add a cumulative total:

| id  | item   | count | cumm_tot
| --- | ------ | ----- |
| 1   | item a | 3     |  3
| 2   | item b | 2     |  5
| 3   | item c | 4     |  9

Then get the last number, 9, for use below.
Index the new column.
Fetch random item thus:

WHERE    cumm_tot > 9 * RAND()
ORDER BY cumm_tot
LIMIT 1

To set the column and get the 9, perform this task:

SET @c := 0;
UPDATE tbl
    SET cumm_tot = (@c := @c + `count`)
    ORDER BY id;
-- @c will now be the total (9)

Whenever you add items or change the weights, rerun the two statements above.

(This code should work on any version of MySQL/MariaDB. There may be a "window function" that works better in new versions.)

0
SELECT * FROM thing ORDER BY RAND()/ct LIMIT 1
1
  • Always try to write down to some explanation statement of the code. For better understanding to other professional. Commented Nov 3, 2018 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.