source

SQL Server 테이블에서 n개의 임의 행 선택

myloves 2023. 5. 28. 23:09

SQL Server 테이블에서 n개의 임의 행 선택

SQL Server 테이블에 약 50,000개의 행이 있습니다.저는 그 행들 중 약 5,000개를 무작위로 선택하고 싶습니다.방법을 "하고, 임시 하고, 각 하는 것입니다. "" 복수한방생난보각니다습았해복고열것업행각다니하트입이는반데을고테하복임을시블이저하사내테는을블거이기에고들만을이블잡이테을법는시있임▁i,",▁"▁each▁with▁row▁table▁column▁creatingr.RAND()그런 다음 해당 표에서 난수 열이 < 0.1인 곳을 선택합니다.저는 가능하다면 단일 명세서에서 더 간단한 방법을 찾고 있습니다.

이 문서에서는 다음을 사용할 것을 제안합니다.NEWID()ㅠㅠ. 것 같은데 일정 을 안정적으로 할 수 있었는지 유망해 보이지만 어떻게 특정 비율의 행을 안정적으로 선택할 수 있었는지 알 수 없습니다.

전에 이런 적 있는 사람?아이디어 있어요?

select top 10 percent * from [yourtable] order by newid()

큰 테이블에 대한 "순수 쓰레기" 의견에 대해: 성능을 향상시키기 위해 이렇게 할 수 있습니다.

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

이 비용은 키 스캔 값에 조인 비용을 더한 값으로, 비율 선택이 적은 큰 테이블에서 합리적이어야 합니다.

필요에 , 의필따라요에당신,,TABLESAMPLE할 수 있습니다.MS SQL 서버 2005 이상에서 사용할 수 있습니다.

TABLESAMPLE는 임의의 행 대신 임의의 페이지에서 데이터를 반환하므로 반환하지 않는 데이터도 검색하지 않습니다.

내가 테스트한 매우 큰 테이블에서

select top 1 percent * from [tablename] order by newid()

20분 이상 걸렸습니다.

select * from [tablename] tablesample(1 percent)

2분이 걸렸습니다.

의작샘서도성향다니상됩능이플의 작은 입니다.TABLESAMPLE반면에 그것은 하지 않을 것입니다.newid().

이것은 그들처럼 무작위적이지 않다는 것을 명심하세요.newid()방법이지만 괜찮은 샘플링을 제공할 것입니다.

MSDN 페이지를 참조하십시오.

newid()/orderby는 작동하지만 모든 행에 대해 id를 생성한 다음 정렬해야 하므로 큰 결과 집합의 경우 매우 비쌉니다.

TABLESAMPLE()은 성능 측면에서는 좋지만 결과의 군집을 얻을 수 있습니다(페이지의 모든 행이 반환됨).

실제 랜덤 표본을 더 잘 수행하려면 행을 랜덤하게 필터링하는 것이 가장 좋습니다.SQL Server Books 온라인 기사 TABLESAMPLE을 사용한 결과 집합 제한에서 다음 코드 샘플을 찾았습니다.

개별 행의 랜덤 표본을 원하는 경우 TABLESSAMPLE을 사용하는 대신 행을 랜덤으로 필터링하도록 쿼리를 수정합니다.예를 들어, 다음 쿼리는 NEWID 함수를 사용하여 판매 행의 약 1%를 반환합니다.판매 주문 세부 정보 표:

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

판매 주문서ID 열은 NEWID()가 행당 한 번씩 평가하여 행당 샘플링을 달성하도록 CHECKSUM 표현식에 포함됩니다.CAST(CHECKSUM(NEWID()), SalesOrder라는 표현식ID) & 0x7ffffff AS float / CAST(0x7ffffff ASint)는 0과 1 사이의 임의의 float 값으로 평가합니다.

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

TABLESSAMPLE을 사용하지 않으신다면 최고의 성능을 제공할 것입니다.그렇지 않으면 newid()/filter 메서드를 사용합니다.newid()/orderby는 결과 집합이 큰 경우 마지막 수단이 되어야 합니다.

MSDN의 큰 테이블에서 행을 무작위로 선택하는 것은 대규모 성능 문제를 해결하는 간단하고 명확한 솔루션입니다.

  SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

