Programing

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

lottogame 2020. 2. 13. 00:27
반응형

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 ()은 성능 관점에서는 좋지만 결과가 뭉치 게됩니다 (페이지의 모든 행이 반환 됨).

보다 정확한 성능의 실제 무작위 표본을 추출하는 가장 좋은 방법은 무작위로 행을 필터링하는 것입니다. SQL Server 온라인 설명서의 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;

가장 좋은 방법은 그 목적을 위해 새로운 열에 임의의 값을 넣고 다음과 같은 것을 사용하는 것입니다 (의사 코드 + SQL).

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

이것이 MediaWiki 코드에 의해 채택 된 솔루션입니다. 물론 더 작은 값에 대해서는 약간의 편견이 있지만 행을 가져 오지 않을 때 임의의 값을 0으로 감싸는 것으로 충분하다는 것을 알았습니다.

newid () 솔루션은 각 행에 새 guid를 할당 할 수 있도록 전체 테이블 스캔이 필요할 수 있으며, 성능이 훨씬 떨어집니다.

rand () 솔루션은 함수가 한 번만 평가되고 모든 행에 동일한 "무작위"번호가 지정 되므로 MSSQL에서 전혀 작동하지 않을 수 있습니다.

SQL Server 2005 및 2008의 경우 ( Books Online의 ) 개별 행의 임의 샘플을 원할 경우 :

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

권장되지 않는 RAND () 를 사용 하면 max ID (= Max)를 얻을 수 있습니다.

SELECT MAX(ID) FROM TABLE;

1..Max (= 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

@ cill의 답변에 대한 @BillKarwin의 의견에서 지적했듯이 ...

LIMIT와 결합 할 때 실제 행을 직접 정렬하는 대신 임의의 순서로 JOIN하는 것이 훨씬 좋습니다 (최소한 PostgreSQL 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

'r'은 복잡한 쿼리에서 가능한 모든 키 값에 대해 'rand'값을 생성해야합니다.이 값은 조인되지만 가능한 경우 여전히 'r'의 행 수를 제한합니다.

정수형 CAST는 특히 정수 및 단 정밀도 부동 유형에 대한 특정 정렬 최적화 기능이있는 PostgreSQL 9.2에 유용합니다.

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

인덱스 스캔으로 전환하여 순차 스캔을 피하는 방법도 있습니다. 임의의 행의 인덱스 값을 알고 있으면 거의 즉각적으로 결과를 얻을 수 있습니다. 문제는 인덱스 값을 추측하는 방법입니다.

다음 솔루션은 PostgreSQL 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은 (놀랍게도) 응답 시간에 큰 영향을 미치지 않으므로 사용할 수 있습니다.

한 가지 문제가 있습니다. 스파 스 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 절이 아닙니다 . 이 경우 첫 번째 부분이 데이터를 반환하면 두 번째 부분은 절대 실행되지 않습니다!

늦었지만 Google을 통해 여기에 왔으므로 후손을 위해 대체 솔루션을 추가하겠습니다.

또 다른 방법은 번갈아 가면서 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 () 유형의 쿼리 실행 시간의 약 1/10에서 실행됩니다. YYMV.

new id()기능을 사용해보십시오 .

검색어를 작성하고 new id()기능별 순서를 사용하십시오 . 꽤 무작위입니다.

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/

아직 답변 에서이 변형을 보지 못했습니다. 매번 동일한 행 집합을 선택하기 위해 초기 시드가 주어지면 추가 제약이있었습니다.

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 */

시드가 지정된 동일한 세트를 선택 해야하는 경우 작동하는 것 같습니다.

MSSQL에서 (11.0.5569에서 테스트) 

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

보다 훨씬 빠르다

SELECT TOP 100 * FROM employee ORDER BY NEWID()

SQL Server에서는 TABLESAMPLE을 NEWID ()와 결합하여 꽤 좋은 임의성을 확보하고 여전히 속도를 유지할 수 있습니다. 이것은 실제로 1 또는 적은 수의 행만 원하는 경우에 특히 유용합니다.

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

 SELECT * FROM table ORDER BY RAND() LIMIT 1

CD-MaN에 동의해야합니다. "ORDER BY RAND ()"를 사용하면 작은 테이블이나 SELECT를 몇 번만 수행 할 때 잘 작동합니다.

또한 "num_value> = RAND () * ..."기술을 사용하며, 실제로 임의의 결과를 원하면 테이블에 하루에 한 번 정도 업데이트되는 특수한 "무작위"열이 있습니다. 단일 UPDATE 실행에는 다소 시간이 걸리지 만 (특히 해당 열에 인덱스가 있어야하기 때문에) 선택이 실행될 때마다 모든 행에 대해 임의의 숫자를 만드는 것보다 훨씬 빠릅니다.

TableSample은 실제로 임의의 행 샘플을 반환하지 않으므로주의하십시오. 쿼리가 행을 구성하는 8KB 페이지의 임의 샘플을 보도록 지시합니다. 그런 다음이 페이지에 포함 된 데이터에 대해 쿼리가 실행됩니다. 이러한 페이지에서 데이터를 그룹화하는 방법 (삽입 순서 등)으로 인해 실제로 임의 샘플이 아닌 데이터가 발생할 수 있습니다.

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

이 TableSample의 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

파이어 버드의 경우 :

Select FIRST 1 column from table ORDER BY RAND()

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

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

여기서 id는 식별 열이고 n은 원하는 행입니다. 테이블의 0과 count ()-1 사이의 난수로 계산됩니다 (오프셋 0은 결국 첫 번째 행).

ORDER BY 절에 대해 작업 할 인덱스가 있으면 테이블 데이터의 구멍에 대해 작업합니다. 그것은 또한 무작위성에 매우 좋습니다-당신이 스스로 통과하도록 노력하지만 다른 방법의 틈새는 존재하지 않습니다. 또한 성능은 꽤 좋으며 작은 데이터 세트에서는 잘 유지되지만 수백만 행에 대해 심각한 성능 테스트를 시도하지는 않았습니다.

SQL Server 2005 이상에서 num_value연속 값이없는 경우에 대한 @GreyPanther의 답변을 확장 합니다. 데이터 세트를 균등하게 분배하지 않은 경우와 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)
)

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

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

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



반응형

'Programing' 카테고리의 다른 글

Git에서 원격 브랜치 추적을 어떻게 중지합니까?  (0) 2020.02.13
grep을 사용하여 폴더 안에서 단어를 찾으려면 어떻게해야합니까?  (0) 2020.02.13
새로운 TTY로 이미 실행중인 Docker 컨테이너에 입력하는 방법  (0) 2020.02.13
JSLint는 "missing radix parameter"라고 말합니다.  (0) 2020.02.13
자식 분기, 포크, 가져 오기, 병합, 리베이스 및 복제의 차이점은 무엇입니까?  (0) 2020.02.13

'Programing'의 다른글

  • 현재글SQL에서 임의의 행을 요청하는 방법은 무엇입니까?

관련글

댓글

티스토리툴바