57 -Oracle : SQL ( 로또 실습 예제 )

 1. 로또 기본 테이블 (lotto_basic) 생성 

-- 1. 로또 기본 테이블 (lotto_basic)
CREATE TABLE lotto_basic(
	seq_no 		NUMBER NOT NULL,	-- 로또 회차 
	draw_date	DATE, 				-- 추첨일 
	num1 	NUMBER,					-- 당첨번호1
	num2 	NUMBER,					-- 당첨번호2
	num3 	NUMBER,					-- 당첨번호3
	num4 	NUMBER,					-- 당첨번호4
	num5 	NUMBER,					-- 당첨번호5
	num6 	NUMBER,					-- 당첨번호6
	bonus 	NUMBER					-- 보너스번호 
);

SELECT * FROM lotto_basic;

-- 기본키 추가 : ALTER TABLE 이용 
-- → 기본키의 경우, 제약조건이므로 CONSTRAINTS 이용 
ALTER TABLE LOTTO_BASIC ADD CONSTRAINTS lotto_basic_pk PRIMARY KEY ( seq_no ) ;

 

 

2. 로또 상세 테이블 (lotto_detail) 생성 

-- 2. 로또 상세 테이블 (lotto_detail)
CREATE TABLE lotto_detail(
	seq_no 			NUMBER NOT NULL, 	-- 로또회차 
	rank_no 		NUMBER NOT NULL, 	-- 등수 
	win_person_no 	NUMBER, 			-- 당첨자수 
	win_won 		NUMBER	 			-- 1인당 당첨금액 
);
-- 기본키 추가 : seq_no, rank_no 2가지로 복합키 지정 
ALTER TABLE LOTTO_DETAIL DROP CONSTRAINTS lotto_detail_pk;	-- 기본키 삭제구문 
ALTER TABLE LOTTO_DETAIL ADD CONSTRAINTS lotto_detail_pk PRIMARY KEY (  seq_no, rank_no );

 

 

3. 각 테이블에 데이터 추가 후, 확인 

SELECT count(*) FROM LOTTO_BASIC lb ;	-- 827
SELECT count(*) FROM LOTTO_DETAIL ld ;	-- 4135

 

 

4. 분석 

-- 1-2) 지금까지 로또번호 중에 혹시 중복된 당첨번호가 있는가?
SELECT 
 	num1, num2, num3, num4, num5, num6
	FROM LOTTO_BASIC lb 
	GROUP BY num1, num2, num3, num4, num5, num6;

-- 1-3) 결과를 줄여서 개수가 1보다 큰 경우로 한정(제한)하기 
-- 그룹질의에서 집합함수가 올 수 있는 자리 : SELECT 컬럼절 또는 HAVING 조건절 

SELECT 
 	num1, num2, num3, num4, num5, num6, count(*) AS 중복개수 
	FROM LOTTO_BASIC lb 
	GROUP BY num1, num2, num3, num4, num5, num6
	HAVING COUNT(*) > 1 ;		-- 중복되는 갯수가 있으면 1보다 큼 

-- 1-4) 가장 많이 당첨된 당첨번호 조회 
-- 당첨번호 순서를 상관없이 모든 당첨번호를 통틀어 가장 많이 나온 번호 조회 
-- 1-4-1) 각각의 당첨번호 자리에서 조회 
-- 		  첫번째 자리 숫자당 당첨번호 개수 
SELECT NUM1 lotto_num , COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC lb 
	GROUP BY NUM1
	HAVING count(*) >= 50
	ORDER BY 2 DESC;

-- 		  두번째 자리 숫자당 당첨번호 개수 
SELECT num2 AS "2번째 공 나온 번호", COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC
	GROUP BY NUM2 
	ORDER BY 2 DESC;

SELECT num3 AS "3번째 공 나온 번호", COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC
	GROUP BY NUM3 
	ORDER BY 2 DESC;

SELECT num4 AS "4번째 공 나온 번호", COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC
	GROUP BY NUM4 
	ORDER BY 2 DESC;

SELECT num5 AS "5번째 공 나온 번호", COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC
	GROUP BY NUM5 
	ORDER BY 2 DESC;

SELECT num6 AS "6번째 공 나온 번호", COUNT(*) AS "번호 나온 횟수"
	FROM LOTTO_BASIC
	GROUP BY NUM6 
	ORDER BY 2 DESC;

	
-- 1-4-2) 각 자리별 당첨번호를 모두 합친 테이블을 만들어보자 
-- 1번 자리 당첨번호 중복횟수 결과 테이블 + 2번 자리 당첨번호 중복횟수 결과 테이블 + 
-- 3번 자리 당첨번호 중복횟수 결과 테이블 + 4번 자리 당첨번호 중복횟수 결과 테이블 + 	
-- 5번 자리 당첨번호 중복횟수 결과 테이블 + 6번 자리 당첨번호 중복횟수 결과 테이블 	
-- 테이블 합치기 : 집합연산자인 UNION(합집합)
-- 중복된 테이블 결과 : INTERSECT(교집합)
-- 중복된 결과 모두를 포함해서 합치기 : UNION ALL(합집합 + 교집합)
-- 중복된 결과를 뺀 순수한 나머지 테이블 결과 : MINUS(차집합)

-- num1자리 중복 갯수구하기  
SELECT NUM1 AS LOTTO_NUM, COUNT(*) AS CNT
	FROM LOTTO_BASIC 
	GROUP BY NUM1 
-- 두 개의 테이블 합치기 : 합치는 테이블의 구조 일치해야 함 ( 컬럼 개수, 각 컬럼의 데이터타입 )
--					  합친 결과의 컬럼명은 첫번째 테이블을 따름 
--					  정렬의 경우, 마지막에 작성 
	UNION ALL 
-- num2자리 중복 갯수구하기 
SELECT NUM2 AS LOTTO_NUM, COUNT(*) AS CNT
	FROM LOTTO_BASIC 
	GROUP BY NUM2
 	ORDER BY 2 DESC;
	
 -- 위와 같은 방식으로 NUM6까지 UNION ALL 해주면 됨
 -- 1-4-3) 위의 과정을 거치면 NUM1 ~ NUM6까지의 모든 자리에서 가장 많이 나온 번호들의 결과 테이블 생성 
 --        여기서 이제 중첩된 결과를 실행하면 최종적으로 원하는 가장 많이 당첨된 번호의 리스트를 얻을 수 있음 

SELECT 
	lotto_num, count(*) "각 번호의 출현 횟수", sum(cnt) AS total 
FROM  
	( SELECT NUM1 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC 
		GROUP BY NUM1 
	
		UNION ALL 
	
	SELECT NUM2 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC 
		GROUP BY NUM2
	 	
	 	UNION ALL 
	 	
	SELECT NUM3 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC
		GROUP BY NUM3 
	
		UNION ALL 
	
	SELECT NUM4 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC 
		GROUP BY NUM4
	 	
	 	UNION ALL
	 	
	SELECT NUM5 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC
		GROUP BY NUM5 
		
		UNION ALL 
		
	SELECT NUM6 AS LOTTO_NUM, COUNT(*) AS CNT
		FROM LOTTO_BASIC
		GROUP BY NUM6
	 	ORDER BY 2 )	-- num1 ~ num6까지의 총 병합 테이블의 결과 : 인라인 뷰 
GROUP BY 
	lotto_num
ORDER BY 
	3 DESC ;