[SQL] SQL 데이터베이스에서 단순 무작위 샘플

SQL 데이터베이스에서 단순 무작위 샘플

어떻게 SQL의 효율적인 단순 무작위 표본을합니까? 문제의 데이터베이스는 MySQL이 실행되고; 내 표는 적어도 20 행이며, 나는 10,000 정도의 단순 무작위 표본을 원한다.

은 "명백한"대답하는 것입니다 :

SELECT * FROM table ORDER BY RAND() LIMIT 10000

너무 느린 대형 테이블의 경우, : 베스트에 그것을 O (N LG 전자 N)를 제작해야합니다 (이미 O (N)에 넣는다) 모든 행에 대한 RAND ()를 호출하고 정렬합니다. O (n)을보다 빠르게 할 수있는 방법이 있습니까?

참고 : RAND ()가 모든 행에 대해 동일한 값을 반환 할 수 있기 때문에 당신은 SQL Server에서이 방법을 사용하는 경우로서 앤드류 마오는 코멘트에 지적, 당신은 T-SQL 기능 NEWID ()를 사용한다.

편집 : 5 년 후

나는 더 큰 테이블에 다시이 문제로 실행, 그리고 버전을 사용하여 종료 @의 무지 솔루션을 두 개조하면 되겠와 함께 :

테이블의 1000 항목의 샘플을 채취하기 위해하여 frozen_rand 열이 10,000 행을, 나는 행을 계산하고 평균에 결과 아래 샘플 :

SELECT COUNT(*) FROM table; -- Use this to determine rand_low and rand_high

  SELECT *
    FROM table
   WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s
ORDER BY RAND() LIMIT 1000

(내 실제 구현은 내가 직접 rand_high 주위를 포장하지 않는 undersample,과에 확인하기 위해 더 많은 작업을 포함하지만, 기본적인 아이디어는 "무작위로 몇 천까지 당신의 N 컷"입니다.)

이 어떤 희생을 기울이고 있으나,이 정도로 작은 ()가 다시 ORDER BY RAND에 때까지, 그것은 나를 인덱스 스캔을 사용하여 아래로 데이터베이스를 샘플링 할 수 있습니다.

해결법

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

   1.이러한 유형의 문제의 매우 흥미로운 토론이 여기에있다 : http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random- 행-에서 테이블 /

   이러한 유형의 문제의 매우 흥미로운 토론이 여기에있다 : http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random- 행-에서 테이블 /

   나는 당신의 O (N LG 전자 N) 솔루션은 최고라고 테이블에 대해 전혀 가정으로 생각합니다. 비록 실제로 좋은 최적화 또는 약간 다른 기술 쿼리하면 목록 조금 더있을 수 있습니다, O (m * n)이이 necesssarily 전체 큰 배열을 정렬 할 필요가없는 것처럼 m은 임의의 행의 수를 원하는 것입니다 , 그냥 작은 m 시간을 검색 할 수 있습니다. 그러나 당신이 게시 번호의 종류를 위해, m은 n은 어쨌든 LG 전자보다 크다.

   세 가지 가정을 우리는 밖으로 시도 할 수 있습니다 :

   단지 가정 1과 2를 당신이 가정 3 일치하도록 테이블에 전체 인덱스를 작성해야하지만이 necesarily 빠른 O (n)이없는, 그래서이, O (N)으로 수행 할 수 있다고 생각합니다. 우리는 또한 테이블에 대해 뭔가 다른 좋은 가정 할 수 있다면, 우리는 O의 작업 (m 로그 m)를 할 수 있습니다. 가정 3 작업하기 쉬운 좋은 속성이 추가 될 것이다. 행 번호 m을 생성하는 경우에는 중복 보장되는 좋은 난수 발생기로하는 O (m) 용액이 가능할 것이다.

   세 가지 가정을 감안할 때, 기본적인 아이디어는 1과 N 사이 m 고유 한 난수를 생성 한 후 테이블에서 해당 키를 사용하여 행을 선택하는 것입니다. 나는 약간처럼 보일 것이있는 의사 정도면, 지금 내 앞에 MySQL의 또는 아무것도하지 않습니다

   
   create table RandomKeys (RandomKey int)
   create table RandomKeysAttempt (RandomKey int)
   
   -- generate m random keys between 1 and n
   for i = 1 to m
     insert RandomKeysAttempt select rand()*n + 1
   
   -- eliminate duplicates
   insert RandomKeys select distinct RandomKey from RandomKeysAttempt
   
   -- as long as we don't have enough, keep generating new keys,
   -- with luck (and m much less than n), this won't be necessary
   while count(RandomKeys) < m
     NextAttempt = rand()*n + 1
     if not exists (select * from RandomKeys where RandomKey = NextAttempt)
       insert RandomKeys select NextAttempt
   
   -- get our random rows
   select *
   from RandomKeys r
   join table t ON r.RandomKey = t.UniqueKey
   

   당신은 정말 효율성에 대해 우려한다면, 당신은 아마의 종류에 더 좋을 것이다 SQL 이외의 거의 아무것도, 절차 적 언어의 어떤 종류의 랜덤 키 생성을 수행하고 데이터베이스에 결과를 삽입하는 것이 좋습니다 루핑 및 난수 생성이 필요합니다 .

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

   2.나는 가장 빠른 해결책이 생각

   나는 가장 빠른 해결책이 생각

   select * from table where rand() <= .3
   

   나는이 일을해야한다고 생각하는 이유 여기에있다.

   이것은 그 랜드 가정 ()은 균일 한 분포의 숫자를 생성한다. 그것은이 작업을 수행 할 수있는 가장 빠른 방법입니다.

   여기 내가 그에게 말할 것입니다 .. 그 사람이 그 솔루션을 추천했다 그들은 증거없이 아래로 총에 맞았 보았다 -

   문제의 데이터베이스는 MySQL이 있기 때문에, 이것이 바로 솔루션입니다.

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

   3.그냥 사용

   그냥 사용

   WHERE RAND() < 0.1 
   

   기록의 10 %를 얻기 위해 또는

   WHERE RAND() < 0.01 
   

   등을 기록, 1 %를 얻을 수 있습니다

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

   4.분명히 SQL의 일부 버전에서 TABLESAMPLE 명령있다,하지만 모든 SQL 구현 (특히, Redshift에)에 아니에요.

   분명히 SQL의 일부 버전에서 TABLESAMPLE 명령있다,하지만 모든 SQL 구현 (특히, Redshift에)에 아니에요.

   http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx

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

   5.나는 따라서는 O (n)이 시간에 실행, 그래서 감동 빠른 수행,이 방법은 ORDER BY RAND ()보다 훨씬 더 빨리 테스트.

   나는 따라서는 O (n)이 시간에 실행, 그래서 감동 빠른 수행,이 방법은 ORDER BY RAND ()보다 훨씬 더 빨리 테스트.

   http://technet.microsoft.com/en-us/library/ms189108%28v=sql.105%29.aspx에서 :

   비 MSSQL 버전 -이 테스트를하지 않았다

   SELECT * FROM Sales.SalesOrderDetail
   WHERE 0.01 >= RAND()
   

   MSSQL 버전 :

   SELECT * FROM Sales.SalesOrderDetail
   WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
   

   이 기록의 ~ 1 %를 선택합니다. 당신이 선택할 수 퍼센트 나 기록의 정확한 # 필요 그렇다면, 몇 가지 안전 여유와 비율을 추정 한 후 무작위로 세트를 결과 RAND () 메소드에 의해 더 비싼 ORDER를 사용하는 여분의 기록을 뽑아.

   내가 잘 알려진 인덱스 컬럼 값의 범위를했기 때문에 나는 더욱이 방법을 개선 할 수 있었다.

   만약 [0..max 균일하게 분포 정수로 인덱싱 된 열이 경우, 예를 들어, 임의로 N 작은 간격들을 선택하는 것을 사용할 수있다. 각 쿼리 실행에 대한 다른 세트를 얻을 수 있도록 프로그램이 동적으로 작업을 수행합니다. 이 부분 집합의 선택은 O (N)이 될 것이다 전체 데이터 세트보다 작은 크기의 수 많은 주문.

   내 테스트에서 나는 0.0 초까지 20 ORDER BY RAND ()를 사용하여 3 분의 샘플 기록 (MIL 20에서)를 얻기 위해 필요한 시간을 감소!

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

   6.나는이 모든 솔루션 교체없이 샘플에 표시 것을 지적하고 싶다. 임의의 종류에서 최고 K 행을 선택 또는 교체없이 생성 된 임의의 샘플을 얻을 것입니다 무작위로 고유 키를 포함하는 테이블에 합류.

   나는이 모든 솔루션 교체없이 샘플에 표시 것을 지적하고 싶다. 임의의 종류에서 최고 K 행을 선택 또는 교체없이 생성 된 임의의 샘플을 얻을 것입니다 무작위로 고유 키를 포함하는 테이블에 합류.

   당신이 당신의 샘플이 독립하려는 경우, 당신은 대체 샘플해야합니다. A는 user12861의 솔루션과 유사한 방식으로 가입하여이 작업을 수행하는 방법의 한 예를 들어 질문 25451034를 참조하십시오. 이 솔루션은 T-SQL 용으로 작성되어 있지만 개념은 모든 SQL DB에서 작동합니다.

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

   7.우리는 테이블의 ID를 검색 할 수있는 관찰을 시작으로 집합을 기반으로 (예 : 5를 계산합니다.)

   우리는 테이블의 ID를 검색 할 수있는 관찰을 시작으로 집합을 기반으로 (예 : 5를 계산합니다.)

   select *
   from table_name
   where _id in (4, 1, 2, 5, 3)
   

   우리는 우리가 "(4, 1, 2, 5, 3)"문자열을 생성 할 수 있다면, 우리는 RAND보다 더 효율적인 방법을 할 것이다, 그 결과로 올 수 있습니다 ().

   예를 들어, 자바 :

   ArrayList<Integer> indices = new ArrayList<Integer>(rowsCount);
   for (int i = 0; i < rowsCount; i++) {
       indices.add(i);
   }
   Collections.shuffle(indices);
   String inClause = indices.toString().replace('[', '(').replace(']', ')');
   

   IDS는 간격이 있다면, 초기의 ArrayList 지수는 IDS에 SQL 쿼리의 결과입니다.

8. ==============================

   8.정확히 행을 해요 필요가있는 경우, 현실적으로는 SQL의 ID를 외부의 당신의 부분 집합을 생성 할 수 있습니다. 대부분의 방법은 "n 번째"항목을 선택하려면 어떤 점에서 필요로하며, SQL 테이블은 정말 전혀 배열되지 않습니다. 키가 불과하기 위해 연속 있다는 가정은 1 사이의 임의의 int에 가입하고 계산도 만족하기 어려운 - MySQL은, 예를 들어 기본적으로 지원하지 않으며, 잠금 조건은 ... 까다로운.

   정확히 행을 해요 필요가있는 경우, 현실적으로는 SQL의 ID를 외부의 당신의 부분 집합을 생성 할 수 있습니다. 대부분의 방법은 "n 번째"항목을 선택하려면 어떤 점에서 필요로하며, SQL 테이블은 정말 전혀 배열되지 않습니다. 키가 불과하기 위해 연속 있다는 가정은 1 사이의 임의의 int에 가입하고 계산도 만족하기 어려운 - MySQL은, 예를 들어 기본적으로 지원하지 않으며, 잠금 조건은 ... 까다로운.

   다음은 O입니다 (최대 (N, m LG 전자 N)) - 시간, 그냥 일반 BTREE 키를 가정 O (N) - 공간 솔루션 :

   SQL의 임의의 부분 집합의 외부를 발생시키는 방법은 적어도 이러한 복잡성이 있어야합니다. 더 빨리 O 이상 (m LG N)와 BTREE (그래서 O (m) 주장은 대부분의 엔진에 대한 환상이다)이 될 수 없습니다 가입하고 셔플은 아래 묶여있다 n과 m LG 전자 N과 점근 동작에 영향을주지 않습니다.

   파이썬 의사 코드에서 :

   ids = sql.query('SELECT id FROM t')
   for i in range(m):
     r = int(random() * (len(ids) - i))
     ids[i], ids[i + r] = ids[i + r], ids[i]
   
   results = sql.query('SELECT * FROM t WHERE id IN (%s)' % ', '.join(ids[0:m-1])
   

9. ==============================

   9.네티 3000 개 무작위 기록을 선택합니다 :

   네티 3000 개 무작위 기록을 선택합니다 :

   WITH IDS AS (
        SELECT ID
        FROM MYTABLE;
   )
   
   SELECT ID FROM IDS ORDER BY mt_random() LIMIT 3000
   

10. ==============================

    10.어쩌면 당신은 할 수

    어쩌면 당신은 할 수

    SELECT * FROM table LIMIT 10000 OFFSET FLOOR(RAND() * 190000)
    

from https://stackoverflow.com/questions/249301/simple-random-samples-from-a-sql-database by cc-by-sa and MIT license