У меня есть таблица MySQL с множеством записей в ней и столбец с названием «Множитель». Значение по умолчанию (и наиболее распространенное) для этого столбца равно 0, но это может быть любое число.
Что мне нужно сделать, это выбрать одну запись из этой таблицы наугад. Однако строки взвешиваются в соответствии с номером в столбце «Множитель». Значение 0 означает, что он не взвешен вообще. Значение 1 означает, что оно взвешено в два раза больше, как если бы запись была в таблице дважды. Значение 2 означает, что он взвешен в три раза больше, как если бы запись была в таблице три раза.
Я пытаюсь изменить то, что мои разработчики уже дали мне, поэтому извините, если настройка не имеет большого смысла. Возможно, я мог бы изменить его, но хочу сохранить как можно больше существующей настройки таблицы.
Я пытался выяснить, как это сделать с помощью SELECT и RAND (), но не знаю, как сделать взвешивание. Является ли это возможным?
Этот парень задает тот же вопрос. Он говорит то же самое, что и Фрэнк, но весы не выходят правильно, и в комментариях кто-то предлагает использовать ORDER BY -LOG(RAND()) / Multiplier , который в моем тестировании дал очень ORDER BY -LOG(RAND()) / Multiplier результаты.
(Если какие-либо математики хотят объяснить, почему это правильно, пожалуйста, просветите меня! Но это работает.)
Недостаток заключается в том, что вы не можете установить весовое значение 0 для временного отключения опции, так как вы закончите деление на ноль. Но вы всегда можете отфильтровать его с помощью WHERE Multiplier > 0 .
Не используйте 0, 1 и 2, а 1, 2 и 3. Затем вы можете использовать это значение как множитель:
SELECT * FROM tablename ORDER BY (RAND() * Multiplier);
Для повышения производительности (особенно на больших таблицах) сначала проиндексируйте столбец веса и используйте этот запрос:
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 .
Ну, я бы поставил логику весов в 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
Я думаю, это сработает 🙂
<?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 строк …
$rand . ранее выбранные строки должны быть исключены в каждом цикле выделения. where ... id not in (3, 5);
Независимо от того, что вы делаете, это giong ужасно, потому что это будет включать: * Получение общих «весов» для всех столбцов как ОДНОГО числа (включая применение множителя). * Получение случайного числа от 0 до этой суммы. * Получение всех записей и их запуск, вычитание веса из случайного числа и выбор одной записи, когда у вас заканчиваются предметы.
В среднем вы будете бегать по половине стола. Производительность – если таблица не мала, то сделайте это за пределами mySQL в памяти – будет SLOW.
Результат псевдокода (rand(1, num) % rand(1, num)) будет больше приближаться к 0 и меньше к num. Вычтите результат из num, чтобы получить противоположное.
Поэтому, если мой язык приложения – 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");
Объяснение приведенного выше кода:
Этого также можно добиться, просто используя 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>';
Для других людей, изучающих эту тему, я считаю, что вы также можете сделать что-то вроде этого:
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 или вставлен как целое значение из вызывающего кода.
Подзапрос суммирует все весовые показатели предшествующих записей, проверяя, что он превышает предоставленное случайное значение.
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, что неверно. Хотел бы я показать вам математику. Однако мне не хватает репутации, чтобы опубликовать соответствующую ссылку.
Хотя я понимаю, что это вопрос о 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 ). Я использовал оператор modulo, чтобы немного уменьшить диапазон чисел.
abs () удалит негативы, чтобы избежать проблем с LOG, который обрабатывает ненулевые положительные числа.
LOG () фактически не присутствует в стандартной установке SQLite3 по умолчанию. Я использовал вызов php SQLite3 CreateFunction для использования функции php в SQL. См. Документы PHP для получения информации об этом.