가중 행 확률로 PostgreSQL의 테이블에서 임의의 행을 선택

예 입력 :

SELECT * FROM test;
 id | percent   
----+----------
  1 | 50 
  2 | 35   
  3 | 15   
(3 rows)

어떻게 시간이 평균 50 %에 내가 ID = 1 행을 얻을 수 있다는 것을, 같은 쿼리를 작성합니다, ID = 2 시간 행의 35 %, 그리고 ID = 3 시간 행의 15 %?

나는 * 무작위 () DESC LIMIT 1 P로 시험 순서와 SELECT ID로 뭔가를 시도하지만 잘못된 결과를 제공합니다. 10,000 실행 후 나는 같은 분포를 얻을 : {= 6293 1, 3 = 405 = 3302 2},하지만 난 분포가 될 것으로 예상 거의 : {= 5000 1 = 3500 2, 3 = 1500}.

어떤 아이디어?

해결법

==============================
1.이 트릭을 수행해야합니다

이 트릭을 수행해야합니다
```
WITH CTE AS (
    SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R
)
SELECT *
FROM (
    SELECT id, SUM(percent) OVER (ORDER BY id) S, R
    FROM YOUR_TABLE CROSS JOIN CTE
) Q
WHERE S >= R
ORDER BY id
LIMIT 1;
```
서브 질의 Q는 다음의 결과를 제공한다 :
```
1  50
2  85
3  100
```
우리는 단순히 범위 [0, 100에서 임의의 번호를 생성) 및 또는 그 수 (절) 넘어 첫 번째 행을 선택합니다. 우리는 임의의 숫자는 한 번만 계산하기 위해 공통 테이블 표현식 (WITH)를 사용합니다.

BTW, YOUR_TABLE의 FROM SELECT SUM (퍼센트)를 사용하면 퍼센트의 모든 무게를 가지고 있습니다 - 그들은 엄격 비율 될 필요가 없습니다 (즉, 추가 최대 100).

[SQL 바이올린]
==============================
2.알고리즘에서 Efraimidis 및 Spirakis 설명.

알고리즘에서 Efraimidis 및 Spirakis 설명.
==============================
3.귀하의 제안 쿼리 작업에 나타납니다; 이 SQLFiddle 데모를 참조하십시오. 그것은 잘못된 유통 불구을 생성; 아래를 참조하십시오.

귀하의 제안 쿼리 작업에 나타납니다; 이 SQLFiddle 데모를 참조하십시오. 그것은 잘못된 유통 불구을 생성; 아래를 참조하십시오.

나는 휘발성 SQL 기능에 싸서 한 하위 쿼리를 최적화에서 PostgreSQL을 방지합니다. PostgreSQL은 당신이 휘발성을 강제하지 않는 경우 그래서 그냥 한 번 실행하는 것입니다, 당신은 외부 쿼리의 모든 행에 대해 한 번 실행하는 하위 쿼리를하려한다는 것을 알 수있는 방법이 없습니다. http://sqlfiddle.com/# : 또 다른 가능성은 - - 일하지만 쿼리 계획이 미래에서 최적화 할 수 있다는 것이이 같은이 항상 사실 where 절을 사용하여이 해킹과 같은 상관 하위 쿼리로 표시하는 것입니다 ! 12 / 3039b / 9

(당신이 작동하지 않는 이유를 설명하기 위해 업데이트 전) 추측에 당신의 테스트 방법론은 잘못했다, 또는 어디 PostgreSQL의 그것은 상관 하위 쿼리 아니다 몰래 그것을 실행하는 외부 쿼리에서 하위 쿼리로 이것을 사용하고 한 번만,이 예에서 좋아한다. .

UPDATE : 생산 유통 당신이 기대하는 것이 아니다. 여기에서 문제는 당신이 여러 샘플을 채취하여 분포를 기울어 야한다는 것입니다 () 임의; 당신은 하나의 샘플이 필요합니다.

이 쿼리는 정확한 분포 (SQLFiddle)를 생성합니다
```
WITH random_weight(rw) AS (SELECT random() * (SELECT sum(percent) FROM test))
 SELECT id
FROM (                   
  SELECT 
    id,
    sum(percent) OVER (ORDER BY id),
    coalesce(sum(prev_percent) OVER (ORDER BY id),0) FROM (
      SELECT 
        id,
        percent,
        lag(percent) OVER () AS prev_percent
      FROM test
    ) x
) weighted_ids(id, weight_upper, weight_lower)
CROSS JOIN random_weight
WHERE rw BETWEEN weight_lower AND weight_upper;
```
성능, 끔찍한, 말할 필요도 없다. 이 창을 두 개의 중첩 세트를 사용하고. 내가하고 있어요 것은 :
==============================
4.여기에 당신이 연주 할 일이 있습니다 :

여기에 당신이 연주 할 일이 있습니다 :
```
select t1.id as id1
  , case when t2.id is null then 0 else t2.id end as id2
  , t1.percent as percent1
  , case when t2.percent is null then 0 else t2.percent end as percent2 
from "Test1" t1 
  left outer join "Test1" t2 on t1.id = t2.id + 1
where random() * 100 between t1.percent and 
  case when t2.percent is null then 0 else t2.percent end;
```
기본적으로 왼쪽 외부 당신이 절 사이에 적용하는 두 개의 열이 너무 조인 수행합니다.

