[더비's 퀴즈] SQL로 로또번호 생성하기

5개의 댓글이 등록되었습니다.

1. 강정식 2014년 10월 16일 17:42

   더비 첫 퀴즈게시물에 재미난 문제를 내준 김지룡 대리님께 감사드립니다.
   이 문제를 통해 나온 로또번호로 이번주에 당첨되면 좋겠네요 ^^

   답은 2가지 버전으로 접근해 보았으며, 참고로 PIVOT 키워드를 사용하여 11g 이상 수행이 가능합니다.

   1. UNION ALL 활용 버전
   - UNION ALL 키워드를 사용하여 수행했는데 단점은 게임횟수를 상수로 넣어야 해서 프로그래밍적으로는 문제가 있는 버전입니다.

   Oracle PL/SQL

   WITH TAB AS ( SELECT 'A' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'B' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'C' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'D' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'E' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 ) SELECT GUBN_1 "자동", NUM_1 "번호1", NUM_2 "번호2", NUM_3 "번호3", NUM_4 "번호4", NUM_5 "번호5", NUM_6 "번호6" FROM TAB PIVOT (MIN(CNT) FOR GUBN_2 IN (1 NUM_1, 2 NUM_2, 3 NUM_3, 4 NUM_4, 5 NUM_5, 6 NUM_6)) ORDER BY 1 ; 자동 번호1 번호2 번호3 번호4 번호5 번호6 ---- ---------- ---------- ---------- ---------- ---------- ---------- A 31 36 26 42 23 37 B 9 41 32 5 12 19 C 38 40 28 13 24 21 D 35 3 15 18 19 34 E 22 36 32 8 7 43 ;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   WITH TAB AS ( SELECT 'A' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER  BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'B' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER  BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'C' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER  BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'D' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER  BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6 UNION ALL SELECT 'E' GUBN_1, ROWNUM GUBN_2, CNT FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 45 ORDER  BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 6   ) SELECT GUBN_1 "자동",        NUM_1  "번호1",        NUM_2  "번호2",        NUM_3  "번호3",        NUM_4  "번호4",        NUM_5  "번호5",        NUM_6  "번호6" FROM   TAB PIVOT  (MIN(CNT) FOR GUBN_2 IN (1 NUM_1,                                 2 NUM_2,                                 3 NUM_3,                                 4 NUM_4,                                 5 NUM_5,                                 6 NUM_6)) ORDER BY 1 ;   자동      번호1      번호2      번호3      번호4      번호5      번호6 ---- ---------- ---------- ---------- ---------- ---------- ---------- A            31         36         26         42         23         37 B             9         41         32          5         12         19 C            38         40         28         13         24         21 D            35          3         15         18         19         34 E            22         36         32          8          7         43 ;

   2. 게임횟수 도입
   - 게임횟수를 도입하여 Input 값에 따라 로또게임 횟수만큼 출력되도록 하였으며 총 12회를 진행해 보았습니다.

   Oracle PL/SQL

   WITH GAME AS ( SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 12 -- 게임횟수(Input 값) ), LOTTO AS ( SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL <= 45 -- 로또번호 ) SELECT CNT "자동", NUM_1 "번호_1", NUM_2 "번호_2", NUM_3 "번호_3", NUM_4 "번호_4", NUM_5 "번호_5", NUM_6 "번호_6" FROM (SELECT CHR(64 + CNT) CNT, NUM, GUBN FROM (SELECT CNT, NUM, MOD(ROWNUM - 1, 45) + 1 GUBN FROM (SELECT A.CNT, B.NUM FROM GAME A, LOTTO B ORDER BY A.CNT, DBMS_RANDOM.VALUE ) ) WHERE GUBN <= 6 ) PIVOT (MIN(NUM) FOR GUBN IN (1 NUM_1, 2 NUM_2, 3 NUM_3, 4 NUM_4, 5 NUM_5, 6 NUM_6)) ORDER BY 1 ; 자동 번호_1 번호_2 번호_3 번호_4 번호_5 번호_6 ---- ---------- ---------- ---------- ---------- ---------- ---------- A 44 23 1 31 10 27 B 3 11 25 36 34 6 C 37 32 5 14 2 26 D 36 18 25 22 44 43 E 44 20 6 45 8 41 F 37 31 30 15 2 27 G 31 28 18 25 2 35 H 10 24 18 27 11 4 I 26 29 41 31 14 3 J 11 13 9 43 28 26 K 19 25 27 38 24 8 L 44 1 30 10 28 40 12 rows selected ;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

   WITH GAME AS (     SELECT LEVEL CNT     FROM   DUAL     CONNECT BY LEVEL <= 12 -- 게임횟수(Input 값) ), LOTTO AS (     SELECT LEVEL NUM     FROM   DUAL     CONNECT BY LEVEL <= 45 -- 로또번호 ) SELECT CNT   "자동",        NUM_1 "번호_1",        NUM_2 "번호_2",        NUM_3 "번호_3",        NUM_4 "번호_4",        NUM_5 "번호_5",        NUM_6 "번호_6" FROM   (SELECT CHR(64 + CNT) CNT,                NUM,                GUBN         FROM   (SELECT CNT,                        NUM,                        MOD(ROWNUM - 1, 45) + 1 GUBN                 FROM   (SELECT A.CNT,                                B.NUM                         FROM   GAME  A,                                LOTTO B                         ORDER BY A.CNT,                                  DBMS_RANDOM.VALUE                         )                 )         WHERE  GUBN <= 6         ) PIVOT  (MIN(NUM) FOR GUBN IN (1 NUM_1,                               2 NUM_2,                               3 NUM_3,                               4 NUM_4,                               5 NUM_5,                               6 NUM_6)) ORDER BY 1 ;   자동     번호_1     번호_2     번호_3     번호_4     번호_5     번호_6 ---- ---------- ---------- ---------- ---------- ---------- ---------- A            44         23          1         31         10         27 B             3         11         25         36         34          6 C            37         32          5         14          2         26 D            36         18         25         22         44         43 E            44         20          6         45          8         41 F            37         31         30         15          2         27 G            31         28         18         25          2         35 H            10         24         18         27         11          4 I            26         29         41         31         14          3 J            11         13          9         43         28         26 K            19         25         27         38         24          8 L            44          1         30         10         28         40   12 rows selected ;

   일단 원하는 결과는 나왔는데 다른분들이 접근하는 방법도 기대가 됩니다.

   앞으로도 재마난 퀴즈 많이 제공해 주시기 바랍니다.

   감사합니다.

   응답
2. 김수영 2014년 10월 24일 15:07

   Oracle PL/SQL

   SELECT a.dep AS "게임"       ,max(decode(a.NUM, 1, a.rnum)) "추첨번호1"       ,max(decode(a.NUM, 2, a.rnum)) "추첨번호2"       ,max(decode(a.NUM, 3, a.rnum)) "추첨번호3"       ,max(decode(a.NUM, 4, a.rnum)) "추첨번호4"       ,max(decode(a.NUM, 5, a.rnum)) "추첨번호5"       ,max(decode(a.NUM, 6, a.rnum)) "보너스번호" FROM  (SELECT dep              ,NUM              ,RANK() OVER (PARTITION BY dep ORDER BY DBMS_RANDOM.VALUE) RNUM        FROM  (SELECT ceil(LEVEL / 45) dep                     ,MOD(LEVEL - 1, 45) + 1 NUM               FROM   dual               CONNECT BY LEVEL <= 45 * 5               ORDER  BY dep, DBMS_RANDOM.VALUE)       ) a GROUP BY a.dep ORDER BY a.dep ;       게임  추첨번호1  추첨번호2  추첨번호3  추첨번호4  추첨번호5 보너스번호 ---------- ---------- ---------- ---------- ---------- ---------- ----------          1         26         37         12         10          9         36          2         29         45         44          2         34          7          3         25         30         45         11         35         23          4          7         23         14          4         28         19          5         20          3         24         17         41          6

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   SELECT a.dep AS "게임"       ,max(decode(a.NUM, 1, a.rnum)) "추첨번호1"       ,max(decode(a.NUM, 2, a.rnum)) "추첨번호2"       ,max(decode(a.NUM, 3, a.rnum)) "추첨번호3"       ,max(decode(a.NUM, 4, a.rnum)) "추첨번호4"       ,max(decode(a.NUM, 5, a.rnum)) "추첨번호5"       ,max(decode(a.NUM, 6, a.rnum)) "보너스번호" FROM  (SELECT dep              ,NUM              ,RANK() OVER (PARTITION BY dep ORDER BY DBMS_RANDOM.VALUE) RNUM        FROM  (SELECT ceil(LEVEL / 45) dep                     ,MOD(LEVEL - 1, 45) + 1 NUM               FROM   dual               CONNECT BY LEVEL <= 45 * 5               ORDER  BY dep, DBMS_RANDOM.VALUE)       ) a GROUP BY a.dep ORDER BY a.dep ;         게임   추첨번호1   추첨번호2   추첨번호3   추첨번호4   추첨번호5   보너스번호 ---------- ---------- ---------- ---------- ---------- ---------- ----------          1         26         37         12         10          9         36          2         29         45         44          2         34          7          3         25         30         45         11         35         23          4          7         23         14          4         28         19          5         20          3         24         17         41          6

   응답
3. 하승환 2014년 10월 24일 17:44

   * 지룡대리가 준 Sample 데이타를 보면 5게임으로 한정 되어 있지만.. 저는 10게임으로 했습니다^^

   1. 로또 번호를 생성 하기 위해 필요한 데이타를 생성 합니다.

   Oracle PL/SQL

   SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN -- 게임수 ,MOD(LEVEL - 1 , 45) + 1 NUM -- 로또번호 FROM dual CONNECT BY LEVEL <= 45 * 10 ORDER BY 1, DBMS_RANDOM.VALUE ; GN NUM ---- ----- 1 33 1 39 1 27 1 2 1 24 1 16 1 20 1 23 1 36 1 34 1 36 1 11 1 29 1 2 1 6 1 11 1 22 1 22 1 11 1 1 1 41 1 9 1 28 1 41 1 16 1 39 1 17 1 25 1 28 1 44 1 33 1 43 1 9 1 13 1 14 1 34 1 3 1 6 1 33 1 43 1 30 1 19 1 8 1 3 1 36 2 11 2 4 2 33 2 17 2 14 2 13 2 14 2 38 2 18 2 34 2 22 2 9 2 24 2 30 . . . . . . 450 Rows Selected.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

   SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN -- 게임수       ,MOD(LEVEL - 1 , 45) + 1 NUM                                                  -- 로또번호 FROM   dual CONNECT BY LEVEL <= 45 * 10 ORDER BY 1, DBMS_RANDOM.VALUE ;   GN      NUM ----    -----    1         33    1         39    1         27    1          2    1         24    1         16    1         20    1         23    1         36    1         34    1         36    1         11    1         29    1          2    1          6    1         11    1         22    1         22    1         11    1          1    1         41    1          9    1         28    1         41    1         16    1         39    1         17    1         25    1         28    1         44    1         33    1         43    1          9    1         13    1         14    1         34    1          3    1          6    1         33    1         43    1         30    1         19    1          8    1          3    1         36    2         11    2          4    2         33    2         17    2         14    2         13    2         14    2         38    2         18    2         34    2         22    2          9    2         24    2         30    .        .    .        .    .        . 450 Rows Selected.

   2. 열로 구성되어 있는 데이터를 행으로 만들기 위한 기준 데이터 생성

   Oracle PL/SQL

   SELECT GN ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT -- 랜덤으로 정렬한것을 게임번호별(1~10)로 그룹을 만든것을 45개의 그룹으로 생성!! ,NUM FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN ,MOD(LEVEL - 1 , 45) + 1 NUM FROM dual CONNECT BY LEVEL <= 45 * 10 ORDER BY 1, DBMS_RANDOM.VALUE) ; GN HEIGHT NUM ---- ------- ----- 1 1 17 1 2 43 1 3 6 1 4 45 1 5 30 1 6 4 1 7 42 1 8 30 1 9 17 1 10 20 1 11 25 1 12 32 1 13 28 1 14 45 1 15 44 1 16 10 1 17 27 1 18 30 1 19 32 1 20 21 1 21 5 1 22 14 1 23 37 1 24 17 1 25 28 1 26 37 1 27 45 1 28 8 1 29 34 1 30 11 1 31 36 1 32 12 1 33 14 1 34 22 1 35 26 1 36 15 1 37 19 1 38 41 1 39 43 1 40 6 1 41 4 1 42 7 1 43 25 1 44 33 1 45 32 2 1 19 2 2 1 . . . . . . . . . 450 Rows Selected.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

   SELECT GN       ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT  -- 랜덤으로 정렬한것을 게임번호별(1~10)로 그룹을 만든것을 45개의 그룹으로 생성!!       ,NUM FROM   (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN               ,MOD(LEVEL - 1 , 45) + 1 NUM         FROM   dual         CONNECT BY LEVEL <= 45 * 10         ORDER BY 1, DBMS_RANDOM.VALUE) ;   GN     HEIGHT     NUM ----   -------   -----    1           1        17    1           2        43    1           3         6    1           4        45    1           5        30    1           6         4    1           7        42    1           8        30    1           9        17    1          10        20    1          11        25    1          12        32    1          13        28    1          14        45    1          15        44    1          16        10    1          17        27    1          18        30    1          19        32    1          20        21    1          21         5    1          22        14    1          23        37    1          24        17    1          25        28    1          26        37    1          27        45    1          28         8    1          29        34    1          30        11    1          31        36    1          32        12    1          33        14    1          34        22    1          35        26    1          36        15    1          37        19    1          38        41    1          39        43    1          40         6    1          41         4    1          42         7    1          43        25    1          44        33    1          45        32    2           1        19    2           2         1        .         .      .    .         .      .    .         .      . 450 Rows Selected.

   3. 열로 되어 있는 것을 행으로 데이터 변환.(CASE함수를 써도 무방함.)

   Oracle PL/SQL

   SELECT GN AS "GAME" ,DECODE(HEIGHT, 1, NUM) AS "No1" ,DECODE(HEIGHT, 2, NUM) AS "No2" ,DECODE(HEIGHT, 3, NUM) AS "No3" ,DECODE(HEIGHT, 4, NUM) AS "No4" ,DECODE(HEIGHT, 5, NUM) AS "No5" ,DECODE(HEIGHT, 6, NUM) AS "Bouns" FROM (SELECT GN ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT ,NUM FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN ,MOD(LEVEL - 1 , 45) + 1 NUM FROM dual CONNECT BY LEVEL <= 45 * 10 ORDER BY 1, DBMS_RANDOM.VALUE ) ) ; GAME No1 No2 No3 No4 No5 Bouns ---- --- --- --- --- --- ----- 1 21 1 18 1 43 1 7 1 36 1 14 ........................ ........................ ........................ ........................ ........................ ........................ ........................ 450 Rows Selected.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

   SELECT GN AS "GAME"       ,DECODE(HEIGHT, 1, NUM) AS "No1"       ,DECODE(HEIGHT, 2, NUM) AS "No2"       ,DECODE(HEIGHT, 3, NUM) AS "No3"       ,DECODE(HEIGHT, 4, NUM) AS "No4"       ,DECODE(HEIGHT, 5, NUM) AS "No5"       ,DECODE(HEIGHT, 6, NUM) AS "Bouns" FROM   (SELECT GN               ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT               ,NUM         FROM   (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN                       ,MOD(LEVEL - 1 , 45) + 1 NUM                 FROM   dual                 CONNECT BY LEVEL <= 45 * 10                 ORDER BY 1, DBMS_RANDOM.VALUE                 )         ) ;   GAME  No1  No2  No3  No4  No5  Bouns ----  ---  ---  ---  ---  ---  -----    1    21                        1            18                    1                   43                1                            7            1                                   36        1                                             14    ........................    ........................    ........................    ........................    ........................    ........................    ........................ 450 Rows Selected.

   위 결과를 보면 우리가 원하는 결과 값으로 나오지 않고 NULL 값과 함께 나오게 됩니다.
   이유는 오라클은 행 단위로 데이터를 처리 하기 때문 인데요.. DELCODE 함수를 통해 행으로 변형 할때..
   DECODE(HEIGHT, 1, NUM) AS “No1″ < -- 해당 조건에서 HEIGHT = 1 조건에 만족하는 값 리턴, 조건에 만족하지 않는 값은 NULL 값으로 치환을 합니다. 만일 DECODE(HEIGHT, 1, NUM, 0) AS "No1" 로 쓰게 되면.. NULL값대신.. 0라는 숫자가 찍힐 것 입니다. 결론적으로 위 결과는 우리가 원하는 결과의 값이 아닙니다. 그래서 이를 해결하기 위해.. 그룹함수를 이용하여.. 해결 할 수 있습니다. 그룹함수의 특징 중 하나가 NULL값을 취급하지 않습니다. GAME컬럼을 기준으로 No1 컬럼을 그룹을 묶게 되면 NULL값을 제외한 나머지 값.. 즉, 값만 리턴하게 될 것입니다. 아래 결과를 확인 합시다.. 4. 최종 결과....

   Oracle PL/SQL

   SELECT GN AS "GAME" ,MAX(DECODE(HEIGHT, 1, NUM)) AS "No1" ,MAX(DECODE(HEIGHT, 2, NUM)) AS "No2" ,MAX(DECODE(HEIGHT, 3, NUM)) AS "No3" ,MAX(DECODE(HEIGHT, 4, NUM)) AS "No4" ,MAX(DECODE(HEIGHT, 5, NUM)) AS "No5" ,MAX(DECODE(HEIGHT, 6, NUM)) AS "Bouns" FROM (SELECT GN ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT ,NUM FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN ,MOD(LEVEL - 1 , 45) + 1 NUM FROM dual CONNECT BY LEVEL <= 45 * 10 ORDER BY 1, DBMS_RANDOM.VALUE ) ) GROUP BY GN ; GAME No1 No2 No3 No4 No5 Bouns ---- --- --- --- --- --- ----- 1 24 1 22 10 13 20 2 33 25 37 18 36 15 3 1 28 29 30 36 2 4 35 23 7 11 22 6 5 7 24 13 44 37 12 6 38 9 16 37 33 25 7 12 20 10 42 14 36 8 35 33 40 21 37 14 9 2 27 29 26 5 16 10 19 31 15 18 21 23

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   SELECT GN AS "GAME"       ,MAX(DECODE(HEIGHT, 1, NUM)) AS "No1"       ,MAX(DECODE(HEIGHT, 2, NUM)) AS "No2"       ,MAX(DECODE(HEIGHT, 3, NUM)) AS "No3"       ,MAX(DECODE(HEIGHT, 4, NUM)) AS "No4"       ,MAX(DECODE(HEIGHT, 5, NUM)) AS "No5"       ,MAX(DECODE(HEIGHT, 6, NUM)) AS "Bouns" FROM   (SELECT GN               ,NTILE(45) OVER(PARTITION BY GN ORDER BY DBMS_RANDOM.VALUE) HEIGHT               ,NUM         FROM   (SELECT ROW_NUMBER() OVER(PARTITION BY CEIL(LEVEL/10) ORDER BY DBMS_RANDOM.VALUE) GN                       ,MOD(LEVEL - 1 , 45) + 1 NUM                 FROM   dual                 CONNECT BY LEVEL <= 45 * 10                 ORDER BY 1, DBMS_RANDOM.VALUE                 )         ) GROUP BY GN ;            GAME  No1  No2  No3  No4  No5  Bouns ----  ---  ---  ---  ---  ---  -----    1     24       1     22      10     13        20    2     33      25     37      18     36        15    3      1      28     29      30     36         2    4     35      23      7      11     22         6    5      7      24     13      44     37        12    6     38       9     16      37     33        25    7     12      20     10      42     14        36    8     35      33     40      21     37        14    9      2      27     29      26      5        16   10     19      31     15      18     21        23

   GAME이라는 값을 그룹핑하여 그룹함수를 써줌으로써 NULL값은 해결되고.. 우리가 원하는 결과가 나오게 됩니다. 감사합니다.

   응답
