2

I have a table of itemproperties that I want to grab a random itemproperty from, however I want the random-ness of it to lean more towards some itemproperties than others.

To accomplish this I've setup a column called rarity that stores how often that item should show up (higher = more often), and one called rarity_position, which is configured using this query:

SET @i:=0;UPDATE itemproperty SET rarity_position = @i:=@i+rarity;

This provides the following results:

rarity results

Then to grab a random value, I thought I would use:

SELECT * FROM itemproperty 
WHERE id = (
        SELECT id 
        FROM itemproperty 
        WHERE rarity_position >= FLOOR
                (
                    RAND()*
                    (
                        SELECT MAX(rarity_position) FROM itemproperty
                    )
                )
        ORDER BY rarity_position 
        ASC LIMIT 1
) LIMIT 1

In order to select a random number with a maximum of the highest rarity_position, then grab the itemproperty with a rarity_position that is just above that.

Most of the time this works as expected, however about a third of the time no results come back.

Possibly relevant server info:

Software: MySQL
Software version: 5.0.95-log - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

You can reproduce the behaviour with this model :

create table itemproperty (
  id int, rarity int, rarity_position int
);
insert into itemproperty
values
  ( 1, 50, 50 ),
  ( 2, 50, 100 ),
  ( 3, 50, 150 ),
  ( 4, 50, 200 ),
  ( 5, 50, 250 ),
  ( 6, 50, 270 ),
  ( 7, 50, 320 ),
  ( 8, 50, 370 ),
  ( 9, 50, 420 ),
  ( 10, 50, 470 ),
  ( 11, 50, 520 )
;

If you try to do the subquery only, it works every time :

    SELECT id 
    FROM itemproperty 
    WHERE rarity_position >= FLOOR
            (
                RAND()*
                (
                    SELECT MAX(rarity_position) FROM itemproperty
                )
            )
    ORDER BY rarity_position 
    ASC LIMIT 1

But if you encapsulate it in :

SELECT * FROM itemproperty
WHERE id = (
 // here the above request
) LIMIT 1;

It will not work as expected.

What is causing it to return no results every time?

Alain Tiemblo
  • 36,099
  • 17
  • 121
  • 153
Jane Panda
  • 1,591
  • 4
  • 23
  • 51
  • 1
    Select the result of your function to see what the calculation's result is. It'll probably reveal the problem. – Madara's Ghost Nov 29 '12 at 22:15
  • 1
    +1, I can reproduce your problem and your subquery is working very well. Very interesting behaviour :-) – Alain Tiemblo Nov 29 '12 at 22:33
  • I've been trying to break it down for a few hours without much luck, the random values seem within the MAX value, and the IDs return valid. Another odd thing is occasionally I see two results. That could be a phpmyadmin bug though, and so I didn't include it. – Jane Panda Nov 29 '12 at 23:10

2 Answers2

2
SELECT *
FROM itemproperty 
ORDER BY RAND() * rarity DESC
LIMIT 1
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • That's clever, and if I understand it the query incorporates the weight of each itemproperty when it sorts them? – Jane Panda Nov 29 '12 at 23:20
  • Actually I tried using this method and it seems to completely ignore lower rarities – Jane Panda Nov 30 '12 at 00:07
  • @Bob: for how low? For the table from example it will appear ~3.8% times – zerkms Nov 30 '12 at 00:49
  • Maybe I'm misinterpreting how the rarities work in that query; I tried inserting 723 rows and these were the results: http://imgur.com/pNLkV The only one that was under 50 in the test that appeared was 4, and even that one was only 10 down from 50. – Jane Panda Nov 30 '12 at 01:25
  • 1
    Interestingly this seems to work: "ORDER BY -LOG(1.0 – RAND()) / rarity", from: http://www.kahunaburger.com/2008/10/13/selecting-random-weighted-records-from-mysql/#comment-42836 – Jane Panda Nov 30 '12 at 22:12
0

Found a workable solution similar to zerkms method:

ORDER BY -LOG(1.0 – RAND()) / rarity

Source

Notably, the missing pieces seem like they might be related to this question: MySQL query with RAND() subquery condition

I guess having RAND() in WHERE causes some weirdness.

Community
  • 1
  • 1
Jane Panda
  • 1,591
  • 4
  • 23
  • 51