그것은 당신이 당신의 표는 올바른 방법으로 만 작업 지시 얻을 것이다 경우합니다.
==============================
5.브란 코 Dimitrijevic의 답변에 따라, 나는 나 (안 ROLLUP과 달리) 계층 윈도 기능을 사용하여 퍼센트의 총합을 사용하여 빠르게하지 않을 수도 있습니다 쿼리를 썼다.

브란 코 Dimitrijevic의 답변에 따라, 나는 나 (안 ROLLUP과 달리) 계층 윈도 기능을 사용하여 퍼센트의 총합을 사용하여 빠르게하지 않을 수도 있습니다 쿼리를 썼다.
```
WITH random AS (SELECT random() AS random)
SELECT id FROM (
    SELECT id, percent,
    SUM(percent) OVER (ORDER BY id) AS rank,
    SUM(percent) OVER () * random AS roll
    FROM test CROSS JOIN random
) t WHERE roll <= rank LIMIT 1
```
순서가 중요하지 않은 경우 먼저 데이터를 정렬하는 것을 피할 수 있기 때문에, 순위 AS OVER SUM (%)은 (ROWS UNBOUNDED이 PRECEDING), 바람직 할 수있다.

매우 성능면에서 유망한 것 같다, (분명히이 논문에 기술 된 바와 같이) 또한 기계공 웨이의 답변을했지만, 몇 가지 테스트 후, 분포는 꺼져있는 것처럼 :
```
SELECT id
FROM test
ORDER BY random() ^ (1.0/percent)
LIMIT 1
```
==============================
6.브랑코의 허용 솔루션은 큰 (감사합니다!)입니다. 그러나, 나는 단지 확대됨에로 (내 테스트에 따라) 인 대안을 기여하고 시각화 아마도 쉽게 싶습니다.

브랑코의 허용 솔루션은 큰 (감사합니다!)입니다. 그러나, 나는 단지 확대됨에로 (내 테스트에 따라) 인 대안을 기여하고 시각화 아마도 쉽게 싶습니다.

하자의 요점을 되풀이하다. 다음과 같이 원래의 질문에 아마도 일반화 될 수 있습니다 :

상대적 무게,하지 퍼센트에 중점을합니다. 로 브랑코는 백분율을 포함하여 무엇이든을 위해 작동 할 상대 가중치를 사용하여, 그의 대답에 지적한다.

이제, 우리는 임시 테이블에 올려 놓을 게요 테스트 데이터를 고려 :
```
CREATE TEMP TABLE test AS
SELECT * FROM (VALUES
    (1, 25),
    (2, 10),
    (3, 10),
    (4, 05)
) AS test(id, weight);
```
나는 그것이 편리하게 100까지 추가하지 않는다는 점에서, 원래의 질문에보다 더 복잡한 예제를 사용하고, 같은 무게 (20)이 한 번 이상 사용되는 것을있어주의 IDS (2, 3), 나중에 살펴 보 겠지만 이는 고려하는 것이 중요하다.

우리가해야 할 첫 번째 일은 간단한 정상화 (무게 / 합계 (무게))보다 더 아무것도없는 0에서 1로 확률에 가중치를 설정입니다 :
```
WITH p AS ( -- probability
    SELECT *,
        weight::NUMERIC / sum(weight) OVER () AS probability
    FROM test
),
cp AS ( -- cumulative probability
    SELECT *,
        sum(p.probability) OVER (
            ORDER BY probability DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumprobability
    FROM p
)
SELECT
    cp.id,
    cp.weight,
    cp.probability,
    cp.cumprobability - cp.probability AS startprobability,
    cp.cumprobability AS endprobability
FROM cp
;
```
이것은 다음과 같은 결과가 발생합니다 :
```
 id | weight | probability | startprobability | endprobability
----+--------+-------------+------------------+----------------
  1 |     25 |         0.5 |              0.0 |            0.5
  2 |     10 |         0.2 |              0.5 |            0.7
  3 |     10 |         0.2 |              0.7 |            0.9
  4 |      5 |         0.1 |              0.9 |            1.0
```
위의 쿼리는 일반적으로 인정 하듯이 우리의 요구에 꼭 필요한 것보다 더 많은 일을하고있다,하지만 난 그게 도움이 상대적 확률이 방법을 시각화 발견, 그것은 사소한 ID를 선택하는 마지막 단계를 만들 않습니다 :
```
SELECT id FROM (queryabove)
WHERE random() BETWEEN startprobability AND endprobability;
```
자,하자가 쿼리가 예상 분포 데이터를 반환 보장하는 테스트로 모두 함께 넣어. 우리는 임의의 숫자를 만 번을 생성하는 generate_series ()를 사용합니다 :
```
WITH p AS ( -- probability
    SELECT *,
        weight::NUMERIC / sum(weight) OVER () AS probability
    FROM test
),
cp AS ( -- cumulative probability
    SELECT *,
        sum(p.probability) OVER (
            ORDER BY probability DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumprobability
    FROM p
),
fp AS ( -- final probability
    SELECT
        cp.id,
        cp.weight,
        cp.probability,
        cp.cumprobability - cp.probability AS startprobability,
        cp.cumprobability AS endprobability
    FROM cp
)
SELECT *
FROM fp
CROSS JOIN (SELECT random() FROM generate_series(1, 1000000)) AS random(val)
WHERE random.val BETWEEN fp.startprobability AND fp.endprobability
;
```
이것은 다음과 유사한 출력을 발생합니다 :
```
 id | count  
----+--------
 1  | 499679 
 3  | 200652 
 2  | 199334 
 4  | 100335 
```
어느, 당신이 볼 수있는, 완벽 트랙 예상 분포를.

