SQL 데이터 분석 - 나눔로또 분석

분석환경: Windows 10 Pro, Oracle DB, SQL Developer
참고 문헌: 홍형경 저 누구나 쉽게 SQL
데이터: 로또 1회차~827회차 당첨번호, 금액 외

1. lotto_master 테이블 생성

   CREATE TABLE lotto_master
   	     (seq_no     NUMBER      NOT NULL, -- 로또 회차
    	      draw_date  DATE            NULL, -- 추첨일
    	      num1       NUMBER          NULL, -- 당첨번호1
   	      num2       NUMBER          NULL, -- 당첨번호2
   	      num3       NUMBER          NULL, -- 당첨번호3	
    	      num4       NUMBER          NULL, -- 당첨번호4
    	      num5       NUMBER          NULL, -- 당첨번호5
    	      num6       NUMBER          NULL, -- 당첨번호6
    	      bonus      NUMBER          NULL  -- 보너스번호); 
     ALTER TABLE lotto_master
    	 ADD  CONSTRAINTS lotto_master_pk PRIMARY KEY (seq_no);

2. lotto_detail 테이블 생성

   CREATE TABLE lotto_detail
   	     (seq_no         NUMBER      NOT NULL,   -- 로또 회차
   	      rank_no        NUMBER      NOT NULL,   -- 등수
   	      win_person_no  NUMBER          NULL,   -- 당첨자 수
   	      win_money      NUMBER          NULL);  -- 인당 당첨 금액
     ALTER TABLE lotto_detail
   	  ADD CONSTRAINTS lotto_detail_pk PRIMARY KEY (seq_no, rank_no);

3. 데이터 입력(기존 참고문헌 자료를 통해 작성되어 있는 쿼리 복사 및 실행)
   

4. 중복번호 조회

   SELECT 	num1, num2, num3, num4, num5, num6, COUNT(*)
     FROM 		lotto_master
   GROUP BY 	num1, num2, num3, num4, num5, num6
   HAVING COUNT(*) > 1;

		중복된 번호가 없음을 알 수 있다.

5. num1 컬럼 값의 당첨 건수 조회

   SELECT num1 lotto_num, COUNT(*) CNT
     FROM lotto_master
    GROUP BY num1
    ORDER BY 2 DESC; -- 2번째 컬럼인 CNT의 내림차순으로 정렬

	num1의 가장 많은 당첨번호는 1임을 알 수 있다.

6. 가장 많은 당첨번호 조회
   : num2~num6 컬럼에도 위와 같은 로직을 적용해야 함.
   위와 같은 형태로 나머지 컬럼도 조회하는 쿼리를 만들어 COUNT(*) 값으로 내림차순 정렬.

   SELECT lotto_num, SUM(CNT) AS CNT
     FROM ( SELECT num1 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num1
             UNION ALL
            SELECT num2 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num2
             UNION ALL
             SELECT num3 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num3
             UNION ALL
             SELECT num4 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num4
             UNION ALL
             SELECT num5 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num5
             UNION ALL
             SELECT num6 lotto_num, COUNT(*) CNT
              FROM lotto_master
             GROUP BY num6 )
    GROUP BY lotto_num
    ORDER BY 2 DESC;

   
   num1부터 num6까지 값을 기준으로 건수를 구하는 쿼리 6개를 만들어 UNION ALL로 연결했고, 이를 다시 서브쿼리로 묶어 메인쿼리의 SELECT 절에 lotto_num, CNT 컬럼을 조회, lotto_num을 GROUP BY 절에 넣었고 SUN(CNT)로 집계했음.

7. 최대 당첨금과 당첨번호 조회
   : 기존 데이터 엑셀파일의 1등 당첨금 컬럼을 내림차 순으로 조회해 보면 알 수 있지만 당첨 번호까지 알기 위해 lotto_master 테이블과 조인을 실행.

   SELECT a.seq_no,
          a.draw_date,
          b.win_person_no,
          b.win_money,
          a.num1, a.num2, a.num3, a.num4, a.num5, a.num6, a.bonus
     FROM lotto_master a,
          lotto_detail b
    WHERE a.seq_no = b.seq_no /* 로또 번호까지 조회해야 하므로
    			      lotto_master와 lotto_detail 테이블을 
                  	 	      seq_no 컬럼으로 조인 */
      AND b.rank_no = 1 -- 최대 당첨금은 1등을 의미.
    ORDER BY b.win_money DESC;

2003년도 4월 12일 나눔로또 19회차가 약 407억원으로 당첨금이 가장 많은 당첨금이 나옴.

[응용문제]

(1) 로또에서 가장 적은 1등 당첨금이 나온 회차와 번호, 금액 조회하기

SELECT a.seq_no
     , a.drow_date
     , b.win_person_no
     , b.win_money
     , a.num1, a.num2, a.num3, a.num4, a.num5, a.num6, a.bonus
  FROM lotto_master a
     , lotto_detail b
 WHERE a.seq_no = b.seq_no
   AND b.rank_no = 1
   AND b.win_person_no > 0  -- 당첨자가 없는 회차는 제외
ORDER BY win_money;         -- 당첨금액 오름차순으로 정렬

2013년 5월 18일 나눔로또 제 546회차가 당첨자 30명, 1등 당첨금 4억 500만원 수준으로 가장 적은 회차이다.

(2) 당첨번호가 연속해서 나온 회차의 번호 조회하기

SELECT a.seq_no
     , a.drow_date
     , a.num1, a.num2, a.num3, a.num4, a.num5, a.num6
  FROM lotto_master a
     , lotto_detail b
 WHERE a.seq_no = b.seq_no
   AND b.rank_no = 1
   AND (  a.num2 = a.num1+1
       OR a.num3 = a.num2+1
       OR a.num4 = a.num3+1 
       OR a.num5 = a.num4+1
       OR a.num6 = a.num5+1
       )
ORDER BY seq_no;