SQL에서 랜덤 행을 요청하는 방법

SQL에서 랜덤 행을 요청하는 방법

순수 SQL에서 랜덤 행(또는 가능한 한 랜덤에 가까운)을 요구하려면 어떻게 해야 합니까?

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

MySQL을 사용하여 임의의 행을 선택합니다.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Postgre를 사용하여 임의의 행을 선택합니다.SQL:

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()

Server 2005를 사용할 수 .TABLESAMPLE반복 가능한 랜덤 샘플을 얻으려면:

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

SQL Server의 경우

newid()/order by는 동작합니다만, 큰 결과 세트에서는, 행 마다 ID 를 생성해, 정렬 할 필요가 있기 때문에, 매우 비쌉니다.

TABLESAMPLE()은 퍼포먼스의 관점에서는 좋지만, 결과의 크램핑이 발생합니다(페이지상의 모든 행이 반환됩니다).

실제 랜덤 표본을 더 잘 사용하려면 행을 랜덤하게 필터링하는 것이 가장 좋습니다.SQL Server Books Online 기사 "Limiting Results Sets by Using TABLESAMPLE"에서 다음 코드 샘플을 찾았습니다.

개별 행의 랜덤샘플을 사용하려면 TABLESAMPLE을 사용하는 대신 행을 랜덤으로 필터링하도록 쿼리를 수정합니다.예를 들어, 다음 쿼리는 NEWID 함수를 사용하여 판매 행의 약 1%를 반환합니다.Sales Order Detail 테이블:

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

Sales Order (세일즈 오더)NEWID()가 행마다 1회 평가하여 행마다 샘플링이 이루어지도록 CHECSUM 식에 ID 열이 포함됩니다.CAST(CHECKSUM(NEWID)), Sales Order 표현ID) & 0x7ffffff AS float / CAST(0x7ffffff 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

TABLE SAMPLE을 사용하면 최고의 퍼포먼스를 얻을 수 있습니다.그렇지 않으면 newid()/filter 메서드를 사용합니다.newid()/order by는 큰 결과 세트가 있는 경우 마지막 수단이 됩니다.

가능하면 저장된 문을 사용하여 RND()에 있는 두 인덱스의 비효율성과 레코드 번호 필드를 생성하지 않도록 하십시오.

"SELECT * FROM table limit ?,1"에서 RandomRecord를 준비한다.SET @n=FLOOR(RAND()*(테이블에서 SELECT COUNT(*))));@n을 사용하여 랜덤 레코드를 실행합니다.

가장 좋은 방법은 이 목적을 위해 랜덤 값을 새 열에 넣고 다음과 같은 것(pseude 코드 + SQL)을 사용하는 것입니다.

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

이것은 MediaWiki 코드에 채용된 솔루션입니다.물론 작은 값에 대한 편견이 있지만 행을 가져오지 않을 때 랜덤 값을 0으로 감는 것으로 충분하다는 것을 알게 되었습니다.

newid() 솔루션은 각 행에 새로운 GUID를 할당할 수 있도록 전체 테이블스캔이 필요할 수 있습니다.이것에 의해, 퍼포먼스가 큰폭으로 저하됩니다.

rand() 솔루션은 함수를 한 번만 평가하여 모든 행에 동일한 "랜덤" 번호가 할당되기 때문에 (MSQL에서는) 전혀 작동하지 않을 수 있습니다.

SQL Server 2005 및 2008의 경우 (Books Online에서) 각 행의 랜덤 샘플을 원하는 경우:

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

늦었지만 구글을 통해 도착했으므로 후세를 위해 다른 해결책을 추가하겠습니다.

또 다른 접근법은 TOP를 두 번 사용하여 번갈아 주문하는 것입니다.TOP의 변수를 사용하기 때문에 "순수한 SQL"인지는 모르겠지만 SQL Server 2008에서 작동합니다.임의의 단어를 원하는 경우 사전 단어 표에 대해 사용하는 예를 보여 줍니다.

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

물론 @idx는 타깃테이블에서 1 ~COUNT(*) 범위의 랜덤으로 생성되는 정수입니다.컬럼이 색인화 되어 있는 경우, 그 이점도 얻을 수 있습니다.또 다른 장점은 NEWID()는 허용되지 않기 때문에 함수에서 사용할 수 있다는 것입니다.

마지막으로 위의 쿼리는 같은 테이블 상의 NEWID() 유형의 쿼리 실행 시간의 약 10분의 1로 실행됩니다.YYMV

