How to add weights to a MySQL table and select random values according to these?
I found this nice little algorithm in Quod Libet. You could probably translate it to some procedural SQL.
function WeightedShuffle(list of items with weights):
max_score ← the sum of every item’s weight
choice ← random number in the range [0, max_score)
current ← 0
for each item (i, weight) in items:
current ← current + weight
if current ≥ choice or i is the last item:
return item i
selecting random rows based on weight on another row
I think the difficult part here is getting any individual row to potentially appear more than once. I'd look into doing something like the following:
1) Build a temp table, duplicating records according to their frequency (I'm sure there's a better way of doing this, but the first answer that came to my mind was a simple while loop... This particular one really only works if the frequency values are integers)
create table #dup
(
id int,
nm varchar(10)
)
declare @curr int, @maxFreq int
select @curr=0, @maxFreq=max(freq)
from tbl
while @curr < @maxFreq
begin
insert into #dup
select id, nm
from tbl
where freq > @curr
set @curr = @curr+1
end
2) Select your top records, ordered by a random value
select top 10 *
from #dup
order by newID()
3) Cleanup
drop table #dup
mySQL select random with probability
SELECT
IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour
FROM
(SELECT @rnd:=rand()) AS rndinit;
Gives you the flavour with the requested probabilities.
SELECT * FROM tablename ORDER BY rand() LIMIT 1
gives you a single random row. Now we put it together:
SELECT
tablename.*
FROM
tablename
INNER JOIN (
SELECT
IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour
FROM
(SELECT @rnd:=rand()) AS rndinit
) AS rndview ON rndview.rndflavour=tablename.flavour
ORDER BY rand()
LIMIT 1
Related Topics
Sql Server Bug or Feature? Decimal Numbers Conversion
Sql Azure Backup & Restore Strategy
How to Relate 3 Tables Depending on Event
Retrieve The Most Recent Record for Each Customer
Sql "If Exists..." Dynamic Query
Postgresql - Conditional Ordering
Simple Db2 Query for Connection Validation
Best Way to Change Clustered Index (Pk) in SQL 2005
What Is The Query to Get "Related Tags" Like in
Wordpress: Automatically Delete Posts That Are X Days Old
Orm or Something to Handle SQL Tables with an Order Column Efficiently
MySQL Procedure to Update Numeric Reference in Previous Rows When One Is Updated
Sql Select Rows Containing Part of String
Executing SQL Query on Multiple Databases
Why Doesn't Oracle Allow Consecutive Newline Characters in Commands
How to Retrieve The Date Part Out of a Datetime Result Column in Sqlite
Why Am I Getting a an Error When Creating a Generated Column in Postgresql