위의 쿼리는 매우 성능이 좋은 것입니다. 심지어 내 평균 기계, PostgreSQL을가 WSL1 인스턴스에 (! 공포)를 실행하여 실행이 상대적으로 빠르다 :
```
     count | time (ms)
-----------+----------
     1,000 |         7
    10,000 |        25
   100,000 |       210
 1,000,000 |      1950 
```
I 종종 단위 / 통합 테스트를위한 테스트 데이터를 생성 할 때, 상기 질의의 변형을 사용한다. 아이디어는 현실을 추적하는 확률 분포를 근사 임의의 데이터를 생성하는 것입니다.

그 상황에서 나는 그것이 유용 시작과 끝 분포를 계산하기 위해 찾아 번 테이블에 결과를 저장 :
```
CREATE TEMP TABLE test AS
WITH test(id, weight) AS (VALUES
    (1, 25),
    (2, 10),
    (3, 10),
    (4, 05)
),
p AS ( -- probability
    SELECT *, (weight::NUMERIC / sum(weight) OVER ()) AS probability
    FROM test
),
cp AS ( -- cumulative probability
    SELECT *,
        sum(p.probability) OVER (
            ORDER BY probability DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) cumprobability
    FROM p
)
SELECT
    cp.id,
    cp.weight,
    cp.probability,
    cp.cumprobability - cp.probability AS startprobability,
    cp.cumprobability AS endprobability
FROM cp
;
```
나는 그 여분의 성능과 간단한 사용 된 결과, 반복이 미리 계산 확률을 사용할 수 있습니다.

난 내가 임의의 ID를 얻기 위해 원하는 시간을 호출 할 수있는 함수에 모든 것을 포장 할 수 있습니다 :
```
CREATE OR REPLACE FUNCTION getrandomid(p_random FLOAT8 = random())
RETURNS INT AS
$$
    SELECT id
    FROM test
    WHERE p_random BETWEEN startprobability AND endprobability
    ;
$$
LANGUAGE SQL STABLE STRICT
```
이 기술은 위의 PRECEDING UNBOUNDED과 현재 행 사이의 비 표준 프레임 행 창 기능을 사용하고 있음을 지적 그것의 가치. 이것은 내가 처음부터 반복 무게 테스트 데이터를 선택한 이유입니다 약간 무게가 반복 될 수 있다는 사실에 대처하는 것이 필요하다!

from https://stackoverflow.com/questions/13040246/select-random-row-from-a-postgresql-table-with-weighted-row-probabilities by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 필드 이름을 대괄호 안에 대괄호를 탈출하는 방법 (0)	2020.07.12
[SQL] 다른 데이터베이스에서 테이블에 외래 키 (0)	2020.07.12
[SQL] 방법 pyspark를 사용하여 일부 필드로 그룹화 된 데이터 집합 주어진에서 최대 (날짜)를 얻으려면? (0)	2020.07.12
[SQL] SQL 스왑 기본 키 값 (0)	2020.07.12
[SQL] 삽입 날짜에 대한 기본 GETDATE (0)	2020.07.12

복붙노트

[SQL] 가중 행 확률로 PostgreSQL의 테이블에서 임의의 행을 선택

가중 행 확률로 PostgreSQL의 테이블에서 임의의 행을 선택

해결법

1.이 트릭을 수행해야합니다

2.알고리즘에서 Efraimidis 및 Spirakis 설명.

3.귀하의 제안 쿼리 작업에 나타납니다; 이 SQLFiddle 데모를 참조하십시오. 그것은 잘못된 유통 불구을 생성; 아래를 참조하십시오.

4.여기에 당신이 연주 할 일이 있습니다 :

5.브란 코 Dimitrijevic의 답변에 따라, 나는 나 (안 ROLLUP과 달리) 계층 윈도 기능을 사용하여 퍼센트의 총합을 사용하여 빠르게하지 않을 수도 있습니다 쿼리를 썼다.

6.브랑코의 허용 솔루션은 큰 (감사합니다!)입니다. 그러나, 나는 단지 확대됨에로 (내 테스트에 따라) 인 대안을 기여하고 시각화 아마도 쉽게 싶습니다.

'SQL' 카테고리의 다른 글

티스토리툴바