출현비율에 따른 로또번호 생성

[원쿼리맨]

숫자 n을 r의 비율로 랜덤하게 생성하는 아래 페이지의 쿼리를 이용하여,

http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=5407#5486

로또번호를 출현비율에 따라 생성해보자.



쿼리는 아래와 같다.





with lotto as (
select ...
...
...
), t as (
select no n
      , count(*) r
   from
     (
     select r
          , lv
          , decode(lv,1,no1,2,no2,3,no3,4,no4,5,no5,6,no6) no
       from lotto
      cross join
            (select level lv
               from dual
            connect by level<=6) tab6
     )
group by no
)
select a.n
     , count(*)
  from (select n
             , r
             , nvl(sum(r) over (order by rownum rows between unbounded preceding and 1 preceding),0) s
             , sum(r) over (order by rownum)-1 e
          from t) a
  left join  
       (select trunc(dbms_random.value(0,mx)) v
          from (select sum(r) mx from t)
       connect by level<=mx*100
       ) b
    on b.v between a.s and a.e
group by a.n
order by 2 desc



         N   COUNT(*)
---------- ----------
        40       9389
        20       9163
        37       8933
        34       8862
        27       8618
         1       8485
        17       8131
        14       8044
         4       7910
        26       7883
...
...





마지막 쿼리로 아래 쿼리를 사용하면,
실제 로또번호가 생성된다.
(중복으로 번호가 출현하는 경우는 무시한다.)


select n, r
  from (select n
             , r
             , nvl(sum(r) over (order by rownum rows between unbounded preceding and 1 preceding),0) s
             , sum(r) over (order by rownum)-1 e
          from t) a
  join  
       (select trunc(dbms_random.value(0,mx)) v
          from (select sum(r) mx from t)
       connect by level<=6
       ) b
    on b.v between a.s and a.e
order by n




         N          R
---------- ----------
         7         68
        15         71
        17         80
        22         68
        29         73
        36         76

(N이 로또번호)





혹은 아래 쿼리를 사용하여,
출현 빈도가 높은 번호 6개만을 사용한다.


select n, count(*)
from (
select n, r
  from (select n
             , r
             , nvl(sum(r) over (order by rownum rows between unbounded preceding and 1 preceding),0) s
             , sum(r) over (order by rownum)-1 e
          from t) a
  join  
       (select trunc(dbms_random.value(0,mx)) v
          from (select sum(r) mx from t)
       connect by level<=1000
       ) b
    on b.v between a.s and a.e
order by n
)
group by n
order by count(*) desc




         N   COUNT(*)
---------- ----------
        27         34
         1         32
        18         31
         8         30
        26         28
        39         28
...
...







이렇게 해서 생성된 번호를 사용할 것인가?