개요
MS-SQL(SQL Server) 에서는 NEWID() 함수를 이용하여 임의의 데이터를 조회할 수 있다.
예를 들면, 1부터 10까지의 숫자가 있을 경우 NEWID() 함수를 통해 1~10 사이의 임의의 값을 출력하여 조회할 수 있다.
-- 테이블 변수 생성
DECLARE @temp_table TABLE(
num int
)
-- 테이블 변수에 1~10의 값 추가
DECLARE @i int = 1
WHILE @i <=10 BEGIN
INSERT INTO @temp_table
SELECT @i
SET @i += 1
END
-- 1~10 사이의 랜덤 값 추출
SELECT TOP 1 * FROM @temp_table ORDER BY NEWID();
위의 쿼리를 실행하면 'num' 컬럼의 값이 1에서 10 사이의 값이 랜덤하게 나오는 것을 확인할 수 있다.
우리는 NEWID() 함수를 통해 데이터베이스 내의 임의의 값을 쿼리로 추출할 수 있다.
혹시나 테이블 변수, NEWID() 함수에 대해 자세히 알고 싶으면 아래의 포스팅을 참고하자.
가상 시나리오 작성
로또 번호를 랜덤으로 생성하는 쿼리를 짜본다고 가정해보자.
프로그램으로 짜면 쉬운데 굳이 왜 이런 로직을 데이터베이스에서 쿼리를 작성해야하는지 의문이 들 수도 있다.
하지만 여러 환경에 의해 SQL 쿼리로 로직을 짜야할 때도 있다.
실제로 내가 다니고 있는 회사에서는 거의 모든 처리 로직을 데이터베이스 내 프로시저로 처리하고 있다.
본론으로 돌아와서 내용을 정리해보면,
로또 번호를 랜덤으로 생성할 때 첫 번째 랜덤으로 뽑힌 숫자는 다음에 뽑을 숫자에서 제외되어야 한다.
즉, 첫 번째 랜덤한 값과 두 번째 랜덤한 값은 중복이 되면 안된다.
요구 사항은 다음과 같다.
- 데이터베이스 숫자 테이블 내의 데이터에서 여러 개의 값 중 랜덤으로 특정 값을 추출해야한다.
- 첫 번째로 뽑은 랜덤 값을 토대로, 두 번째로 뽑은 값은 첫 번째 값과 중복이 되지 않아야 한다.
내가 쿼리로 작성했던 내용은 다음과 같다.
- WITH 구문을 사용
- 첫 번째 테이블에 랜덤의 첫 번째 값을 구함
- 두 번째 테이블에 첫 번째로 구한 값을 WHERE 조건절에 NOT IN을 이용하여 해당 중복값을 제거하고 새로 랜덤의 두 번째 값을 구함
- 두 랜덤한 결과값을 UNION 을 통해 데이터 통합
정말 중복 없이 서로 다른 숫자가 조회될까?
극단적으로 1과 2의 숫자만 있다고 하고, 위의 내용대로 쿼리를 작성해보자.
[ 주의! 꼭 확인해야 할 것 ]
예제 쿼리에서 사용되는 테이블 변수는 실행 시에만 존재하는 변수로, 하나의 단위로 실행시켜야 한다.
즉, 아래의 쿼리를 모두 한꺼번에 실행해야 된다.
-- 테이블 변수 생성
DECLARE @temp_table TABLE(
num int
)
-- 테이블 변수에 1~2의 값 추가
DECLARE @i int = 1
WHILE @i <=2 BEGIN
INSERT INTO @temp_table
select @i
SET @i += 1
END
;WITH
-- 첫 번째 랜덤 값 추출
cte_first AS (
SELECT TOP 1 * FROM @temp_table ORDER BY NEWID()
),
-- 두 번째 랜덤 값 추출
cte_second AS (
SELECT TOP 1 * FROM @temp_table
WHERE
num not in (
SELECT num FROM cte_first
)
ORDER BY NEWID()
)
-- 랜덤 데이터 조회
SELECT * FROM cte_first
UNION
SELECT * FROM cte_second
두 번째 랜덤값은 첫 번째 값을 제외하고 랜덤으로 추출하였으니,
분명히 결과 값은 무조건 1과 2가 모두 나와야 한다.
과연 그럴까?
아니다.
여러 번을 조회해보면 실제로는 무조건적으로 1과 2의 값이 모두 나오지 않는다.
쿼리를 실행하면 다음과 같은 결과 중 하나의 결과를 마주하게 된다.
- 1과 2 모두 출력
- 1만 출력
- 2만 출력
왜 그럴까?
NEWID() 함수의 랜덤 액세스의 원리
NEWID() 함수는 쿼리가 실행 될 때마다 매번 다른 결과값을 호출한다.
그러니까 위의 쿼리에서는 첫 번째 값을 중복 안되게 제거하고 나머지의 값을 랜덤으로 다시 값을 액세스 하는 것이 아닌,
독립적으로 랜덤으로 액세스 된 값에서 첫 번째 값과 중복되는지 확인하게 될 뿐인 것이라는거다.
이해하기 쉽게 더 쉬운 쿼리로 작성해보자.
WITH 구문을 사용하여 무조건 하나의 랜덤 값만을 뽑도록 해보자.
그리고 해당 테이블을 중복이 있도록 UNION ALL로 합쳐 두 번 조회해보자.
사실 아래의 쿼리는 상사님께서 이해를 시켜주기 위해 쿼리를 쉽게 작성해주신 예제이다 :)
-- 테이블 변수 생성
DECLARE @temp_table TABLE(
num int
)
-- 테이블 변수에 1~2의 값 추가
DECLARE @i int = 1
WHILE @i <=2 BEGIN
INSERT INTO @temp_table
select @i
SET @i += 1
END
;WITH test AS (
SELECT TOP 1 num FROM @temp_table ORDER BY NEWID()
)
SELECT * FROM test
UNION ALL
SELECT * FROM test
결과는 다음과 같다.
- 각각 1, 2
- 각각 2, 1
- 각각 1, 1
- 각각 2, 2
그렇다면 랜덤 값 중복 제거는 어떻게 해야할까?
랜덤 값을 중복 없이 추출하여 조회하려면 어떻게 해야할까?
정답은 하나의 쿼리 내에서 NEWID() 함수를 각각 독립적으로 실행되지 않게 하면 된다.
이제 극단적인 1과 2만 있는 숫자 테이블에서,
각각의 값이 서로 중복되지 않도록 랜덤으로 출력하는 쿼리를 다시 작성해보자.
-- 테이블 변수 생성
DECLARE @temp_table TABLE(
num int
)
-- 테이블 변수에 1~2의 값 추가
DECLARE @i int = 1
WHILE @i <=2 BEGIN
INSERT INTO @temp_table
select @i
SET @i += 1
END
-- 랜덤값을 저장할 테이블 변수 생성
DECLARE @rand_table TABLE(
rand_num int
)
-- 첫 번째 랜덤 값 테이블 변수에 저장
INSERT INTO @rand_table
SELECT TOP 1 * FROM @temp_table ORDER BY NEWID()
-- 두 번째 랜덤 값 테이블 변수에 저장 : 테이블 변수에서 첫 번째 값을 확인하여 제외
INSERT INTO @rand_table
SELECT TOP 1 * FROM @temp_table
WHERE
num NOT IN (
SELECT rand_num FROM @rand_table
)
ORDER BY NEWID()
-- 랜덤 데이터 조회
SELECT * FROM @rand_table
첫 번째 NEWID() 함수로 추출된 램덤의 값을 테이블에 저장하고
다음 두 번째 랜덤 값 호출에서 데이터 중복 검사를 할 때,
NEWID() 함수를 독립적으로 실행되게 하지 않고 저장된 테이블에서 랜덤 값을 확인하여 중복을 제거하는 방식이다.
결과 값은 이제 중복이 되지 않고 1과 2 혹은 2와 1만 출력된다.
이 글을 작성한 이유는,
프로시저 내에서 짠 쿼리문의 결과가 자꾸 중복이 제거되지 않고 중복된 데이터가 함께 조회되는 문제로
며칠을 골머리 섞다가 시니어분께 요청하여 해결하게 된 결과를 공유하려는 마음에 작성하였다.
내가 너무 하나의 긴 쿼리문으로 모든 것을 해결하려 하진 않았는지 반성하게 된다.