Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By mlseim

Thread: How do I randomize with higher rating on some parts?

  1. #1
    Senior Member
    Join Date
    Aug 2011
    Posts
    227
    Member #
    29153
    Liked
    4 times

    How do I randomize with higher rating on some parts?

    I have a list of data stored in a database that I want to come up on a page randomly. I have some that I want to come up more often than others. I have them rated on a scale of 1 to 10. I want the 10's to show up most often and the 1's less often. How do I get this to happen?

    The rating won't be in whole numbers. may be 3.2 or 7.333333..... etc.

  2.  

  3. #2
    Senior Member
    Join Date
    Aug 2011
    Posts
    227
    Member #
    29153
    Liked
    4 times
    I forgot to say, I'm using php and MySQL

  4. #3
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,483
    Member #
    425
    Liked
    2783 times
    You're looking for what's known as a weighted random sample. Here's how you handle it in general terms (PHP/MySQL is not a specific implementation I've tried).

    1) Add up all the ratings to make one big number.
    2) Pick a number at random between 0 and the big number.
    3) Go through your list of data, starting with the biggest numbers first, until you get to a number that is greater than your random number (or until you get to the end of the list).
    4) Show whatever record that is.

    The bigger ratings will not only be more likely to show up because of their relation to the big number, but also since you're starting from the biggest number and working downward.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  5. #4
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,723
    Member #
    5580
    Liked
    718 times
    Glenn, how many total rows would you expect to have in your database at the most? 1000, 100000?

    I think the method to list weighted random would be different depending on table size.

    Also, I think you are trying to display the entire list randomly, but weighted? Or are you just picking one row from the list?


    @TheGame,
    For picking one number (or row), that might be OK, but what if the entire list, or the first 10 of them need to be displayed? I'm not sure how you would generate a complete list from MySQL.


    EDIT:
    I of course Googled this too ...

    From all of the results, I thought this one was the best (untested).

    SELECT * FROM `table` ORDER BY -LOG(1.0 – RAND())/rating

    This will result in a random listing of your whole list by weighted "rating" 0.1 to 10.0
    The rating must be greater than 0.0, so 0.1 would be the smallest rating. You mentioned that your rating starts at 1.0, so that won't be an issue.

    The key point to all of my findings are that RAND produces a real number between 0 and 1. The word "between" would indicate that the value would never be 0 or would never be 1.
    Last edited by mlseim; Mar 08th, 2015 at 11:39 AM.
    TheGAME1264 likes this.


  6. #5
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,483
    Member #
    425
    Liked
    2783 times
    mlseim: the way I've handled it through MS-SQL is usually through a stored procedure. A sum query to get the weighted random sum, generate the random number, a query to retrieve the associated record, subtract the weight from the random sum, repeat steps 2-4 as needed. It has worked well with several hundred thousand records.

    Hence the reason I said "in general terms". You know PHP and I. We don't play nicely together.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:55 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com