이 링크는 1, 7 및 1,300만 행의 테이블에 대한 Orderby(NEWID())와 다른 방법을 흥미롭게 비교합니다.

종종 토론 그룹에서 임의 행을 선택하는 방법에 대한 질문이 있을 때 NEWID 쿼리가 제안됩니다. NEWID 쿼리는 간단하고 작은 테이블에 매우 적합합니다. 

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

그러나 NEWID 쿼리는 큰 테이블에 사용할 때 큰 단점이 있습니다.ORDER BY 절을 사용하면 테이블의 모든 행이 tempdb 데이터베이스에 복사되어 정렬됩니다.이로 인해 다음과 같은 두 가지 문제가 발생합니다.

  1. 일반적으로 정렬 작업에는 비용이 많이 듭니다.정렬은 많은 디스크 I/O를 사용할 수 있으며 장기간 실행될 수 있습니다.
  2. 최악의 경우 tempdb의 공간이 부족해질 수 있습니다.최상의 시나리오에서는 tempdb가 많은 양의 Disk 공간을 차지할 수 있으며 이는 수동 축소 명령 없이는 회수되지 않습니다.

tempdb를 사용하지 않고 테이블이 커질수록 속도가 크게 느려지지 않는 행을 임의로 선택하는 방법이 필요합니다.이 방법에 대한 새로운 아이디어가 있습니다.

SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

이 쿼리의 기본 개념은 테이블의 각 행에 대해 0에서 99 사이의 임의의 숫자를 생성한 다음 지정된 백분율 값보다 작은 임의의 숫자를 가진 모든 행을 선택하는 것입니다.이 예제에서는 행의 약 10%를 랜덤하게 선택하기 때문에 랜덤 번호가 10보다 작은 모든 행을 선택합니다.

MSDN의 전체 기사를 읽어보시기 바랍니다.

표를 임의의 숫자로 정렬하고 처음 5,000개의 행을 얻기만 하면 됩니다.TOP.

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

갱신하다

방금 해봤는데요.newid()통화로 충분합니다. 모든 출연진과 모든 수학이 필요하지 않습니다.

(OP와 달리) 특정 레코드 수가 필요하고(이로 인해 CHECKMSAMPLE 접근이 어려워짐) TABLESSAMPLE이 자체적으로 제공하는 것보다 더 랜덤한 샘플을 원하고 또한 CHECKMSAMPLE보다 더 나은 속도를 원한다면 다음과 같은 TABLESSAMPLE과 NEWID() 메서드를 병합하여 사용할 수 있습니다.

DECLARE @sampleCount int = 50
SET STATISTICS TIME ON

SELECT TOP (@sampleCount) * 
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()

SET STATISTICS TIME OFF

저의 경우, 이것은 무작위성(사실은 그렇지 않습니다, 저도 압니다)과 속도 사이의 가장 직접적인 절충안입니다.표 표본 백분율(또는 행)을 적절하게 변경합니다. 백분율이 높을수록 표본이 랜덤하지만 속도가 선형으로 떨어질 것으로 예상됩니다.(TABLESSAMPLE은 변수를 허용하지 않습니다.)

이것은 초기 시드 아이디어와 체크섬을 결합한 것으로, NEWID() 비용 없이 적절한 무작위 결과를 제공할 것으로 보입니다.

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

MySQL에서 다음 작업을 수행할 수 있습니다.

SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;

아직 답변에서 이러한 변화를 제대로 보지 못했습니다.초기 시드가 주어지면 매번 동일한 행 집합을 선택해야 하는 추가적인 제약이 있었습니다.

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

시드가 지정된 동일한 세트를 선택해야 하는 경우 이 방법이 효과적인 것 같습니다.

사용해 보십시오.

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

여기에 업데이트되고 개선된 샘플링 형식이 있습니다.이는 다음을 사용하는 일부 다른 답변의 동일한 개념을 기반으로 합니다.CHECKSUM/BINARY_CHECKSUM그리고 계수.