4. 김민호 2014년 10월 26일 22:23

   집에 Oracle 10g가 깔려있어서.. 10g기준으로 작성하였습니다 ^^;

   (답글을 늦게 올려 죄송합니다..)

   Oracle PL/SQL

   SELECT A.CNT AS "횟수",        MAX(DECODE(A.NUM, 1, A.RNUM)) "NO.1",        MAX(DECODE(A.NUM, 2, A.RNUM)) "NO.2",        MAX(DECODE(A.NUM, 3, A.RNUM)) "NO.3",        MAX(DECODE(A.NUM, 4, A.RNUM)) "NO.4",        MAX(DECODE(A.NUM, 5, A.RNUM)) "NO.5",        MAX(DECODE(A.NUM, 6, A.RNUM)) "BONUS"   FROM (SELECT CNT,                NUM,                RANK() OVER(PARTITION BY CNT ORDER BY DBMS_RANDOM.VALUE) RNUM           FROM (SELECT CEIL(LEVEL / 45) CNT, MOD(LEVEL - 1, 45) + 1 NUM                   FROM DUAL                 CONNECT BY LEVEL <= 45 * 10                  ORDER BY CNT, DBMS_RANDOM.VALUE)) A  GROUP BY A.CNT  ORDER BY A.CNT;       횟수       NO.1       NO.2       NO.3       NO.4       NO.5      BONUS ---------- ---------- ---------- ---------- ---------- ---------- ----------          1          3         43          1         34         44         17          2         17          7         27          1         21         10          3         40         26         43         18         36         41          4         25         33         29          3         45         10          5         20         43          5         23         45         39          6          7         25         32          4         21         15          7         11         42         20          3          9         21          8         25         16         40         29         33         14          9         13          4         14         38         20         18         10         23          2         32          3         22         36 10 개의 행이 선택되었습니다.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

   SELECT A.CNT AS "횟수",        MAX(DECODE(A.NUM, 1, A.RNUM)) "NO.1",        MAX(DECODE(A.NUM, 2, A.RNUM)) "NO.2",        MAX(DECODE(A.NUM, 3, A.RNUM)) "NO.3",        MAX(DECODE(A.NUM, 4, A.RNUM)) "NO.4",        MAX(DECODE(A.NUM, 5, A.RNUM)) "NO.5",        MAX(DECODE(A.NUM, 6, A.RNUM)) "BONUS"   FROM (SELECT CNT,                NUM,                RANK() OVER(PARTITION BY CNT ORDER BY DBMS_RANDOM.VALUE) RNUM           FROM (SELECT CEIL(LEVEL / 45) CNT, MOD(LEVEL - 1, 45) + 1 NUM                   FROM DUAL                 CONNECT BY LEVEL <= 45 * 10                  ORDER BY CNT, DBMS_RANDOM.VALUE)) A  GROUP BY A.CNT  ORDER BY A.CNT;         횟수       NO.1       NO.2       NO.3       NO.4       NO.5      BONUS ---------- ---------- ---------- ---------- ---------- ---------- ----------          1          3         43          1         34         44         17          2         17          7         27          1         21         10          3         40         26         43         18         36         41          4         25         33         29          3         45         10          5         20         43          5         23         45         39          6          7         25         32          4         21         15          7         11         42         20          3          9         21          8         25         16         40         29         33         14          9         13          4         14         38         20         18         10         23          2         32          3         22         36   10 개의 행이 선택되었습니다.

   응답