RAND()를 사용하는 것이 권장되지 않으므로 단순히 최대 ID(=Max)를 얻을 수 있습니다.

SELECT MAX(ID) FROM TABLE;

1에서 무작위로...최대(=My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

다음 SQL을 실행합니다.

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

ID가 선택된 값보다 같거나 큰 행이 없는지 확인합니다.테이블 아래 행을 헌트하여 My_Generated_Random과 같거나 낮은 ID를 얻은 후 다음과 같이 쿼리를 변경할 수도 있습니다.

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

@cnu의 답변에 대한 @BillKarwin의 코멘트에서도 알 수 있듯이...

LIMIT와 조합하면 (적어도 Postgre와 조합하면) 퍼포먼스가 훨씬 좋아집니다. 9행의 직접 랜덤 에 접속합니다를 들어 SQL 9.1)은 실제 행의 순서를 직접 하지 않습니다.

SELECT * FROM tbl_post AS t
JOIN ...
JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand
       FROM tbl_post
       WHERE create_time >= 1349928000
     ) r ON r.id = t.id
WHERE create_time >= 1349928000 AND ...
ORDER BY r.rand
LIMIT 100

복잡한 쿼리에서 결합될 수 있는 모든 키 값에 대해 'rand' 값이 생성되는지 확인하고 가능한 경우 'r' 행 수를 제한하십시오.

정수로서의 CAST는 특히 Postgre에 도움이 됩니다.SQL 9.2는 정수 및 단정도 부동 유형에 대한 특정 정렬 최적화 기능을 갖추고 있습니다.

MySQL이 임의 레코드를 가져오려면

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

상세한 것에 대하여는, http://jan.kneschke.de/projects/mysql/order-by-rand/ 를 참조해 주세요.

SQL Server 2012+에서는 OFFSET FETCH 쿼리를 사용하여 단일 랜덤 행에 대해 이 작업을 수행할 수 있습니다.

select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

여기서 id는 ID 컬럼, n은 원하는 행입니다.테이블의 0 ~count()-1 사이의 난수로 계산됩니다(결국 0은 첫 번째 행입니다).

ORDER BY 절에 대해 작업할 인덱스가 있는 한 테이블 데이터의 구멍에 대해 작동합니다.그것은 또한 무작위성에 매우 좋습니다 - 당신이 그것을 통과시키기 위해 스스로 연습할 때, 그러나 다른 방법에서는 눈에 띄지 않습니다.게다가 퍼포먼스도 꽤 좋기 때문에, 작은 데이터 세트에서도 버틸 수 있습니다.다만, 몇백만 행에 대해서 본격적인 퍼포먼스 테스트를 실시해 본 적은 없습니다.

SQL의 임의 함수가 도움이 될 수 있습니다.또한 한 행으로 제한하려면 마지막에 해당 행을 추가하십시오.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

SQL Server의 경우 "단일 랜덤 행"이 필요합니다.

샘플링이 하지 않은 값 " "을 합니다.[0, max_rows)ORDER BY를 사용합니다.오프셋..SQL Server 2012+에서 FETCH.

이것은 매우 빠른 속도입니다.COUNT ★★★★★★★★★★★★★★★★★」ORDER BY데이터가 쿼리 라인을 따라 '정렬 정렬'되도록 적절한 인덱스 위에 있습니다.이러한 조작을 커버하고 있는 경우, 신속한 요구이며, 사용의 무서운 확장성에 시달리지 않습니다.ORDER BY NEWID()또는 이와 유사합니다.인덱스가 없는 HEAP 테이블에서는 이 접근 방식이 잘 확장되지 않습니다.

declare @rows int
select @rows = count(1) from t

-- Other issues if row counts in the bigint range..
-- This is also not 'true random', although such is likely not required.
declare @skip int = convert(int, @rows * rand())

select t.*
from t
order by t.id -- Make sure this is clustered PK or IX/UCL axis!
offset (@skip) rows
fetch first 1 row only

적절한 트랜잭션 격리 수준을 사용하거나 0개의 결과를 고려해야 합니다.

---

SQL Server의 경우 "일반 행 샘플" 접근법이 필요합니다.

주의: 이것은 SQL Server 고유의 행 샘플 취득에 관한 질문에 대한 답변을 수정한 것입니다.문맥에 맞게 조정되었습니다.