다른 답변과 달리 이와 유사한 구현을 사용해야 하는 이유:

  • 대규모 데이터 세트에 비해 상대적으로 빠르며 파생 쿼리에 효율적으로 사용할 수 있습니다.tempdb를 사용하지 않고 수백만 개의 미리 필터링된 행을 몇 초 만에 샘플링할 수 있으며, 나머지 쿼리와 정렬된 경우 오버헤드가 거의 없는 경우가 많습니다.
  • 데이터 실행에 문제가 발생하지 않습니다.사용 시CHECKSUM(*)접근하면 행을 "선택"할 수 있고 "선택"할 수 없습니다!이는 CHECKMS가 분배보다 속도를 선호하기 때문입니다.
  • 결과적으로 안정적이고 반복 가능한 행을 선택할 수 있으며 이후 쿼리 실행 시 다른 행을 생성하도록 사소한 변경이 가능합니다.사용하는 접근 방식NEWID()를 들어, 를들어예와 같은CHECKSUM(NEWID()) % 100절대 안정적일 수 없습니다.
  • 표본 정밀도를 높이고 도입된 통계 오류를 줄일 수 있습니다.샘플링 정밀도를 조정할 수도 있습니다. CHECKSUM만 합니다.intvalue.value.value.
  • 를 사용하지 않습니다. 대량 입력 세트의 경우 주문이 중요한 병목 현상이 될 수 있습니다.정렬을 피하면 메모리 및 tempdb 사용도 줄어듭니다.
  • 사안함을 하지 않습니다.TABLESAMPLE 따서와함작니다합동께라▁a▁with다▁and와 함께 작동합니다.WHERE사전 필터링

단점 / 제한 사항:

  • 이 약간 실행시사속약느림을 사용합니다.CHECKSUM(*)아래와 같이 해시 바이트를 사용하면 백만 회선당 약 3/4초의 오버헤드가 추가됩니다.이것은 제 데이터베이스 인스턴스인 YMMV에 대한 데이터입니다.이 오버헤드는 HASHBYTS에서 '잘 분산된' 결과 값의 지속적인 계산 열을 사용하는 경우 제거할 수 있습니다.
  • 인 기과달와는 본리달.SELECT TOP n .. ORDER BY NEWID()정확히 N개의 행을 반환하는 것은 보장되지 않습니다.대신 해당 값이 미리 결정된 백분율 행 행을 반환합니다.표본 크기가 매우 작은 경우 0개의 행이 선택될 수 있습니다.이 제한 사항은 다음과 같습니다.CHECKSUM(*)접근법

요점은 다음과 같습니다.

-- Allow a sampling precision [0, 100.0000].
declare @sample_percent decimal(7, 4) = 12.3456

select
    t.*
