programing

MySQL: 임의 항목 선택, 그러나 특정 항목에 대한 가중치

subpage 2023. 7. 28. 22:05
반응형

MySQL: 임의 항목 선택, 그러나 특정 항목에 대한 가중치

MySQL 테이블에 항목이 여러 개 있고 "멀티플라이어"라는 열이 있습니다.이 열의 기본값(가장 일반적인 값)은 0이지만 임의의 숫자일 수 있습니다.

제가 해야 할 일은 테이블에서 항목 하나를 무작위로 선택하는 것입니다.그러나 행은 "승수" 열의 숫자에 따라 가중치가 부여됩니다.값이 0이면 가중치가 전혀 없습니다.값이 1이면 항목이 테이블에 두 번 있는 것처럼 가중치가 두 배임을 의미합니다.값이 2이면 항목이 테이블에 세 번 있는 것처럼 가중치가 세 배라는 것을 의미합니다.

개발자들이 이미 제공한 내용을 수정하려고 합니다. 설정이 의미가 없다면 죄송합니다.아마도 변경할 수는 있지만 가능한 한 기존 테이블 설정을 유지하고 싶습니다.

SELECT와 RAND()를 사용하여 이 작업을 수행하는 방법을 알아보려고 했지만 가중치를 부여하는 방법을 모르겠습니다.가능합니까?

이 남자는 같은 질문을 합니다.그는 프랭크와 같은 말을 하지만 가중치가 제대로 나오지 않고 누군가가 사용을 제안합니다.ORDER BY -LOG(1.0 - RAND()) / Multiplier제 테스트에서 거의 완벽한 결과를 얻었습니다.

(만약 밖에 있는 수학자들 중에 이것이 왜 옳은지 설명하고 싶은 사람이 있다면, 저를 깨우쳐주세요!하지만 효과가 있습니다.)

단점은 옵션을 일시적으로 비활성화하기 위해 가중치를 0으로 설정할 수 없다는 것입니다. 결국 0으로 나누기 때문입니다.하지만 당신은 항상 그것을 걸러낼 수 있습니다.WHERE Multiplier > 0.

성능을 훨씬 향상시키려면(특히 큰 테이블에서) 먼저 가중치 열을 인덱싱하고 다음 쿼리를 사용합니다.

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

40MB 테이블에서 일반적인 쿼리는 i7 기계에서 1초가 걸리고 이 쿼리는 0.04초가 걸립니다.

이것이 더 빠른 이유에 대한 설명은 MySQL에서 600K 행에서 10개의 임의 행을 빠르게 선택하십시오.

0, 1, 2가 아니라 1, 2, 3을 사용하세요.그런 다음 이 값을 승수로 사용할 수 있습니다.

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

음, 저는 가중치의 논리를 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

효과가 있을 것 같습니다 :)

이것이 MySQL에 대한 질문이라는 것을 알고 있지만, RANDOM과 LOG의 구현이 미묘하게 다른 SQLite3를 사용하는 사람에게 다음이 유용할 수 있습니다.

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

가중치는 정수를 포함하는 테이블의 열입니다(테이블의 범위로 1-100을 사용했습니다).

SQLite의 RANDOM()은 -9 사이의 숫자를 생성합니다.2E18과 +9.2E18(자세한 내용은 SQLite 문서 참조).저는 모듈로 연산자를 사용하여 숫자의 범위를 조금 낮췄습니다.

abs()는 0이 아닌 양수만 처리하는 LOG의 문제를 방지하기 위해 음수를 제거합니다.

LOG()는 SQLite3의 기본 설치에 실제로 존재하지 않습니다.SQL에서 php 함수를 사용하기 위해 php SQLite3 CreateFunction 호출을 사용했습니다.이에 대한 자세한 내용은 PHP 문서를 참조하십시오.

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로 나타나는데, 이는 올바르지 않습니다.제가 수학을 보여드릴 수 있으면 좋겠습니다.하지만 저는 관련 링크를 게시하기에 충분한 담당자가 없습니다.

다른 사람들이 이 주제를 검색하면 다음과 같은 작업을 수행할 수 있다고 생각합니다.

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로 설정하거나 호출 코드의 정수 값으로 삽입할 수 있습니다.

하위 선택 항목은 모든 선행 레코드의 가중치를 합계하여 제공된 임의 값을 초과하는지 확인합니다.

<?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. 재설정 범위("seto" 열).
  3. 을선합니다택행▁▁row다행을 합니다.$randpointing)

이전에 선택한 행은 각 선택 루프에서 제외되어야 합니다.where ... id not in (3, 5);

당신이 무엇을 하든, 그것은 다음과 관련될 것이기 때문에 끔찍할 것입니다: * 모든 열의 총 "가중치"를 하나의 숫자로 얻는 것(승수 적용 포함).0과 해당 합계 사이의 난수를 얻습니다.모든 항목을 가져와서 실행하고, 난수에서 가중치를 차감하고, 항목이 부족할 때 하나의 항목을 선택합니다.

평균적으로 당신은 테이블의 절반을 따라 달릴 것입니다.테이블이 작지 않은 경우 메모리의 mySQL 외부에서 수행하면 성능이 느려집니다.

의사 코드의 결과입니다.(rand(1, num) % rand(1, num))0을 향해 더 많이 가고 n을 향해 더 적게 갈 것입니다.숫자에서 결과를 빼면 반대가 됩니다.

따라서 내 응용 프로그램 언어가 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을 사용하는 것만으로도 달성할 수 있습니다.

의사 코드가 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>';

@ali의 대답은 훌륭하지만 결과가 얼마나 더 높거나 낮은 가중치로 기울어지는지 제어할 수 없습니다. 승수를 변경할 수 있지만 매우 역동적인 접근 방식은 아닙니다.

나는 추가함으로써 코드를 최적화했습니다.POWER(weight,skewIndex)대신에weight이는 스큐에 대해 1보다 큰 값을 가진 더 높은 가중치가 나타나도록 합니다.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

예: 스큐 설정인덱스를 3으로 지정하면 평균 78%의 왜곡률을 얻을 수 있습니다.지수 1은 평균 65%를 나타냅니다.

언급URL : https://stackoverflow.com/questions/2417621/mysql-select-random-entry-but-weight-towards-certain-entries

반응형

'programing' 카테고리의 다른 글

CrudRepository를 확장하는 Spring Boot  (0) 2023.07.28
각진 소재에서 스타일링 매트 선택  (0) 2023.07.28
도커-업 vs 도커-업 --빌드 vs 도커-업 빌드 --캐시 없음  (0) 2023.07.28
Genymotion 가상 장치에 Google 프레임워크(Play, Accounts 등)를 설치하려면 어떻게 해야 합니까?  (0) 2023.07.28
도커는 모든 포트 또는 7000~8000 범위의 포트를 노출합니다.  (0) 2023.07.28

'programing'의 다른글

  • 현재글MySQL: 임의 항목 선택, 그러나 특정 항목에 대한 가중치

관련글

댓글

티스토리툴바