일반적인 표본 추출 접근법은 여기서 주의해서 사용해야 하지만, 다른 답변(및 확장성이 없거나 의심스러운 구현의 반복적 제안)의 맥락에서 여전히 유용한 정보이다.이러한 샘플링 방법은 표시된 첫 번째 코드보다 효율이 떨어지며 "단일 랜덤 행"을 찾는 것이 목표라면 오류가 발생하기 쉽습니다.

---

다음은 일정 비율의 행을 샘플링하는 업데이트된 개선된 형식입니다.CHECKSUM / BINARY_CHECKSUM 및 계수를 사용하는 다른 응답과 동일한 개념을 기반으로 합니다.

• 대용량 데이터 세트에 비해 비교적 빠르며 파생된 쿼리에서 효율적으로 사용할 수 있습니다.tempdb 사용 없이 수백만 개의 사전 필터링된 행을 몇 초 만에 샘플링할 수 있습니다.또한 쿼리의 나머지 부분과 일치할 경우 오버헤드는 최소가 되는 경우가 많습니다.

• 데이터 실행과 관련된 문제를 겪지 않습니다.를 사용하는 경우CHECKSUM(*)접근법, 행은 "선택"이 아닌 "선택"으로 선택할 수 있습니다.이는 CHECSUM이 배포보다 속도를 선호하기 때문입니다.

• 그 결과 안정적인/반복 가능한 행을 선택할 수 있으며 이후 쿼리 실행 시 다른 행을 생성하도록 변경할 수 있습니다.가 사용하는 어프로치NEWID()하다/하다

• 입력 세트가 크면 순서가 큰 병목현상이 발생할 수 있으므로 입력 세트 전체를 사용하지 않습니다.불필요한 정렬을 피하면 메모리와 tempdb 사용률도 줄어듭니다.

• 「」는 .TABLESAMPLE '아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아,WHERE프리필터링

요점은 이렇다.자세한 내용과 참고 사항은 이 답변을 참조하십시오.

순진한 시도:

declare @sample_percent decimal(7, 4)
-- Looking at this value should be an indicator of why a
-- general sampling approach can be error-prone to select 1 row.
select @sample_percent = 100.0 / count(1) from t

-- BAD!
-- When choosing appropriate sample percent of "approximately 1 row"
-- it is very reasonable to expect 0 rows, which definitely fails the ask!
-- If choosing a larger sample size the distribution is heavily skewed forward,
-- and is very much NOT 'true random'.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )

과 혼재된 할 수 .ORDER BY훨씬 작은 샘플 세트에서 선택합니다.그러면 정렬 작업이 원래 테이블의 크기가 아닌 샘플 크기로 제한됩니다.

-- Sample "approximately 1000 rows" from the table,
-- dealing with some edge-cases.
declare @rows int
select @rows = count(1) from t

declare @sample_size int = 1000
declare @sample_percent decimal(7, 4) = case
    when @rows <= 1000 then 100                              -- not enough rows
    when (100.0 * @sample_size / @rows) < 0.0001 then 0.0001 -- min sample percent
    else 100.0 * @sample_size / @rows                        -- everything else
    end

-- There is a statistical "guarantee" of having sampled a limited-yet-non-zero number of rows.
-- The limited rows are then sorted randomly before the first is selected.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )
-- ONLY the sampled rows are ordered, which improves scalability.
order by newid()

 SELECT * FROM table ORDER BY RAND() LIMIT 1

이 솔루션의 대부분은 정렬을 피하는 것을 목적으로 하고 있지만, 여전히 테이블에서 순차적으로 스캔해야 합니다.

인덱스 스캔으로 전환하여 순차 스캔을 피하는 방법도 있습니다.랜덤 행의 인덱스 값을 알고 있으면 결과를 거의 즉각적으로 얻을 수 있습니다.문제는 지수 값을 어떻게 추측하느냐이다.

다음 솔루션은 Postgre에서 작동합니다.SQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

위의 솔루션에서는 범위 0에서 10가지 랜덤 인덱스 값을 추측합니다.[id의 마지막 값]

숫자 10은 임의입니다. 응답 시간에 큰 영향을 미치지 않으므로 100 또는 1000을 사용할 수 있습니다.

또, 1개의 문제가 있습니다.즉, 스퍼스 ID가 있는 경우는, 놓칠 가능성이 있습니다.해결책은 백업플랜을 갖는 것입니다:) 이 경우 random() 쿼리에 의한 완전 오래된 순서입니다.결합된 ID가 다음과 같은 경우:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

union ALL 조항이 아닙니다.이 경우 첫 번째 부품이 데이터를 반환할 경우 두 번째 부품은 절대 실행되지 않습니다!

