경품 추첨 DB 로직

경품 갯수가 n 개일 때,
경품 전체 데이타를 미리 만들어 놓고,
update 하는 방식으로 세팅할 수도 있지만..

여기에서는,
경품을 관리하는 경품테이블을 두고..
추첨이 일어날 때마다 이벤트테이블에
insert 하는 방식.

insert 하는 방식 보다는 update 할 때 동시성 문제가 발생할 수 있으므로,
데이타도 적게 적재되는 insert 방식으로 구현하는게 좋음.

 

insert into 이벤트테이블(                                          
       이벤트시퀀스
      ,이벤트카테고리                                              
      ,사용자아이디                                                   
      ,경품코드                                             
       )                                                         
select 이벤트시퀀스.nextval
      ,a.*                               
  from (select 이벤트카테고리                                    
              ,사용자아이디                                         
              ,(case                                             
                when today                  <= '20191030'             -- 특정 기간별로 당첨되는 경품이 다르게 세팅
                then (case                                            -- case 문은 중첩 가능
                      when to_char(sysdate,'hh24') < '10'             -- 10시 전에는 무조건 꽝
                        or e.cnt_min           >= 1                   -- 특정 경품이 당첨된 후 30분 이내에는 무조건 꽝
                        or b.cnt_big           >= 1                   -- 좋은 경품은 1인 1건만 당첨, 한 번만 당첨 
                        or c.cnt_mid           >= 2                   -- 중간 경품은 1인 2건만 당첨, 2회까지만 당첨
                        or d.cnt_total         >= d.경품별당첨자상한  -- 경품별 상한을 넘기면 무조건 꽝
                        or (a.당첨시킬경품코드 = 2                    -- 특정 경품별 당첨 추가 조건
                       and  a.yoil             = 6                    -- 6 금요일
                       and  d.cnt_today        >= 5)                
                        or (a.당첨시킬경품코드 = 2                  
                       and  a.yoil             <> 6                 
                       and  d.cnt_today        >= 3)                
                        or (a.당첨시킬경품코드 = 3                  
                       and  d.cnt_today        >= 1)                
                        or (a.당첨시킬경품코드 = 4                  
                       and  a.yoil             = 5                  
                       and  d.cnt_today        >= 1)                
                        or (a.당첨시킬경품코드 = 4                  
                       and  a.yoil             <> 5)                  -- 
                        or (a.당첨시킬경품코드 = 5                  
                       and  a.yoil             in (3,4)               -- 3 화, 4 수
                       and  d.cnt_today        >= 3)                  -- 하루 3건 넘으면 무조건 꽝, 3건은 가능
                        or (a.당첨시킬경품코드 = 5                  
                       and  a.yoil             not in (3,4)           -- 3 화, 4 수가 아닌 1일/2월/5목/6금/7토이면
                       and  d.cnt_today        >= 1)                  -- 하루 1건 넘으면 무조건 꽝. 1건은 가능
                        or (a.당첨시킬경품코드 = 6                  
                       and  to_char(sysdate                      
                             ,'yyyymmdd')      < '20191028')             -- 특정 경품은 특정기간에만 당첨
                      then 1                                          -- 상기 조건인 경우 무조건 꽝
                      else a.당첨시킬경품코드                         최초 발생했던 난수
                     end)                                        
                when today                  = '20191031'              -- 마지막 날에는 경품 떨이
                then (case                                       
                      when to_char('hh24') < '10'                     -- 10시 전에는 무조건 1(꽝)
                        or e.cnt_min       >= 1                 
                        or b.cnt_big       >= 1                 
                        or c.cnt_mid       >= 2                 
                        or d.cnt_total     >= d.경품별당첨자상한       
                      then 1                                     
                      else a.당첨시킬경품코드                                
                     end)                                        
                else 0 end) win                                  
          from (select trunc(dbms_random.value(2,7)) 당첨시킬경품코드 -- Oracle 난수. 2~6 사이의 정수. 경품 코드 범위
                      ,to_char(sysdate,'d') yoil                      -- 요일별로 경품 당첨 갯수를 달리하기 위해서 세팅
                      ,to_char(sysdate,'yyyymmdd') today              -- 기간별로 경품 당첨이 다르게 하려고 세팅
                  from dual_view                                 
               ) a                                               
              ,(select count(*) cnt_big                          
                  from 이벤트테이블                                
                 where 이벤트카테고리 = ?                         
                   and 사용자아이디   = ?                         
                   and 경품코드       >= 3                            -- 3부터 비싼 경품
               ) b                                               
              ,(select count(*) cnt_mid                        
                  from 이벤트테이블                                
                 where 이벤트카테고리 = ?                         
                   and 사용자아이디   = ?                         
                   and 경품코드       = 2                             -- 1 꽝, 2 중간. 상황에 맞게 조정 
               ) c                                               
              ,(select a.경품코드, a.경품별당첨자상한                   
                      ,nvl(b.cnt_total,0) cnt_total                   -- 경품별 전체 당첨자수
                      ,nvl(c.cnt_today,0) cnt_today                   -- 경품별 오늘 당첨자수, null 은 0 
                  from (select 경품코드, 경품별당첨자상한
                          from 경품테이블                           
                         where 경품카테고리 = ?                  
                       ) a                                       
                      ,(select 경품코드, count(*) cnt_total 
                          from 이벤트테이블                        
                         where 이벤트카테고리 = ?                  
                         group by 경품코드                  
                       ) b                                       
                      ,(select 경품코드, count(*) cnt_today           -- 경품별 오늘 당첨자수
                          from 이벤트테이블                        
                         where 이벤트카테고리 = ?                  
                           and 등록일시       > trunc(sysdate)     
                           and 등록일시       < trunc(sysdate)+1   
                         group by 경품코드                  
                       ) c                                       
                 where a.경품코드 = b.경품코드(+)           
                   and a.경품코드 = c.경품코드(+)           
               ) d                                               
              ,(select count(*) cnt_min                               -- 30분 이내 특정경품 건수 
                  from 이벤트테이블                                
                 where 이벤트카테고리 = ?                         
                   and 등록일시 > sysdate -1/24/2                     -- 1/24/2 30분 이내
                   and 경품코드 >= 2                        
               ) e                                               
         where d.경품코드 = a.당첨시킬경품코드                               
       ) a                                                       
 where not exists                                                     -- 이정도 세팅해도 동시성 문제는 발생하지 않음
       (select 1 from 이벤트테이블
         where 이벤트카테고리     = ?                                 -- 카테고리가 여럿일 때
           and 사용자아이디 = ?                                  
           and 등록일시     > trunc(sysdate)                     
           and 등록일시     < trunc(sysdate)+1                   
           and rownum       = 1                                       -- 1인 1일 1회
       )                                                         

;

 

trunc(dbms_random.value(2,7)) 에서
dbms_random.value(2,7) 을 하면, 2.xxxxx ~ 6.xxxxx 사이의 수치를 뽑아 오기 때문에..
정수로 바꾸기 위해 소수 이하를 trunc() 로 버림함.
이렇게 하면 2 ~ 6 까지의 정수가 나옴.
따라서, 2 ~ 6 까지의 수치를 뽑으려면 dbms_random.value() 의 파라미터는 2 와 7 이어야 함.