CLASS

≫ CLASS ≫ 오늘의 퀴즈

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

일반태그: 퀴즈.DB태그: Oracle. 김지룡 5 Comments 2014년 10월 13일 602 (3)

아래의 SQL을 가지고 로또번호를 생성해 봅시다.

– ? 부분에 들어갈 적절한 함수를 찾으세요.

 SELECT ?(LEVEL/45)  dep,
?(LEVEL - 1 , 45) + 1 num
FROM DUAL
CONNECT BY LEVEL &lt;= 45 * 5
ORDER BY 1, DBMS_RANDOM.VALUE

--결과

 NO1        NO2        NO3        NO4        NO5        NO6
---- ---------- ---------- ---------- ---------- ----------
   1          5         11         17         23         43
   2         13         15         20         23         37
   2          9         21         23         26         43
  12         15         24         27         32         41
   9         12         15         23         25         33

SELECT ?(LEVEL/45) dep,

?(LEVEL - 1 , 45) + 1 num

FROM DUAL

CONNECT BY LEVEL <= 45 * 5

ORDER BY 1, DBMS_RANDOM.VALUE

--결과

NO1 NO2 NO3 NO4 NO5 NO6

---- ---------- ---------- ---------- ---------- ----------

1 5 11 17 23 43

2 13 15 20 23 37

2 9 21 23 26 43

12 15 24 27 32 41

9 12 15 23 25 33

1.문제 풀이

2.풀이 과정

3.결과

10월 24일 정답을 올리도록 하겠습니다.

About the Author

View all posts by 김지룡
● (주)더비소프트 SI&ITO사업부 / ERP사업팀 팀원
● 경력 5년(현 LG전자 GERP DBA/Tuning 업무)

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

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

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

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

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

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
;

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회를 진행해 보았습니다.

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
;

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

;

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

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

감사합니다.

응답

김수영 2014년 10월 24일 15:07

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

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

응답

하승환 2014년 10월 24일 17:44

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

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

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.

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 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. 열로 구성되어 있는 데이터를 행으로 만들기 위한 기준 데이터 생성

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.

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함수를 써도 무방함.)

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.

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. 최종 결과....

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

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값은 해결되고.. 우리가 원하는 결과가 나오게 됩니다. 감사합니다.

응답

김민호 2014년 10월 26일 22:23

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

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

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 개의 행이 선택되었습니다.

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 개의 행이 선택되었습니다.

응답

김지룡 2014년 10월 27일 08:59

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

--풀이
--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하였습니다.
이제 로또 해도 되겠죠? ㅎ

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하였습니다.

이제 로또 해도 되겠죠? ㅎ

응답

Tag Cloud

Recent Comments

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

About the Author

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

강정식 에 응답 남기기 응답 취소

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