MySQL: выберите произвольную запись, но вес зависит от определенных записей

Question

У меня есть таблица MySQL с кучей записей в ней и столбец с именем "Multiplier". Значение по умолчанию (и наиболее распространенное) для этого столбца - 0, но это может быть любое число.

Что мне нужно сделать, это выбрать одну запись из этой таблицы наугад. Однако строки взвешиваются в соответствии с числом в столбце "Множитель". Значение 0 означает, что оно вообще не взвешено. Значение 1 означает, что оно взвешено вдвое больше, чем если бы запись была в таблице дважды. Значение 2 означает, что оно взвешено в три раза больше, как если бы запись была в таблице три раза.

Я пытаюсь изменить то, что мои разработчики уже дали мне, так что извините, если установка не имеет большого смысла. Я мог бы, вероятно, изменить его, но хотел бы сохранить как можно большую часть существующей настройки таблицы.

Я пытался выяснить, как сделать это с помощью SELECT и RAND(), но не знаю, как сделать взвешивание. Является ли это возможным?

Answer 1

Этот парень задает тот же вопрос. Он говорит то же, что и Фрэнк, но взвешивание не получается правильно, и в комментариях кто-то предлагает использовать ORDER BY -LOG(1.0 - RAND()) / Multiplier, что в моем тестировании дало довольно много прекрасных результатов.

(Если кто-то из математиков хочет объяснить, почему это правильно, пожалуйста, просветите меня! Но это работает.)

Недостатком было бы то, что вы не могли бы установить весовое значение 0, чтобы временно отключить опцию, так как в итоге вы бы делили на ноль. Но вы всегда можете отфильтровать это с WHERE Multiplier > 0,

Answer 2

Для повышения производительности (особенно для больших таблиц) сначала индексируйте столбец веса и используйте этот запрос:

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

Используются два подзапроса, потому что MySQL еще не поддерживает LIMIT в первом подзапросе.

На 40-мегабайтной таблице обычный запрос занимает 1 с на моей машине i7, а этот - 0.04 с.

Answer 3

Не используйте 0, 1 и 2, но 1, 2 и 3. Тогда вы можете использовать это значение как множитель:

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

Answer 4

Хотя я понимаю, что это вопрос к MySQL, следующее может быть полезно для тех, кто использует SQLite3, который имеет несколько разные реализации RANDOM и LOG.

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

weight - это столбец в таблице, содержащий целые числа (в качестве диапазона в моей таблице я использовал 1-100).

Функция RANDOM() в SQLite создает числа от -9.2E18 до +9.2E18 (дополнительную информацию см. В документации SQLite). Я использовал оператор по модулю, чтобы немного уменьшить диапазон чисел.

abs () удалит негативы, чтобы избежать проблем с LOG, который обрабатывает только ненулевые положительные числа.

LOG () на самом деле не присутствует в установке SQLite3 по умолчанию. Я использовал вызов php SQLite3 CreateFunction, чтобы использовать функцию php в SQL. Смотрите PHP документы для информации об этом.

Answer 5

Ну, я бы поставил логику весов в PHP:

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

и запрос:

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

Я думаю, что это будет работать:)

Answer 6

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

Это тот, который дает вам правильное распределение.

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

Дает вам неправильное распределение.

Например, в таблице есть две записи A и B. A с весом 100, а B с весом 200. Для первого (экспоненциальная случайная величина) он дает Pr(A выигрыш) = 1/3, а второй дает вам 1/4, что не правильно. Я хотел бы показать вам математику. Однако мне не хватает представителя, чтобы разместить соответствующую ссылку.

Answer 7

Для других, прибегая к помощи этой темы, я считаю, что вы также можете сделать что-то вроде этого:

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

Общая сумма весов для всех записей должна быть n-1, а @RAND должен быть случайным значением от 0 до n-1 включительно.

@RAND может быть установлен в SQL или вставлен как целочисленное значение из вызывающего кода.

Подвыбор суммирует веса всех предыдущих записей, проверяя, что он превышает предоставленное случайное значение.

Answer 8

<?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>

если вы хотите выбрать N строк...

1. пересчитать сумму.
2. диапазон сброса (столбец "вверх").
3. выберите строку, которая $rand указывающий.

ранее выбранные строки должны быть исключены в каждом цикле выбора. where ... id not in (3, 5);

Answer 9

Результат псевдокода (rand(1, num) % rand(1, num)) будет больше к 0 и меньше к ну. Вычтите результат из числа, чтобы получить противоположное.

Так что если мой язык приложения PHP, он должен выглядеть примерно так:

$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");

Объяснение кода выше:

1. Получить наибольшее значение в столбце множителя
2. вычислить случайное значение множителя (взвешенное по отношению к максимальному значению в столбце множителя)
3. Получить случайную строку, которая имеет это значение множителя

Это также достижимо просто с помощью MySQL.

Доказательство того, что псевдокод (rand(1, num) % rand(1, num)) будет иметь значение 0: выполнить следующий код PHP, чтобы понять, почему (в этом примере 16 является наибольшим числом):

$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 10

Ответ @ali отлично работает, но вы не можете контролировать, насколько ваш результат отклоняется в сторону большего или меньшего веса, вы можете изменить множитель, но это не очень динамичный подход.

я оптимизировал код, добавив POWER(weight,skewIndex) вместо weight что заставляет более высокие веса отображаться больше со значениями более 1 для skewIndex и меньше со значениями от 0 до 1.

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY -LOG(1-RAND())/POWER(weight,skewIndex) LIMIT 10) AS t2 ON t1.id = t2.id

вы можете анализировать результаты запроса с помощью

SELECT AVG(weight) FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY -LOG(1-RAND())/POWER(weight,skewIndex) LIMIT 10) AS t2 ON t1.id = t2.id

например, установка skewIndex на 3 дает мне в среднем 78%, в то время как skewIndex 1 дает в среднем 65%

Answer 11

Что бы вы ни делали, это ужасно, потому что это будет включать: * Получение общего "веса" для всех столбцов как ОДНОГО числа (включая применение множителя). * Получение случайного числа от 0 до этой суммы. * Получение всех записей и их прогон, вычитая вес из случайного числа и выбирая одну запись, когда у вас заканчиваются предметы.

В среднем вы будете бегать по половине стола. Производительность - если таблица не мала, а затем сделать это вне mySQL в памяти - будет медленным.