SQL Server 에서 무작위로 (random) 데이터를 가져오는 방법
SQL Server2019. 2. 9. 06:52
Sql server 에서는 Random sampling 을 위해서 newid() 라는 함수를 주로 사용하는데 이 함수는 각 행별로 GUID (globally unique identifier) 값을 만들어 낸다. 이것은 중복이 없는 값이고 호출할때 마다 다른 값을 리턴한다.
따라서 order by 절을 이용하여 정렬하면 항상 다른 행을 리턴하게 된다.
랜덤 샘플링을 위해서 newid() 함수를 이용한 데이터 selection 은 일반적으로 제시되는 방법이다. 하지만 테이블이 매우 크거나 빠른시간내에 결과값이 나와야 하는경우 이 방법은 치명적인 약점을 가지고 있다. 전체 데이터에 대한 guid 생성과 이를 다시 정렬하여 상위 몇개를 뽑아 내는것은 보통 부하를 유발하는 것이 아니다.
우선 newid() 함수를 이용한 random sampling 을 알아보고 성능을 고려한 상위 몇개의 데이터를 추출하는 쿼리를 알아보자.
newid() 를 이용한 구문
SELECT TOP 1 col1 FROM table ORDER BY NEWID()
위 쿼리를 실제 테이블에 적용하여 실행계획을 확인한 것이다. Clustered index 를 순차적으로 스캔하면서 newid() 값을 만들고 전체 데이터에 대해 정렬을 하는데 정렬하는 비용이 장난이 아니다.
성능확보를 위한 트릭
SELECT TOP 1 col1 FROM table WHERE ascii(substring(col1, cast(floor(rand()*10) as int), 1))=97
위 쿼리의 실행계획을 보면 데이터 정렬과정이 없다. 따라서 매우 빠르게 상위 몇개의 결과값을 가져올수 있다. 위 쿼리에 대해 설명을 하자면 col1 이라는 column 에 대해서 cast(floor(rand()*10) as int) 에서 나온 integer 값부터 1 자리를 잘라서 ascii 값을 취할때 97(소문자 a) 값이 나오는 데이터를 가져 오라는 조건이다. cast(floor(rand()*10) as int) 에 의해 실행시마다 substring 의 start 값이 변하기 때문에 어느정도 random 결과값을 구할수 있다.
위 쿼리는 단순한 로직이기 때문에 전체 테이블에서 랜덤하게 결과값이 나오진 않는다. clustered index scan 을 하면서 매치된 결과값을 가져오기 때문에 col1 에 입력된 길이가 짧고 단순한 값이면 몇번을 수행하면 같은 값이 리턴 될수 있다.
'SQL Server' 카테고리의 다른 글
SQL Server 테이블 데이터 중복제거 방법 (0) | 2019.02.09 |
---|---|
SQL Server 조인(join) 을 이용한 데이터 업데이트 방법 (0) | 2019.02.09 |
SQL Server default 제약조건(constraint) 추가, 변경, 제거 (0) | 2019.02.09 |
SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제 (0) | 2019.02.08 |
SQL Server 에서 mysql 의 group_concat 과 같은 효과 내기 (0) | 2019.02.08 |