php - two - MySQL: Seleccione entrada aleatoria, pero peso hacia ciertas entradas

random mysql

Question

Tengo una tabla MySQL con un montón de entradas y una columna llamada "Multiplicador". El valor predeterminado (y el más común) para esta columna es 0, pero podría ser cualquier número.

Lo que tengo que hacer es seleccionar una sola entrada de esa tabla al azar. Sin embargo, las filas se ponderan según el número en la columna "Multiplicador". Un valor de 0 significa que no está ponderado en absoluto. Un valor de 1 significa que se pondera el doble, como si la entrada estuviera en la tabla dos veces. Un valor de 2 significa que se pondera tres veces más, como si la entrada estuviera en la tabla tres veces.

Estoy tratando de modificar lo que mis desarrolladores ya me han dado, así que lo siento si la configuración no tiene mucho sentido. Probablemente podría cambiarlo pero quiero mantener la mayor cantidad posible de la configuración de la tabla existente.

He estado tratando de averiguar cómo hacer esto con SELECT y RAND (), pero no sé cómo hacer la ponderación. ¿Es posible?

Answer 1

Aunque me doy cuenta de que esta es una pregunta en MySQL, lo siguiente puede ser útil para alguien que usa SQLite3 que tiene implementaciones sutilmente diferentes de RANDOM y LOG.

SELECT * FROM table ORDER BY (-LOG(abs(RANDOM() % 10000))/weight) LIMIT 1;

peso es una columna en la tabla que contiene enteros (he utilizado 1-100 como el rango en mi tabla).

RANDOM () en SQLite produce números entre -9.2E18 y + 9.2E18 (consulte los documentos de SQLite para obtener más información). Usé el operador de módulo para bajar el rango de números un poco.

abs () eliminará los negativos para evitar problemas con LOG que solo maneja números positivos distintos de cero.

LOG () no está realmente presente en una instalación predeterminada de SQLite3. Usé la llamada php SQLite3 CreateFunction para usar la función php en SQL. Consulte los documentos de PHP para obtener información sobre esto.

Answer 2

Bueno, yo pondría la lógica de los pesos en PHP:

<?php
    $weight_array = array(0, 1, 1, 2, 2, 2);
    $multiplier = $weight_array[array_rand($weight_array)];
?>

y la consulta:

SELECT *
FROM `table`
WHERE Multiplier = $multiplier
ORDER BY RAND()
LIMIT 1

Creo que funcionará :)

Answer 3

El resultado del pseudocódigo (rand(1, num) % rand(1, num)) aumentará más hacia 0 y hacia num. Reste el resultado de num para obtener lo opuesto.

Entonces, si el lenguaje de mi aplicación es PHP, debería verse más o menos así:

$arr = mysql_fetch_array(mysql_query(
    ''SELECT MAX(`Multiplier`) AS `max_mul` FROM tbl''
));
$MaxMul = $arr[''max_mul'']; // Holds the maximum value of the Multiplier column

$mul = $MaxMul - ( rand(1, $MaxMul) % rand(1, $MaxMul) );

mysql_query("SELECT * FROM tbl WHERE Multiplier=$mul ORDER BY RAND() LIMIT 1");

Explicación del código de arriba:

1. Obtenga el valor más alto en la columna Multiplicador
2. calcule un valor Multiplicador aleatorio (ponderado hacia el valor máximo en la columna Multiplicador)
3. Obtenga una fila aleatoria que tenga ese valor Multiplier

También se puede lograr simplemente mediante el uso de MySQL.

Demostrando que el pseudocódigo (rand(1, num) % rand(1, num)) pesará hacia 0: ejecuta el siguiente código PHP para ver por qué (en este ejemplo, 16 es el número más alto):

$v = array();

for($i=1; $i<=16; ++$i)
    for($k=1; $k<=16; ++$k)
        isset($v[$i % $k]) ? ++$v[$i % $k] : ($v[$i % $k] = 1);

foreach($v as $num => $times)
        echo ''<div style="margin-left:'', $times  ,''px">
              times: '',$times,'' @ num = '', $num ,''</div>'';

Answer 4

Hagas lo que hagas, es terrible ser terrible porque implicará: * Obtener el total de "ponderaciones" para todas las columnas como UN NÚMERO (incluyendo la aplicación del multiplicador). * Obteniendo un número aleatorio entre 0 y ese total. * Obteniendo todas las entradas y ejecutándolas, deduciendo el peso del número aleatorio y eligiendo una entrada cuando se queden sin artículos.

En promedio, correrás a lo largo de la mitad de la mesa. Rendimiento - a menos que la tabla sea pequeña, hágalo fuera de mySQL en la memoria - será LENTO.

Answer 5

No use 0, 1 y 2, sino 1, 2 y 3. Entonces puede usar este valor como multiplicador:

SELECT * FROM tablename ORDER BY (RAND() * Multiplier);

Answer 6

Para otros googlear este tema, creo que también puedes hacer algo como esto:

SELECT strategy_id
FROM weighted_strategies AS t1 
WHERE (
   SELECT SUM(weight) 
   FROM weighted_strategies AS t2 
   WHERE t2.strategy_id<=t1.strategy_id
)>@RAND AND 
weight>0
LIMIT 1