도 한번 됩니다.new id()★★★★★★ 。

해서 사용순서대로 쓰세요.new id()기능.꽤 무작위적이네요.

아직 답변의 차이를 잘 보지 못했습니다.초기 시드를 지정하면 매번 동일한 행 세트를 선택해야 하는 추가 제약이 있었습니다.

MS SQL의 경우:

최소 예:

select top 10 percent *
from table_name
order by rand(checksum(*))

정규화 실행 시간: 1.00

NewId()의 예:

select top 10 percent *
from table_name
order by newid()

정규화 실행 시간: 1.02

NewId()rand(checksum(*))따라서 큰 레코드 세트에는 사용하지 않는 것이 좋습니다.

초기 시드 선택:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % seed) /* any other math function here */

시드가 지정된 동일한 세트를 선택해야 하는 경우 이 방법이 작동합니다.

MSQL (11.0.5569에서 테스트 완료)에서는

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

보다 현저하게 빠르다

SELECT TOP 100 * FROM employee ORDER BY NEWID()

Firebird의 경우:

Select FIRST 1 column from table ORDER BY RAND()

SQL Server에서는 TABLESSAMPLE과 NEWID()를 조합하여 매우 양호한 랜덤성을 얻으면서도 속도를 유지할 수 있습니다.이것은 1개 또는 소수의 행만 원하는 경우에 특히 유용합니다.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

CD-MaN에 동의해야 합니다.작은 테이블이나 SELECT를 몇 번만 실행할 경우 "ORDER BY RAND()"를 사용하는 것이 좋습니다.

또, 「num_value > = RAND() * ...」테크놀로지를 사용하고 있습니다만, 랜덤한 결과를 얻고 싶은 경우는, 테이블내에 1일 1회 정도 갱신하는 특별한 「filename」컬럼이 있습니다.단일 UPDATE 실행에는 시간이 좀 걸리지만(특히 해당 열에 인덱스가 있어야 하므로), 선택을 실행할 때마다 모든 행에 대해 랜덤 번호를 생성하는 것보다 훨씬 빠릅니다.

Table Sample은 행의 랜덤 표본을 실제로 반환하지 않으므로 주의하십시오.그러면 행을 구성하는 8KB 페이지의 랜덤 샘플이 조회됩니다.그런 다음 이러한 페이지에 포함된 데이터에 대해 조회가 실행됩니다.이러한 페이지에서 데이터를 그룹화하는 방법(삽입 순서 등)으로 인해 실제로 랜덤 샘플이 아닌 데이터가 발생할 수 있습니다.

참조: http://www.mssqltips.com/tip.asp?tip=1308

이 Table Sample MSDN 페이지에는 실제 랜덤 샘플 생성 방법의 예가 포함되어 있습니다.

http://msdn.microsoft.com/en-us/library/ms189108.aspx

기재되어 있는 아이디어의 대부분은 아직 주문을 사용하고 있는 것 같습니다.

그러나 임시 표를 사용하는 경우 많은 솔루션이 제안한 것처럼 랜덤 인덱스를 할당한 다음 0과 1 사이의 임의 숫자보다 큰 첫 번째 인덱스를 가져올 수 있습니다.

예를 들어(DB2의 경우):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

http://akinas.com/pages/en/blog/mysql_random_row/에서 심플하고 효율적인 방법

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

dbms_random.value를 사용하는 대신 Oracle을 위한 더 나은 솔루션이 있지만 dbms_random.value를 기준으로 행을 정렬하려면 전체 검사가 필요하며 테이블 크기가 큰 경우 속도가 상당히 느립니다.

대신 이것을 사용하세요.

SELECT *
FROM employee sample(1)
WHERE rownum=1

SQL Server 2005 이상에서는 다음과 같은 경우에 대해 @GreyPanther의 답변을 확장합니다.num_value에는 연속적인 값이 없습니다.이는 데이터셋이 균등하게 분산되어 있지 않은 경우나num_value는 숫자가 아니라 하나의 식별자입니다.

WITH CTE_Table (SelRow, num_value) 
AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM table
) 

SELECT * FROM table Where num_value = ( 
    SELECT TOP 1 num_value FROM CTE_Table  WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)

select r.id, r.name from table AS r
INNER JOIN(select CEIL(RAND() * (select MAX(id) from table)) as id) as r1
ON r.id >= r1.id ORDER BY r.id ASC LIMIT 1

이를 위해서는 더 적은 계산 시간이 필요합니다.

언급URL : https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql