[sql] SQL에서 임의의 행을 요청하는 방법은 무엇입니까?

순수 SQL에서 임의의 행을 요청하거나 가능한 한 임의의 행에 가깝게 요청하려면 어떻게해야합니까?

답변

이 게시물을 참조하십시오 : 데이터베이스 테이블에서 임의의 행을 선택하는 SQL . MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 및 Oracle 에서이 작업을 수행하는 방법을 안내합니다 (다음은 해당 링크에서 복사 됨).

MySQL로 임의의 행을 선택하십시오.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

PostgreSQL로 임의의 행을 선택하십시오.

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Microsoft SQL Server에서 임의의 행을 선택하십시오.

SELECT TOP 1 column FROM table
ORDER BY NEWID()

IBM DB2로 임의의 행을 선택하십시오.

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle에서 무작위 레코드를 선택하십시오.

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

답변

Jeremies와 같은 솔루션 :

SELECT * FROM table ORDER BY RAND() LIMIT 1

작동하지만 모든 테이블을 순차적으로 스캔해야합니다 (각 행과 관련된 임의의 값을 계산해야하므로 가장 작은 값을 결정할 수 있기 때문에). 중형 테이블의 경우 상당히 느릴 수 있습니다. 내 권장 사항은 일종의 색인 된 숫자 열 (많은 테이블이 기본 키로 사용)을 사용하고 다음과 같이 작성하는 것입니다.

SELECT * FROM table WHERE num_value >= RAND() *
    ( SELECT MAX (num_value ) FROM table )
ORDER BY num_value LIMIT 1

num_value인덱스 된 경우 테이블 크기에 관계없이 로그 시간으로 작동합니다 . 한 가지주의 사항 : 이것은 num_value범위에 균등하게 분포되어 있다고 가정합니다 0..MAX(num_value). 데이터 세트가이 가정에서 크게 벗어나면 결과가 왜곡됩니다 (일부 행은 다른 행보다 더 자주 나타남).

답변

이것이 얼마나 효율적인지 모르겠지만 전에 사용했습니다.

SELECT TOP 1 * FROM MyTable ORDER BY newid()

GUID는 매우 임의적이므로 순서는 임의의 행을 얻는 것을 의미합니다.

답변

ORDER BY NEWID()

소요 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

걸립니다 0.0065 milliseconds!

나는 후자의 방법으로 분명히 갈 것입니다.

답변

어떤 서버를 사용하고 있는지 말하지 않았습니다. 이전 버전의 SQL Server에서는 다음을 사용할 수 있습니다.

select top 1 * from mytable order by newid()

SQL Server 2005 이상에서는 TABLESAMPLE반복 가능한 임의의 샘플을 얻는 데 사용할 수 있습니다 .

SELECT FirstName, LastName
FROM Contact
TABLESAMPLE (1 ROWS) ;

답변

SQL Server의 경우

newid () / order by는 작동하지만 모든 행에 대해 id를 생성 한 다음 정렬해야하기 때문에 큰 결과 집합에는 비용이 많이 듭니다.

TABLESAMPLE ()은 성능 관점에서는 좋지만 결과가 뭉치 게됩니다 (페이지의 모든 행이 반환 됨).

더 나은 성능의 실제 무작위 샘플을 얻으려면 가장 좋은 방법은 행을 무작위로 필터링하는 것입니다. SQLS 온라인 설명서의 TABLESAMPLE을 사용하여 결과 집합 제한 에서 다음 코드 샘플을 찾았습니다 .

실제로 개별 행의 임의 샘플을 원하면 TABLESAMPLE을 사용하는 대신 무작위로 행을 필터링하도록 쿼리를 수정하십시오. 예를 들어 다음 쿼리는 NEWID 함수를 사용하여 Sales.SalesOrderDetail 테이블 행의 약 1 %를 반환합니다.
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)
SalesOrderID 열은 CHECKSUM 표현식에 포함되므로 NEWID ()가 행당 한 번 평가되어 행별로 샘플링을 수행 할 수 있습니다. CAST (CHECKSUM (NEWID (), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) 식은 0과 1 사이의 임의의 부동 소수점 값으로 평가됩니다.

1,000,000 개의 행이있는 테이블에 대해 실행할 때 내 결과는 다음과 같습니다.

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

TABLESAMPLE을 사용하여 벗어날 수 있다면 최상의 성능을 제공합니다. 그렇지 않으면 newid () / filter 메소드를 사용하십시오. 결과 세트가 큰 경우 newid () / order by를 최후의 수단으로 사용해야합니다.

답변

가능하면, 저장된 명령문을 사용하여 RND ()에 대한 두 인덱스의 비 효율성을 피하고 레코드 번호 필드를 작성하십시오.

PREPARE RandomRecord FROM "SELECT * FROM table LIMIT?, 1";
SET @ n = FLOOR (RAND () * (SELECT COUNT (*) FROM 테이블));
EXECUTE RandomRecord USING @n;