[SQL] 어떻게 체중을 고려하여 무작위로 하나 개의 행을 선택하려면?

어떻게 체중을 고려하여 무작위로 하나 개의 행을 선택하려면?

나는 그렇게 보이는 테이블이 있습니다 :

id: primary key
content: varchar
weight: int

내가 뭘 원하는 무작위이 테이블에서 하나 개의 행을 선택하지만, 계정에 무게를 복용. 예를 들어, 나는 3 개 행이있는 경우 :

id, content, weight
1, "some content", 60
2, "other content", 40
3, "something", 100

첫 번째 행 선택되는 30 %의 확률로, 두 번째 행이 선택 될 확률은 20 %를 가지며, 세 번째 행이 선택 될 확률이 50 %.

그렇게 할 수있는 방법이 있습니까? 내가 2 개 또는 3 쿼리를 실행해야하는 경우는 문제가되지 않습니다.

해결법

1. ==============================

   1.나는 그것이 작동하지만, 그것은 빨리하지, 밴의 솔루션을 시도하고있다.

   나는 그것이 작동하지만, 그것은 빨리하지, 밴의 솔루션을 시도하고있다.

   나는이 문제를 해결하고 있다는 방법은 가중치에 대한 별도의 연결된 테이블을 유지하는 것입니다. 기본 테이블 구조는 다음과 유사합니다 :

   CREATE TABLE `table1` (
     `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     `name` varchar(100),
     `weight` tinyint(4) NOT NULL DEFAULT '1',
   );
   
   CREATE TABLE `table1_weight` (
     `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     `table1_id` int(11) NOT NULL
   );
   

   내가 3의 무게 표에 기록을 가지고 있다면, 나는 table1_id 필드를 통해 표에 연결 table1_weight 3 개 레코드를 만들 수 있습니다. 무엇이든 무게의 값은 내가 table1_weight에서 만든 얼마나 많은 링크 기록의 그, 표입니다.

   2031의 총 중량과 표 1에서 976 개 기록과 table1_weight에 따라서 2,031 레코드가 데이터 집합에, 나는 다음과 같은 두 가지 SQL을 달렸다 :

   SELECT t.*
   FROM table1 t
   INNER JOIN table1_weight w
       ON w.table1_id = t.id
   ORDER BY RAND()
   LIMIT 1
   

   SQL 1은 지속적으로 0.4 초 정도 걸립니다.

   SQL이 0.01 내지 0.02 초가 걸린다.

   임의의, 가중 기록의 선택의 속도가 문제가되지 않는 경우, 반에서 제안하는 단일 테이블의 SQL 괜찮 별도의 테이블을 유지 관리의 오버 헤드를 가지고 있지 않습니다.

   내 경우처럼 짧은 선택 시간이 중요한 경우에, 나는 두 개의 테이블 방법을 추천 할 것입니다.

2. ==============================

   2.이 MSSQL의 작품과 내가 그것을 잘로 MySQL에서 작동하도록 키워드의 커플을 변경할 수 있어야 확신 (어쩌면 더 좋은) :

   이 MSSQL의 작품과 내가 그것을 잘로 MySQL에서 작동하도록 키워드의 커플을 변경할 수 있어야 확신 (어쩌면 더 좋은) :

   SELECT      TOP 1 t.*
   FROM        @Table t
   INNER JOIN (SELECT      t.id, sum(tt.weight) AS cum_weight
               FROM        @Table t
               INNER JOIN  @Table tt ON  tt.id <= t.id
               GROUP BY    t.id) tc
           ON  tc.id = t.id,
              (SELECT  SUM(weight) AS total_weight FROM @Table) tt,
              (SELECT  RAND() AS rnd) r
   WHERE       r.rnd * tt.total_weight <= tc.cum_weight
   ORDER BY    t.id ASC
   

   아이디어는, 각 행 (부속-1)의 누적 중량을 가지고 다음이 누적 범위의 스팬 RAND ()의 위치를 ​​찾는 것이다.

3. ==============================

   3.나는 간단한이 가중 저수지 샘플링을 사용하는 것이 실제로 생각 :

   나는 간단한이 가중 저수지 샘플링을 사용하는 것이 실제로 생각 :

   SELECT
     id,
     -LOG(RAND()) / weight AS priority
   FROM
     your_table
   ORDER BY priority
   LIMIT 1;
   

   그것은 당신이 확률이 그 무게에 비례하는 각 요소에 대해 선택되는 N 요소에서 M을 선택할 수있는 좋은 방법입니다. 당신은 단지 하나 개의 요소 싶은 일 때와 마찬가지로 잘 작동합니다. 이 방법은 본 문서에 설명되어있다. 그들은 전쟁 포로의 가장 큰 값을 선택하는 것이 주 (RAND (), 1 / 무게) -log (RAND ()) / 무게의 가장 작은 값을 선택하는 것과 같습니다.

4. ==============================

   4.간단한 방법은 (피 조인하거나 서브 질의)은 단지 곱셈 0과 1 사이의 난수에 의해 중량을 기준으로 정렬 임시 중량을 생성한다 :

   간단한 방법은 (피 조인하거나 서브 질의)은 단지 곱셈 0과 1 사이의 난수에 의해 중량을 기준으로 정렬 임시 중량을 생성한다 :

   SELECT t.*, RAND() * t.weight AS w 
   FROM table t 
   ORDER BY w DESC
   LIMIT 1
   

   이 문제를 이해하기 위해, RAND는 () * 2 배 RAND보다 더 큰 값이 될 것이라는 점을 고려 () * X 대략 시간의 3 분의 2. 따라서, 시간이 지남에 따라 각각의 행은 그 상대 중량에 비례의 주파수로 선택한다 (예. 100 중량 가진 행 등 체중 1 행보다 더 자주 100 회에 대해 선택 될 것이다).

   업데이트 :이 방법은 지금 사용하지 않는 위해 이렇게 정확한 분포를 생산 실제로하지 않습니다! (아래 설명 참조). 나는 여전히 그 의지 작업 위의 유사한 간단한 방법 일 수 있지만, 관련된 조인 더 좋을 수도 더 복잡한 방법을 아래에 지금한다고 생각합니다. 나는이 대답을 떠날거야 때문에 (A) 관련 아래 코멘트에 대한 논의, 그리고 내가 기회를 얻을 때, 나는 그것을 해결하기 위해 노력하겠습니다 경우 / (B)가있다.

5. ==============================

   5.이 사람은 작동하는 것 같다,하지만 난 뒤에 수학 모르겠어요.

   이 사람은 작동하는 것 같다,하지만 난 뒤에 수학 모르겠어요.

   SELECT RAND() / t.weight AS w, t.* 
   FROM table t 
   WHERE t.weight > 0
   ORDER BY 1
   LIMIT 1
   

   그것이 작동하는 이유에서 내 생각 엔 가장 작은 결과에 대한 높은 무게의 무게에 의해 임의의 결과를 나누어 오름차순의 모습이보다 긴밀하게 제로에 가까운 클러스터 된 것입니다.

   나는 3000 행에 걸쳐 209,000 쿼리 (PostgreSQL을에 실제로 동일한 알고리즘)을 테스트 무게 표현은 올바른 나왔다.

   내 입력 데이터 :

   select count(*),weight from t group by weight
    count | weight 
   -------+--------
     1000 |     99
     1000 |     10
     1000 |    100
   (3 rows)
   

   내 결과 :

   jasen=# with g as ( select generate_series(1,209000) as i )
   ,r as (select (  select t.weight as w 
       FROM  t 
       WHERE t.weight > 0
       ORDER BY ( random() / t.weight ) + (g.i*0)  LIMIT 1 ) from g)
   
   select r.w, count(*), r.w*1000 as expect from r group by r.w;
   
     w  | count | expect 
   -----+-------+--------
     99 | 98978 |  99000
     10 | 10070 |  10000
    100 | 99952 | 100000
   (3 rows)
   

   + (g.i * 0)가 연산 결과에 영향을주지 않지만, 외부 참조로 계획 강제하는 데 필요한 g에에서 제조 209K 입력 행의 각 서브 - 선택 재 평가할

6. ==============================

   6.어쩌면 이것은 하나

   어쩌면 이것은 하나

   SELECT * FROM <Table> T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM <Table> ) AS x ON T.ID >= x.ID LIMIT 1;
   

   또는이 하나

   SELECT * FROM tablename
             WHERE somefield='something'
             ORDER BY RAND() LIMIT 1
   

7. ==============================

   7.내가 어떻게 여기 RND () MySQL은,하지만, MSSQL에 대한 작업 예제 기억하지 않는다 :

   내가 어떻게 여기 RND () MySQL은,하지만, MSSQL에 대한 작업 예제 기억하지 않는다 :

   SELECT TOP(1) (weight +RAND ()) r, id, content, weight FROM Table
   ORDER BY 1 DESC
   

   TOP (1) 적용 할 수없는 경우는 전체 결과 집합에서 첫 번째 레코드를 가져옵니다.

from https://stackoverflow.com/questions/1398113/how-to-select-one-row-randomly-taking-into-account-a-weight by cc-by-sa and MIT license