La suma total de los pesos para todos los registros debe ser n-1, y @RAND debe ser un valor aleatorio entre 0 y n-1 inclusive.

@RAND podría establecerse en SQL o insertarse como un valor entero a partir del código de llamada.

La subselección resumirá todos los pesos de los registros precedentes, verificando que exceda el valor aleatorio proporcionado.

Answer 7

Para un rendimiento mucho mejor (especialmente en las tablas grandes), primero indexe la columna de peso y use esta consulta:

SELECT * FROM tbl WHERE id IN 
    (SELECT id FROM (SELECT id FROM tbl ORDER BY -LOG(1-RAND())/weight LIMIT x) t)

Se utilizan dos subconsultas porque MySQL no admite LIMIT en la primera sub consulta todavía.

En la tabla de 40MB la consulta habitual toma 1s en mi máquina i7 y esta toma 0.04s .

Answer 8

Este chico hace la misma pregunta. Él dice lo mismo que Frank, pero las ponderaciones no salen bien y en los comentarios alguien sugiere usar ORDER BY -LOG(1.0 - RAND()) / Multiplier , que en mi prueba dio resultados casi perfectos.

(Si algún matemático quiere explicar por qué esto es correcto, ¡por favor, ilumíname! Pero funciona).

La desventaja sería que no podría establecer la ponderación en 0 para deshabilitar temporalmente una opción, ya que terminaría dividiendo por cero. Pero siempre puedes filtrarlo con un WHERE Multiplier > 0 .

Answer 9

<?php
/**
 * Demonstration of weighted random selection of MySQL database.
 */
$conn = mysql_connect(''localhost'', ''root'', '''');

// prepare table and data.
mysql_select_db(''test'', $conn);
mysql_query("drop table if exists temp_wrs", $conn);
mysql_query("create table temp_wrs (
    id int not null auto_increment,
    val varchar(16),
    weight tinyint,
    upto smallint,
    primary key (id)
)", $conn);
$base_data = array(    // value-weight pair array.
    ''A'' => 5,
    ''B'' => 3,
    ''C'' => 2,
    ''D'' => 7,
    ''E'' => 6,
    ''F'' => 3,
    ''G'' => 5,
    ''H'' => 4
);
foreach($base_data as $val => $weight) {
    mysql_query("insert into temp_wrs (val, weight) values (''".$val."'', ".$weight.")", $conn);
}

// calculate the sum of weight.
$rs = mysql_query(''select sum(weight) as s from temp_wrs'', $conn);
$row = mysql_fetch_assoc($rs);
$sum = $row[''s''];
mysql_free_result($rs);

// update range based on their weight.
// each "upto" columns will set by sub-sum of weight.
mysql_query("update temp_wrs a, (
    select id, (select sum(weight) from temp_wrs where id <= i.id) as subsum from temp_wrs i 
) b
set a.upto = b.subsum
where a.id = b.id", $conn);

$result = array();
foreach($base_data as $val => $weight) {
    $result[$val] = 0;
}
// do weighted random select ($sum * $times) times.
$times = 100;
$loop_count = $sum * $times;
for($i = 0; $i < $loop_count; $i++) {
    $rand = rand(0, $sum-1);
    // select the row which $rand pointing.
    $rs = mysql_query(''select * from temp_wrs where upto > ''.$rand.'' order by id limit 1'', $conn);
    $row = mysql_fetch_assoc($rs);
    $result[$row[''val'']] += 1;
    mysql_free_result($rs);
}

// clean up.
mysql_query("drop table if exists temp_wrs");
mysql_close($conn);
?>
<table>
    <thead>
        <th>DATA</th>
        <th>WEIGHT</th>
        <th>ACTUALLY SELECTED<br />BY <?php echo $loop_count; ?> TIMES</th>
    </thead>
    <tbody>
    <?php foreach($base_data as $val => $weight) : ?>
        <tr>
            <th><?php echo $val; ?></th>
            <td><?php echo $weight; ?></td>
            <td><?php echo $result[$val]; ?></td>
        </tr>
    <?php endforeach; ?>
    <tbody>
</table>

si quieres seleccionar N filas ...

1. volver a calcular la suma.
2. rango de restablecimiento (columna "hasta").
3. seleccione la fila que $rand apunta.

Las filas seleccionadas previamente deben excluirse en cada ciclo de selección. where ... id not in (3, 5);

Answer 10

SELECT * FROM tablename ORDER BY -LOG(RAND()) / Multiplier;

Es el que te da la distribución correcta.

SELECT * FROM tablename ORDER BY (RAND() * Multiplier);

Te da la distribución incorrecta.

Por ejemplo, hay dos entradas A y B en la tabla. A está con el peso 100 mientras que B está con el peso 200. Para el primero (variable aleatoria exponencial), le da Pr (A que gana) = 1/3 mientras que el segundo le da 1/4, que no es correcto. Desearía poder mostrarte las matemáticas. Sin embargo, no tengo suficiente representante para publicar el enlace relevante.