from t
where 1=1
    and t.Name = 'Mr. No Questionable Checksum Usages'
    and ( -- sample
        @sample_percent = 100
        or abs(
            -- Choose appropriate identity column(s) for hashbytes input.
            -- For demonstration it is assumed to be a UNIQUEIDENTIFIER rowguid column.
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )

주의:

  • SHA1은 SQL Server 2016 이후로 기술적으로 더 이상 사용되지 않지만 작업에 충분하고 MD5 또는 SHA2_256보다 약간 빠릅니다.관련된 다른 해싱 기능을 사용합니다.테이블에 이미 해시 열(분포가 양호한)이 있는 경우 이 열도 사용될 수 있습니다.
  • 의 bigint모듈러스 연산자를 적용할 2^63비트의 '랜덤 공간'을 허용하기 때문에 중요합니다. 이는 CHECKMS 결과의 2^31 범위보다 훨씬 더 큽니다.이렇게 하면 특히 정밀도가 높아지면 한계에서 계수 오차가 줄어듭니다.
  • 계수 피연산자와 표본 백분율을 적절히 곱하면 표본 정밀도를 변경할 수 있습니다.즉 이경우, 즉즉▁in입니다.1000 *에서 허용되는 4자리 정밀도를 설명합니다.@sample_percent.
  • 곱셈을 할 수 있습니다.bigint으로 평가함.RAND()각 실행마다 다른 행 표본을 반환합니다.이렇게 하면 고정 해시 값의 순열이 효과적으로 변경됩니다.
  • 한다면@sample_percent완전히 할 수 . 100 쿼 플 래 은 너 느 계 코 완 제 있 수 습 니 다 할 거 히 전 를 드 리 산 린 가 ▁planner 니 습 있 다 ▁is 100' 변수 규칙을 매개 변수 스니핑' 규칙을 기억하십시오.이렇게 하면 샘플링 활성화에 관계없이 쿼리에 코드를 남길 수 있습니다.

컴팅@sample_percent및 하상/한및추 TOP샘플이 파생된 테이블 컨텍스트에서 사용될 때 유용할 수 있는 쿼리의 "message".

-- Approximate max-sample and min-sample ranges.
-- The minimum sample percent should be non-zero within the precision.
declare @max_sample_size int = 3333333
declare @min_sample_percent decimal(7,4) = 0.3333
declare @sample_percent decimal(7,4) -- [0, 100.0000]
declare @sample_size int

-- Get initial count for determining sample percentages.
-- Remember to match the filter conditions with the usage site!
declare @rows int
select @rows = count(1)
    from t
    where 1=1
        and t.Name = 'Mr. No Questionable Checksum Usages'

-- Calculate sample percent and back-calculate actual sample size.
if @rows <= @max_sample_size begin
    set @sample_percent = 100
end else begin
    set @sample_percent = convert(float, 100) * @max_sample_size / @rows
    if @sample_percent < @min_sample_percent
        set @sample_percent = @min_sample_percent
end
set @sample_size = ceiling(@rows * @sample_percent / 100)

select *
from ..
join (
    -- Not a precise value: if limiting exactly at, can introduce more bias.
    -- Using 'option optimize for' avoids this while requiring dynamic SQL.
    select top (@sample_size + convert(int, @sample_percent + 5))
    from t
    where 1=1
        and t.Name = 'Mr. No Questionable Checksum Usages'
        and ( -- sample
            @sample_percent = 100
            or abs(
                convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
            ) % (1000 * 100) < (1000 * @sample_percent)
        )
) sampled
on ..

newid()는 where 절에서 사용할 수 없으므로 이 솔루션에는 내부 쿼리가 필요합니다.

SELECT *
FROM (
    SELECT *, ABS(CHECKSUM(NEWID())) AS Rnd
    FROM MyTable
) vw
WHERE Rnd % 100 < 10        --10%

하위 쿼리에서 사용하고 있었는데 하위 쿼리에서 동일한 행이 반환되었습니다.

 SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

그런 다음 나는 부모 테이블 변수를 포함하여 해결했습니다.

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

위치 조건을 기록합니다.

사용 중인 서버 측 처리 언어(예: PHP, .net 등)는 지정되지 않았지만, PHP인 경우 필요한 숫자(또는 모든 레코드)를 가져와 쿼리에서 무작위화하는 대신 PHP의 셔플 기능을 사용합니다..net에 동등한 기능이 있는지는 모르겠지만 만약 당신이 .net을 사용한다면 그것을 사용합니다.

ORDER BY RAND()는 관련된 레코드 수에 따라 상당한 성능 저하를 초래할 수 있습니다.

select  * from table
where id in (
    select id from table
order by random()
limit ((select count(*) from table)*55/100))

// to select 55 percent of rows randomly

약 N개의 행이 있고 약 K개의 랜덤 행을 원하는 경우 주어진 행을 K/N으로 당기면 됩니다.0과 1 사이의 공정한 분포를 제공하는 RAND() 함수를 사용하면 PROB = K/N에서 다음을 수행할 수 있습니다.저를 위해 매우 빠르게 일했습니다.

SELECT * FROM some_table WHERE RAND() < PROB

이것은 나에게 도움이 됩니다.

SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]

언급URL : https://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table

저작자표시 (새창열림)

'source' 카테고리의 다른 글

VB.NET에서 어레이를 인라인으로 선언하는 방법  (0) 2023.05.28
UIColor가 어두운지 밝은지 확인하십시오.  (0) 2023.05.28
Mongodb : $in 연산자 대 단일 쿼리 로트  (0) 2023.05.28
pip 또는 설치 패키지를 업데이트하려고 할 때 "경고: scheme.data 값이 일치하지 않음"  (0) 2023.05.28
.NET 4.5의 기본 보안 프로토콜  (0) 2023.05.28

'source'의 다른글

  • 현재글SQL Server 테이블에서 n개의 임의 행 선택

관련글

댓글

티스토리툴바