5. 김지룡 2014년 10월 27일 08:59

   -로또 번호 생성 퀴즈 풀이 및 정답

   Oracle PL/SQL

   --풀이 --1단계 SELECT (LEVEL / 45) dep, LEVEL num FROM DUAL CONNECT BY LEVEL <= 45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE; DEP NUM ---------- ---------- .022222222 1 .044444444 2 .066666667 3 .088888889 4 .111111111 5 .133333333 6 .155555556 7 .177777778 8 .2 9 .222222222 10 ....... 위의 보기 형태를 당장 수행할수 있는 형태로 변경해서 수행했다면 이와 같은 형태의 값을 보실 수 있을 것 입니다. Dep는 게임 수를 나타내게 되고 Num은 로또 번호가 되겠지요? 이때, 결과는 LEVEL <= 45 * 5 라는 로직으로 인해서 DEP는 5이하의 값들이, NUM은 225까지의 값들이 나오게 됩니다. 우리는 여기서 DEP는 1,2,3,4,5 게임수가 필요하고 NUM은 MAX로 45까지의 숫자가 필요하니 DEP에는 CEIL 함수가 NUM에는 45로 다시 나눌 수 있는 MOD가 필요하게 됩니다. --2단계 SELECT CEIL(LEVEL / 45) dep, MOD(LEVEL - 1, 45) + 1 num FROM DUAL CONNECT BY LEVEL <= 45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE; DEP NUM ---------- ---------- 1 26 1 17 1 16 1 21 1 43 1 44 1 45 1 41 1 5 1 10 1 14 1 11 ........ 짜잔! 여기까지 오셨다면 위의 결과를 보셨겠지요? 그런데 아직 많이 결과라고 보기엔 많이 모자란 모습입니다. 1에 45까지 Random하게 Sort되어 각 DEP값별로 나열이 되었기에 이걸가지고 로또를 하기는 힘들지요. 이제 다음 단계로 가기위해 필요한건 어떠한 정보 일까요? --3단계 SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum FROM (SELECT CEIL(LEVEL/45) dep, MOD(LEVEL - 1 , 45) + 1 num FROM DUAL CONNECT BY LEVEL <= 45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE); DEP NUM RNUM ---------- ---------- ---------- 1 26 1 1 4 2 1 13 3 1 42 4 1 34 5 1 44 6 1 35 7 1 5 8 1 21 9 1 30 10 ...... 저는 여기서 rnum이라는 값을 새로 만들어 각 DEP별 1~45까지의 정열된 값을 가진 Colum을 새롭게 만들었습니다. 이 정보가 있어야만 1게임에 6개의 번호를 선택해야하는 로또게임의 룰을 충족할 수 있는 데이터를 만들어 낼 수 있기 때문이죠. 아래와 같이 말입니다. SELECT dep,num FROM (SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum FROM (SELECT CEIL(LEVEL/45) dep, MOD(LEVEL - 1 , 45) + 1 num FROM DUAL CONNECT BY LEVEL <= 45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE)) WHERE rnum <= 6 ORDER BY dep,num; SELECT MIN(DECODE(MOD(ROWNUM -1 ,6)+1,1,num))no1, MIN(DECODE(MOD(ROWNUM -1 ,6)+1,2,num))no2, MIN(DECODE(MOD(ROWNUM -1 ,6)+1,3,num))no3, MIN(DECODE(MOD(ROWNUM -1 ,6)+1,4,num))no4, MIN(DECODE(MOD(ROWNUM -1 ,6)+1,5,num))no5, MIN(DECODE(MOD(ROWNUM -1 ,6)+1,6,num))no6 FROM (SELECT dep,num FROM (SELECT dep,num,MOD(ROWNUM - 1 ,45) +1 rnum FROM (SELECT CEIL(LEVEL/45) dep, MOD(LEVEL - 1 ,45) + 1 num FROM DUAL CONNECT BY LEVEL <=45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE)) WHERE rnum <= 6 ORDER BY dep,num) GROUP BY dep; DEP NUM ---------- ---------- 1 3 1 12 1 25 1 29 1 40 1 42 ........ --4단계 3단계까지 왔으면 DEP(게임횟수)별 6개의 번호를 얻을수는 있지만 최종 형태가 아직 불안정 합니다. 이제 로또 형태로 게임별 번호를 가로로 나열을 해야겠지요? --결과 SELECT MIN(DECODE (MOD(ROWNUM -1,6)+1 ,1,num))no1, MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 2,num)) no2, MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 3,num)) no3, MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 4,num)) no4, MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 5,num)) no5, MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 6,num)) no6 FROM (SELECT dep,num FROM (SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum FROM (SELECT CEIL(LEVEL/45) dep, MOD(LEVEL - 1 , 45) + 1 num FROM DUAL CONNECT BY LEVEL <= 45 * 5 ORDER BY 1, DBMS_RANDOM.VALUE)) WHERE rnum <= 6 ORDER BY dep,num) GROUP BY dep; NO1 NO2 NO3 NO4 NO5 NO6 ---------- ---------- ---------- ---------- ---------- ---------- 2 11 22 25 32 39 10 15 18 35 39 43 5 15 22 25 27 40 3 9 22 24 25 40 13 29 36 40 42 45 가로로 나열하기 위한 기준값과 MOD(ROWNUM-1,6)+1 값과 DECODE문을 사용하고, 하나의 Row로 결과를 도출하기 위하여 DEP기준으로 Group BY하였습니다. 이제 로또 해도 되겠죠? ㅎ

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

   --풀이 --1단계 SELECT (LEVEL / 45) dep,         LEVEL num FROM DUAL CONNECT BY LEVEL <= 45 * 5   ORDER BY 1, DBMS_RANDOM.VALUE;          DEP        NUM ---------- ---------- .022222222          1 .044444444          2 .066666667          3 .088888889          4 .111111111          5 .133333333          6 .155555556          7 .177777778          8         .2          9 .222222222         10 ....... 위의 보기 형태를 당장 수행할수 있는 형태로 변경해서 수행했다면 이와 같은 형태의 값을 보실 수 있을 것 입니다.   Dep는 게임 수를 나타내게 되고 Num은 로또 번호가 되겠지요? 이때, 결과는 LEVEL <= 45 * 5 라는 로직으로 인해서 DEP는 5이하의 값들이, NUM은 225까지의 값들이 나오게 됩니다. 우리는 여기서 DEP는 1,2,3,4,5 게임수가 필요하고 NUM은 MAX로 45까지의 숫자가 필요하니 DEP에는 CEIL 함수가 NUM에는 45로 다시 나눌 수 있는 MOD가 필요하게 됩니다.   --2단계 SELECT CEIL(LEVEL / 45) dep,         MOD(LEVEL - 1, 45) + 1 num FROM DUAL CONNECT BY LEVEL <= 45 * 5   ORDER BY 1, DBMS_RANDOM.VALUE;          DEP        NUM ---------- ----------          1         26          1         17          1         16          1         21          1         43          1         44          1         45          1         41          1          5          1         10          1         14          1         11 ........   짜잔! 여기까지 오셨다면 위의 결과를 보셨겠지요? 그런데 아직 많이 결과라고 보기엔 많이 모자란 모습입니다. 1에 45까지 Random하게 Sort되어 각 DEP값별로 나열이 되었기에 이걸가지고 로또를 하기는 힘들지요. 이제 다음 단계로 가기위해 필요한건 어떠한 정보 일까요?   --3단계 SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum                  FROM (SELECT CEIL(LEVEL/45) dep,                               MOD(LEVEL - 1 , 45) + 1 num                          FROM DUAL                        CONNECT BY LEVEL <= 45 * 5                          ORDER BY 1, DBMS_RANDOM.VALUE);          DEP        NUM       RNUM ---------- ---------- ----------          1         26          1          1          4          2          1         13          3          1         42          4          1         34          5          1         44          6          1         35          7          1          5          8          1         21          9          1         30         10 ......   저는 여기서 rnum이라는 값을 새로 만들어 각 DEP별 1~45까지의 정열된 값을 가진 Colum을 새롭게 만들었습니다. 이 정보가 있어야만 1게임에 6개의 번호를 선택해야하는 로또게임의 룰을 충족할 수 있는 데이터를 만들어 낼 수 있기 때문이죠. 아래와 같이 말입니다.   SELECT dep,num          FROM (SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum                  FROM (SELECT CEIL(LEVEL/45) dep,                               MOD(LEVEL - 1 , 45) + 1 num                          FROM DUAL                        CONNECT BY LEVEL <= 45 * 5                          ORDER BY 1, DBMS_RANDOM.VALUE))        WHERE rnum <= 6        ORDER BY dep,num;     SELECT MIN(DECODE(MOD(ROWNUM -1 ,6)+1,1,num))no1,        MIN(DECODE(MOD(ROWNUM -1 ,6)+1,2,num))no2,        MIN(DECODE(MOD(ROWNUM -1 ,6)+1,3,num))no3,        MIN(DECODE(MOD(ROWNUM -1 ,6)+1,4,num))no4,        MIN(DECODE(MOD(ROWNUM -1 ,6)+1,5,num))no5,        MIN(DECODE(MOD(ROWNUM -1 ,6)+1,6,num))no6 FROM   (SELECT dep,num           FROM (SELECT dep,num,MOD(ROWNUM - 1 ,45) +1 rnum                   FROM (SELECT CEIL(LEVEL/45) dep,                                 MOD(LEVEL - 1 ,45) + 1 num                            FROM DUAL                         CONNECT BY LEVEL <=45 * 5                           ORDER BY 1, DBMS_RANDOM.VALUE))             WHERE rnum <= 6             ORDER BY dep,num) GROUP BY dep;          DEP        NUM ---------- ----------          1          3          1         12          1         25          1         29          1         40          1         42 ........   --4단계 3단계까지 왔으면 DEP(게임횟수)별 6개의 번호를 얻을수는 있지만 최종 형태가 아직 불안정 합니다. 이제 로또 형태로 게임별 번호를 가로로 나열을 해야겠지요?   --결과 SELECT MIN(DECODE (MOD(ROWNUM -1,6)+1 ,1,num))no1,        MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 2,num)) no2,        MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 3,num)) no3,       MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 4,num)) no4,       MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 5,num)) no5,       MIN(DECODE(MOD(ROWNUM - 1,6)+1 , 6,num)) no6 FROM  (SELECT dep,num          FROM (SELECT dep,num,MOD(ROWNUM - 1,45) + 1 rnum                  FROM (SELECT CEIL(LEVEL/45) dep,                               MOD(LEVEL - 1 , 45) + 1 num                          FROM DUAL                        CONNECT BY LEVEL <= 45 * 5                          ORDER BY 1, DBMS_RANDOM.VALUE))        WHERE rnum <= 6        ORDER BY dep,num) GROUP BY dep;          NO1        NO2        NO3        NO4        NO5        NO6 ---------- ---------- ---------- ---------- ---------- ----------          2         11         22         25         32         39         10         15         18         35         39         43          5         15         22         25         27         40          3          9         22         24         25         40         13         29         36         40         42         45   가로로 나열하기 위한 기준값과 MOD(ROWNUM-1,6)+1 값과 DECODE문을 사용하고, 하나의 Row로 결과를 도출하기 위하여 DEP기준으로 Group BY하였습니다. 이제 로또 해도 되겠죠? ㅎ

   응답

댓글작성시 Code-Highlighter 삽입방법