MSSQL - 확률성부여 및 큰수의 법칙 + 해결방안

CREATE PROCEDURE [dbo].[usp_insert_event_participation_2025]
    @memberId NVARCHAR(50),
    @eventCategory INT OUTPUT,
    @statusCode BIT OUTPUT,
    @statusMessage NVARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @rewardPoints INT;
    DECLARE @description NVARCHAR(100);
    DECLARE @expiryDate VARCHAR(20);
    DECLARE @randomValue FLOAT;
    DECLARE @retryCount INT = 0;
    DECLARE @selectedEventType INT;
    DECLARE @couponCode NVARCHAR(30);
    DECLARE @sourceID INT = 1234;

    -- 기본값 초기화
    SET @statusCode = 0;
    SET @statusMessage = N'처리가 완료되지 않았습니다.';
    SET @eventCategory = 0;

    IF (GETDATE() > CONVERT(DATETIME, '2025-02-14 00:00:00'))
    BEGIN
        SET @statusCode = 0;
        SET @statusMessage = N'종료되었습니다.';
        RETURN;
    END;

    -- 사용자 정보 복호화
    -- 복호화는 개인 및 그룹별 다르므로 서술하지 않음.

    -- 이벤트 타입 결정
    SET @randomValue = RAND();

    WHILE @retryCount < 10
    BEGIN
        IF @randomValue <= 0.5
            SET @selectedEventType = 1;
        ELSE IF @randomValue <= 0.8
            SET @selectedEventType = 2;
        ELSE IF @randomValue <= 0.95
            SET @selectedEventType = 3;
        ELSE
            SET @selectedEventType = 4;

        IF @selectedEventType = 1 OR NOT EXISTS (
            SELECT 1 FROM [event_schema].[dbo].[EventParticipation2025]
            WHERE eventCategory = @selectedEventType
              AND CONVERT(DATE, registeredAt) = CONVERT(DATE, GETDATE())
        )
        BEGIN
            SET @eventCategory = @selectedEventType;
            BREAK;
        END;

        SET @randomValue = RAND();
        SET @retryCount = @retryCount + 1;
    END;

    -- 포인트 또는 쿠폰 지급
    IF @eventCategory = 1
    BEGIN
        SET @rewardPoints = 100;
        SET @description = '이벤트 보너스 100P';
        SET @expiryDate = CONVERT(VARCHAR(20), DATEADD(MONTH, 3, GETDATE()), 120);

        EXEC [loyalty].[dbo].[usp_add_member_points] @decodedMemberId, @rewardPoints, @expiryDate, @description, @statusCode OUTPUT, @statusMessage OUTPUT;
    END
    ELSE
    BEGIN
        SET @couponCode = CASE @eventCategory
                               WHEN 2 THEN 'EVENT05'
                               WHEN 3 THEN 'EVENT10'
                               WHEN 4 THEN 'EVENT30'
                           END;

        EXEC [coupons].[dbo].[usp_issue_coupon] @sourceID, @couponCode, @decodedMemberId, @statusMessage OUTPUT;
    END;

    -- 이벤트 참여 기록 저장
    INSERT INTO [event_schema].[dbo].[EventParticipation2025] (memberId, eventCategory, registeredAt)
    VALUES (@decodedMemberId, @eventCategory, GETDATE());

    SET @statusCode = 1;
END;

입력 및 출력 파라미터

파라미터 타입 설명

@memberId	NVARCHAR(50)	암호화된 사용자 번호
@eventCategory	INT OUTPUT	당첨된 이벤트 유형
@statusCode	BIT OUTPUT	처리 결과 코드 (0: 실패, 1: 성공)
@statusMessage	NVARCHAR(200) OUTPUT	결과 메시지

주요 로직 흐름

1. 종료 여부 확인
   • 현재 날짜가 2025-02-14 00:00:00 이후라면 종료 메시지 반환 후 종료.
2. 참여 여부 확인 (현재 주석 처리됨)
   • 이미 참여한 사용자인지 확인하는 로직이 주석 처리되어 있음. (중복 참여 가능)
3. 타입 결정 (난수 기반)
   • RAND() 함수를 이용해 확률적으로 타입 결정:
     • 1번 (50%): 100P 지급
     • 2번 (30%): eSIM 쿠폰 5%
     • 3번 (15%): eSIM 쿠폰 10%
     • 4번 (5%): eSIM 쿠폰 30%
   • 특정 이벤트의 당첨 인원 제한이 초과되면 다시 추첨 (@retryCount 최대 10회까지)
4. 포인트 또는 쿠폰 지급
   • 포인트 지급 (1번):
     • usp_add_member_points 프로시저 호출.
   • 쿠폰 지급 (2~4번):
     • usp_issue_coupon 호출하여 쿠폰 발급.
5. 참여 기록 저장
   • [event_schema].[dbo].[EventParticipation2025] 테이블에 memberId, eventCategory, registeredAt 삽입.
6. 결과 코드 및 메시지 반환
   • 정상적으로 이벤트가 처리되면 '경품이 발급되었습니다.' 메시지 반환.
   • 오류 발생 시 '오류가 발생했습니다.' 메시지 반환.

타입별 총 카운트 조회

타입별 총 참여 횟수를 확인하는 쿼리는 다음과 같습니다.

SELECT eventCategory, COUNT(*) AS total_count
FROM [event_schema].[dbo].[EventParticipation2025]
GROUP BY eventCategory
ORDER BY eventCategory;

날짜별로 참여 횟수를 확인하려면 다음과 같이 조회할 수 있습니다.

SELECT CONVERT(DATE, registeredAt) AS event_date, eventCategory, COUNT(*) AS daily_count
FROM [event_schema].[dbo].[EventParticipation2025]
GROUP BY CONVERT(DATE, registeredAt), eventCategory
ORDER BY event_date DESC, eventCategory;

---

(변수 및 프로시저 이름은 임의로 변경 됨)
완벽한 독립시행이 아니라서 큰수의 법칙에 적용됨.

 

문제점을 적자면...

1. 재호출 시 값이 변함
   • RAND()는 동일한 쿼리 내에서 호출할 때마다 새로운 값을 반환.
   • WHILE 루프에서 RAND()를 여러 번 호출하면, 연속된 호출 값이 완전히 독립적이지 않음.
2. 완전한 독립성 부족
   • RAND()는 세션 기반이므로, 같은 세션에서 여러 번 호출하면 예측 가능한 패턴이 나올 수 있음.
   • 특정 분포를 따른다고 해도 완전한 균등 분포를 보장하지 않음.
3. 큰 수의 법칙 적용
   • RAND()를 충분히 많이 호출하면 각 확률이 원래 지정된 비율에 수렴하므로, 완전한 랜덤이 아님.

이로 인해 시행이 많아지면 많아질수록 문제 발생

 

🛠 해결 방법

1. NEWID() 기반의 난수 사용 (예측 불가능)

NEWID()를 활용하여 더 예측 불가능한 난수를 생성

SELECT CAST(CHECKSUM(NEWID()) & 0x7FFFFFFF AS FLOAT) / 0x7FFFFFFF

* NEWID()는 고유한 GUID 값을 생성하기 때문에, 기존 RAND()보다 더 무작위한 결과를 얻을 수 있음.

---

2. CRYPTOGRAPHIC 난수 활용 (CRYPT_GEN_RANDOM)

SQL Server에서 암호화 수준의 난수를 생성

SELECT CAST(CRYPT_GEN_RANDOM(4) AS INT) % 100

* CRYPT_GEN_RANDOM()은 RAND()보다 더 예측 불가능한 난수를 생성할 수 있음.