굉장히 엄청나!

판다스(Pandas)

• 행렬 데이터를 처리하기 위한 다양한 함수를 지원하는 라이브러리
  
• 파일 읽기, 저장, 행렬데이터 처리, 기본 시각화 등 지원
• 데이터 전처리 과정에서 주로 사용됨
  

데이터 분석과정

• 데이터 수집 > 데이터 전처리 > 데이터 가공(필요시 전처리) > 데이터 분석 탐색/시각화(필요시 전처리) > 필요시 모델 훈련(머신러닝 or 딥러닝) > 웹서비스 또는 분석 보고서
• 책에 나온 일반적인 과정 : 데이터수집 > 전처리 > 분석 > 시각화로 설명되고 있다.
• 분석과정은 회사에 따라 다르다.
  

판다스(Pandas) 설치

• Anaconda Prompt에서 pip list로 pandas 설치 확인

• jupyter notebook에서 import

import pandas as pd

• 사용되는 데이터 파일 - 노란색 부분

컬럼 : 국적코드, 성별, 입국객수, 전년동기

행은 0행 부터 시작한다.

즉, 노란색 부분을 추출하려면 A열에서 C열, 1행에서 7행만 추출한다.

데이터 수집 시 확인 사항

• 날짜 확인 : 기준일로 사용
  - 년월일시분이 나타나 있는게 좋다.|
  - 최소한 년월까지는 있어야 한다. 최소 12개의 데이터를 확보할 수 있기 때문이다.
• 범주형 데이터 확인 : 예로 남자 또는 여자와 같은 데이터
  - 범주형 데이터가 많은 데이터가 좋다. 비교대상이 많아 지기 때문이다.
  - 비교대상이 많아지면 데이터로 보여줄 수 있는게 많아진다

데이터 읽어오기

•  파일 위치 지정
  - 상대경로로 지정한다.

file_path = "./files/sample_1.xlsx"

• 피일 데이터 추출하기
  - file_path : 파일지정(위치 포함)
  - header : 칼럼명으로 사용할 행의 위치(default - 0)
  - skipfooter : 행의 가장 밑에서부터 포함하지 않을 행의 갯수(default - 0)
  - usecols : 가지고 올 열의 범위(A분터 C까지의 열)(default - data가 있는 모든 열)

sample_1 = pd.read_excel(file_path,
                        header=1,
                        skipfooter=2,
                        usecols="A:C")

• csv, excel, json많이 사용
• csv는 순수한 text 파일로 처리 과정이 따로 안들어가서 가볍다. 즉, 용량을 더 많이 담을 수 있다.
• excel 파일 자체에 다양한 처리 과정이 같이 들어가기 때문에 무겁다.

• 데이터 조회하기

sample_1

• DataFrame 정보 확인하기
  - DataFrame 타입 : 행렬을 저장 관리하는 타입
  - info()함수는 데이터의 결측치(nan,null) 데이터 확인 가능
  - RangeIndex : 전체 행(row)의 갯수 0 to 5 총 6개
  - 전체 행의 갯수와 각 컬럼의 갯수가 안맞으면 결측 데이터가 존재한다는 의미.

sample_1.info()

# Non-Null = not null null이 아닌 데이터의 갯수
#  Dtype = 데이터 타입
#  object 문자열
# int 숫자

0 to 5 총 6개

• 데이터프레임 출력
  - 데이터 행/열이 많은 경우 -> 기본 상위 5개, 하위 5개를 추출해서 보여줌
  - 데이터 행/열의 갯수가 작으면 모두 보여줌
  
  
• head() : 상위 데이터 조회
  - 상위 5개
  

  sample_1.head()​

  
  - 상위 1개
  

  sample_1.head(1)​

• tail() : 마지막 데이터 조회
  - default : 5개
  - 하위 5개
  

  sample_1.tail()​

  
  - 하위 1개
  

  sample_1.tail(1)​

• 기초 통계 데이터

sample_1.describe()

- count : 데이터 행의 갯수
- mean : 데이터 평균
- std : 표준편차
- min : 최솟값
- max : 최댓값
- 25%, 50%(중앙값), 75% : 4분위수 데이터
        → 4분위수 데이터를 이용해서 이상치(이상한) 데이터 확인

- 기초통계 데이터는 숫자값을 가지는 컬럼에 대해서만 확인

데이터 조회하기

• key 입력하고 value 받아오기
  - 국적코드 데이터 조회하기

sample_1["국적코드"]

- dtype: object

  → 데이터 하나하나의 타입이 object이다.

• 타입 확인
  - 데이터 전체의 타입

type(sample_1["국적코드"])

- Series : 튜플과 모양이 같다. (사용법도 동일)
- {"국적코드" : (데이터, 데이터, ...)}

• 2차원 행렬 형태로 조회

sample_1[["국적코드"]]

type(sample_1[["국적코드"]])

- [[ ]] : 2차원의 행렬인 DataFrame 타입

• 국적코드와 성별 모두 조회하기
  - 컬럼 2개이상 조회시 [[ ]] 2차원 행렬 형태로 조회해야함

sample_1[["국적코드","성별"]]

기준년월 컬럼 추가하기

• 2019-11 값을 추가하기

sample_1["기준년월"] = '2019-11'

sample_1

데이터 필터링 하기

• 필터링 : 조건에 맞는 값 조회하기
• 성별 중에 여성인 데이터만 추출하기

condition = sample_1["성별"] == "여성" 
condition

• default 값은  True

sample_1[condition == False]

• 한 줄로 조회하기

sample_1[(sample_1["성별"] == "여성") == True]

• 객체 주소 전달 방식
  - 둘다 같은 곳을 바라 본다
  - sample_1이 바뀌면 sample_2가 바뀌고 sample_2가 바뀌면 sample_1이 바뀜

sample_2 = sample_1

• copy()
  - 메모리 복제방식 (신규로 동일하게 생성 됨)
  - 신규로 만들어진 메모리 주소를 받아옴 (서로 영향 안받음)
  - 주소를 넣는 방식이 아니라 메모리를 복제하는 방식

sample_2 = sample_1.copy()

🎄사용자  정의 함수

• 상품테이블에서 아래 컬럼 조회하기
  - 서브쿼리 사용 (join 사용하지 않기)
  - 조회컬럼 : 상품명, 상품분류명, 상품분류코드

 SELECT prod_name
 	, (select lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) AS lprod_nm
    	, prod_lgu
 FROM prod;

• 상품분류명을 추출하는 함수 생성하기
  - IF문으로 NULL값 있는지 확인
  - Delimiter 뒤에는 무조건 띄어쓰기
  - 문자열이 맞지 않아서 CHARACTER SET UTF8MB4 적용

 -- 함수 구문 시작
Delimiter //

-- 사용자 정의함수
CREATE FUNCTION udfGetLprodNm(paramGu VARCHAR(10))

-- 리턴타입 정의
RETURNS VARCHAR(50) CHARACTER SET UTF8MB4

-- 함수 기능 시작
BEGIN
	
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50) CHARACTER SET UTF8MB4;

	-- 상품분류명을 추출하는 sql구문 작성
	SELECT lprod_nm INTO result
	FROM lprod
	WHERE lprod_gu = paramGu;
	
	-- 조회결과가 있는지 확인
	if result IS NULL then
	 SET result = 'null';
	END if;
	
	-- 반환하기
	RETURN result;
    
-- 함수 기능 종료
END //

-- 함수 전체 구문 종료
Delimiter ;

•  생성한 함수 호출하여 처리하기

SELECT prod_name
	,defGetLprodNm(prod_lgu) AS lprod_nm
    	,prod_lgu
FROM prod;

위에서 서브쿼리를 사용하여 작성한 코드와 동일한 결과

찾은 행 74 개

사용자 함수 정의 문제

문제 1

• 회원이름, 성별 조회하기
• 성별은 남성, 여성으로 조회
• 함수 이름 : udfGetMenWomen
  
• udfGetMenWomen 함수 정의
  - 남성 / 여성 추출하기

Delimiter //

CREATE FUNCTION udfGetMenWomen(paramNum INT)

-- 리턴 타입 정의
RETURNS VARCHAR(4) CHARACTER SET UTF8
BEGIN
	-- 반환할 변수 선언
	DECLARE result VARCHAR(4) CHARACTER SET UTF8;
	
	-- 남여 구분을 위한 조건문 처리
	if MOD(SUBSTRING(paramNum, 1, 1), 2) = 0 THEN
		SET result = '여성';
	ELSE 
		SET result = '남성';
	END if;

	RETURN result;
	
END //

Delimiter ;

• 생성한 함수 호출하여 처리하기

SELECT mem_name
, udfGetMenWomen(mem_regno2) AS gender
FROM member;

문제 2

• 새로운 주문이 발생했을 때 신규주문번호를 발급하는 함수 생성하기
• 함수이름 : udfGetNewCartNo()
  
• udfGetNewCartNo 함수 정의

Delimiter //

CREATE FUNCTION udfGetNewCartNo(paramNum VARCHAR(50))

RETURNS VARCHAR(50) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(50) CHARACTER SET UTF8;
	
	if SUBSTRING(paramNum,1,8) = CURDATE() then	
		set result = paramNum + 1;
	ELSE
		SET result = concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'00001');
	END if;
	
	RETURN result;

END //

Delimiter ;

• 생성한 함수 호출하여 처리하기

SELECT udfGetNewCartNo(MAX(cart_no)) AS newnum
FROM cart;

• udfGetNewCartNo 함수 정의 - 강사님 

--
Delimiter //

CREATE FUNCTION udfGetNewCartNo()

RETURNS VARCHAR(13) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(13) CHARACTER SET UTF8;
	
	SELECT if (MAX(cart_no) IS NOT NULL,
					MAX(cart_no) + 1,
						CONCAT(replace(CURDATE(), '-', ''),
								'00001')) INTO result
													
	FROM cart
	WHERE SUBSTRING(cart_no, 1, 8) = CURDATE();
	
	RETURN result;

END //

Delimiter ;

• 생성한 함수 호출하여 처리하기 - 강사님
  

SELECT udfGetNewCartNo();

🎄View

•  VIEW : 가상테이블
• 자주 사용되거나, SQL 구문이 긴 경우 조회 목적으로만 사용
• 미리 객체화 시켜서 테이블 처럼 사용하는 방식
• 조회만 가능하며, 입력/수정/삭제가 되지 않는다.
• 입력/수정/삭제가 되는 경우도 있지만, VIEW 사용 목적에 맞지 않는다.
  
• 구매 내역이 없는 회원 조회하기
  - 조회 컬럼 : 회원아이디, 회원이름

SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member FROM cart);

• 구매내역이 없는 회원 view 생성

Delimiter //

CREATE VIEW viewNoCartMember AS

-- 조회할 select문 생성
SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member 
FROM cart)//

Delimiter ;

• view 사용하기
  - 사용법 : 테이블과 동일

SELECT *
from viewNoCartMember;

🎄저장 프로시저(Stored Procedure; sp)

• 프로그램 영역이 아닌, DB자체에서 SQL구문을 관리하고자 할 때 사용
• 데이터베이스에 SQL구문을 객체화하여 호출방식으로만 사용하는 방식
  
  
• 구매내역이 없는 회원 프로시저 생성

Delimiter //

CREATE Procedure spNoCartMember()
Begin

-- 조회할 select문 생성
SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member 
FROM cart);

END //
Delimiter ;

• 프로시저 호출하기

call spNoCartMember();

VIEW / 저장 프로시저 문제

문제 1

• 모든 거래처별 매출 금액의 총합 조회하기
• 단, 2005년도 주문내역
• 조회컬럼 : 거래처 코드, 거래처명, 매출금액의 총합
• 위 조회에 대한 select문을 view로 생성
• 프로시저에서 위에 생성한 view를 사용하여 call
• view이름 : viewGetBuyerAll
• 프로시저이름 : spGetBuyerAll
  
  
• viewGetBuyerAll VIEW 생성

Delimiter //

CREATE VIEW viewGetBuyerAll AS

	-- 조회할 select문 생성
	SELECT buyer_id
			, buyer_name
			, SUM(NVL(prod_sale*cart_qty,0)) AS total
	FROM buyer
		LEFT JOIN prod
			ON (buyer_id = prod_buyer)
		LEFT JOIN CART
			ON (prod_id = cart_prod 
					AND cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name//

Delimiter ;

• spGetBuyerAll 프로시저 생성

Delimiter //

CREATE procedure spGetBuyerAll()
begin

	-- 조회할 select문 생성
	SELECT buyer_id
			, buyer_name
			, SUM(NVL(prod_sale*cart_qty,0)) AS total
	FROM buyer
		LEFT JOIN prod
			ON (buyer_id = prod_buyer)
		LEFT JOIN CART
			ON (prod_id = cart_prod 
					AND cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name;
	
END //	

Delimiter ;

• VIEW / 프로시저 호출

SELECT *
FROM viewgetbuyerall;

CALL spGetBuyerAll();

🎄저장 모듈(Stored Module)

•  SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.
  
  
• Procedure와 Trigger의 차이점

🎀 Union / Union All

Union

• 조회결과와 조회결과를 행단위로 합치는 기능
• 조회결과들 간의 칼럼의 개수는 동일해야 함
• 조회결과들 간의 데이터 타입을 동일해야 함
• 예시
  - 'member'와 'cart'는 구분자로 쓰임

SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;

찾은 행  28개

UNION ALL

• 회원 28명, 주문정보 135건 일 때 아래 코드 실행 시 결과
  UNION : 155건
  UNION ALL : 163건

SELECT 'member', mem_id, mem_name
FROM member
UNION all
SELECT 'cart', cart_member, cart_prod
FROM cart;

• UNION : 중복제거
• UNION ALL : 중복포함
• MINUS : 차집합
• INTERSECT : 교집합

Union 문제

문제 1 - 1번

SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY mem_id
UNION
SELECT '', '', SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY '', '';

찾은 행 29개

- 그룹화 하지 않은 SUM은 전체 행의 합이 구해진다.

- Group by ' ', ' ' 는 아무 의미가 없지만 빈 문자열로 컬럼을 지정하면 오류가 나기 때문에 문법상 적어준 것이다.

문제 1 - 2번

• Union 문제 1 - 1번 에서 총 구매금액 순을 내림차순으로 정렬
• 단, 총 구매금액의 총합은 마지막 행에 위치

SELECT A.mem_id, A.mem_name, A.total
FROM 
(SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY mem_id
ORDER BY total DESC) A
UNION
SELECT '', '', SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY '', '';

🎀 Exists / Not Exists

Exists

• 조회결과가 1건이라도 있으면 true, 0건이면 false
• 서브쿼리를 적용(다중컬럼의 다중행 모두 가능)
• 예시
  - 구매내역이 있는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 19명이 조회 된다.
  

  SELECT mem_id, mem_name
  FROM member
  WHERE EXISTS(SELECT *
  FROM cart
  WHERE cart_member = mem_id);​

Not Exists

• 예시
  - 구매 (주문) 내역이 없는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 9명이 조회 된다.

SELECT mem_id, mem_name
FROM member
WHERE NOT EXISTS (SELECT * FROM cart WHERE mem_id = cart_member);

Exists / Not Exists 문제

문제 1번

• 2005년도 구매내역이 있는 회원 조회하기
• 단, 구매내역이 있는 회원의 총구매금액이 3천만원 이상인 데이터에 대해서만 조회
• 조회컬럼 : 회원아이디, 회원이름, 마일리지
• 방법 1 - Exists 사용

SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS (
		SELECT SUM(cart_qty*prod_sale)
			FROM cart, prod
			WHERE cart_member = mem_id
				AND cart_prod = prod_id
				AND left(cart_no,4) = '2005'
		HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

SELECT A.mem_id, A.mem_name, A.mem_mileage
FROM(
	SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
		FROM member 
        	LEFT JOIN cart
				ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
			LEFT JOIN prod
				ON(cart_prod = prod_id)
		GROUP BY mem_id) A
WHERE A.total >= 30000000;

결과는 동일하다.

🎀 UPDATE

• 데이터 수정하기
• UPDATE 할 땐 수정하려는 값이 문제에서 제시한 수정 대상이 맞는지  SELECT ~ WHERE 절로 확인하기
• 수정 및 삭제 시 기준 컬럼은 PK값을 기준으로 행을 조건 처리 해야 함
  
• 예시 1
  - 회원아이디 a001, b001인 회원의 마일리지 값을 2로 수정

UPDATE member set mem_mileage = 2
WHERE mem_id IN ('a001','b001') ;

• 예시 2
  - 구매내역이 있는 회원의 마일리지 값을 3으로 일괄 수정처리하기

UPDATE member SET mem_mileage =3
WHERE EXISTS(SELECT *
FROM cart
WHERE cart_member = mem_id);

UPDATE 문제

문제 1

• Exists 문제 1번 조건에 만족하는 회원에 대해서 마일리지 값을 1000점 부여하기
• 방법 1 - Exists 사용

UPDATE member SET mem_mileage = 1000
WHERE EXISTS (
			SELECT SUM(cart_qty*prod_sale)
				FROM cart, prod
					WHERE cart_member = mem_id
						AND cart_prod = prod_id
						AND left(cart_no,4) = '2005'
					HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

UPDATE member SET mem_mileage = (mem_mileage + 1000)
	WHERE mem_id IN(
		SELECT A.mem_id
		FROM (
			SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
			FROM member 
				LEFT JOIN cart
					ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
				LEFT JOIN prod
					ON(cart_prod = prod_id)
			GROUP BY mem_id) A
	WHERE A.total >= 30000000);

Inner Join 복습 문제

문제 

• 회원아이디 b001 회원의 마일리지 값보다 큰 회원정보 조회
• 조회칼럼 : 회원아이디, 이름

-- 1
SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage
FROM member
WHERE mem_id ='b001') ;

-- 2
SELECT A.mem_id, A.mem_name
FROM (SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage FROM member WHERE mem_id ='b001')) A ;

-- 3
SELECT mem_id, mem_name
FROM member, 
(SELECT mem_mileage
FROM member
WHERE mem_id ='b001') MEM
WHERE member.mem_mileage > MEM.mem_mileage ;

결과는 모두 동일하다.

🎀 Self Join

• Self Join으로 처리하기
• 셀프조인 시 : 조건은 한쪽 m2 테이블에 적용
• 조회 시 : m1 테이블에서 조회

Self Join 문제

문제 1번

• Inner Join 문제 1번에 Self join을 사용해 보기

SELECT m1.mem_id, m1.mem_name
FROM member m1, member m2
WHERE m2.mem_id = 'b001'
AND m1.mem_mileage > m2.mem_mileage;

Inner Join 문제 1번과 결과가 동일하다.

🎀 Outer Join

• 특정 테이블 전체에 대한 집계를 하고자 할 때 사용
• OUTER JOIN 종류
  1. LEFT OUTER JOIN : 테이블 순서 중 왼쪽에 위치한 테이블 전체
  2. RIGHT OUTER JOIN : 테이블 순서 중 오른쪽에 위치한 테이블 전체
  3. FULL OUTER JOIN : 왼쪽, 오른쪽 전체 테이블 (Oracle DB만 사용 가능)
• OUTER JOIN 개념
  1. Outer Join은 표준방식으로만 사용해야 하며 일반 조건은 무조건 On() 내부에 작성해야 한다
  2. 일반 조건을 where 절을 사용하면 Outer 전체 계념이 적용이 안된다
  3. Inner Join을 만족해야 한다(Inner Join 개념을 그대로 적용)
  4. Left or Right 기준으로 같으면 같은 조건대로 조회하고 다르면 null로 조회된다

Outer join 문제

문제 1번

SELECT lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod 
	LEFT JOIN prod
		ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt;

문제 2 - 1번

• 회원별 구매금액의 총액 조회
• 2005년 5월에 구매한 내역
• 조회칼럼 : 회원이름, 구매금액 총액
  

  -- 일반 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member, prod, cart
  WHERE mem_id=cart_member
  AND cart_prod = prod_id
  AND substring(cart_no,1,6) = '200505'
  group BY mem_id, mem_name
  ORDER BY total desc;
  
  -- 표준 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member INNER join cart
  ON (mem_id=cart_member AND substring(cart_no,1,6) = '200505')
  INNER JOIN prod
  ON (cart_prod = prod_id)
  group BY cart_member
  ORDER BY total desc;​

찾은 행 13개

5월에 구매한 내역이 있는 고객만 조회됨

문제 2 - 2번

• 문제 2 - 1에 이어서 회원 전체에 대해서 문제 2 - 1조건으로 조회하기
• 총회원은 28명

SELECT mem_name, SUM(nvl(cart_qty,0)*nvl(prod_sale,0)) AS total
FROM member left join cart
ON (mem_id=cart_member)
left JOIN prod
ON (cart_prod = prod_id)
group BY mem_id, mem_name
ORDER BY total desc;

찾은 행 28개

구매이력이 없는 회원도 모두 조회되며

NULL값을 가지면 0으로 대체됨

문제 3 - 1번

• 2005년도 월별 판매 현황 검색하기
• 조회칼럼 : 판매월, 월별 총판매수량, 월별 총판매금액

SELECT substring(cart_no,1,6) AS cart_no, sum(cart_qty) AS cart_qty, sum(cart_qty*prod_sale) AS total
	FROM cart 
		left join prod
			ON (cart_prod = prod_id
					AND SUBSTRING(cart_no,1,4) = '2005')
	GROUP BY substring(cart_no,1,6);

문제 3 - 2번

• 문제 3 - 1번 조건에서 1월 ~ 12월까지 모두 표현하고자 한다면?
  
• 방법 1 - UNION 사용

SELECT
    months.month,
    NVL(SUM(A.cart_qty), 0) AS total_sales,
    NVL(SUM(A.cart_qty*B.prod_sale),0) AS total
FROM (
    SELECT '200501' AS month
    UNION SELECT '200502'
    UNION SELECT '200503'
    UNION SELECT '200504'
    UNION SELECT '200505'
    UNION SELECT '200506'
    UNION SELECT '200507'
    UNION SELECT '200508'
    UNION SELECT '200509'
    UNION SELECT '200510'
    UNION SELECT '200511'
    UNION SELECT '200512'
) AS months
LEFT JOIN cart A ON (SUBSTRING(A.cart_no, 1, 6) = months.month)
LEFT join prod B ON (A.cart_prod = B.prod_id)
GROUP BY months.month
ORDER BY months.month;

• 방법 2 - 새로운 날짜 테이블 생성

-- 테이블 생성
CREATE TABLE date_mm (
	mm CHAR(2) NOT NULL
);

-- 값 입력
INSERT INTO date_mm VALUES('01');
INSERT INTO date_mm VALUES('02');
INSERT INTO date_mm VALUES('03');
INSERT INTO date_mm VALUES('04');
INSERT INTO date_mm VALUES('05');
INSERT INTO date_mm VALUES('06');
INSERT INTO date_mm VALUES('07');
INSERT INTO date_mm VALUES('08');
INSERT INTO date_mm VALUES('09');
INSERT INTO date_mm VALUES('10');
INSERT INTO date_mm VALUES('11');
INSERT INTO date_mm VALUES('12');

-- 조회 코드 작성
SELECT date_mm.mm,
	SUM(nvl(cart_qty,0)) AS total_qty,
	SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM date_mm
	LEFT JOIN cart
		ON(date_mm.mm = SUBSTRING(cart_no,5,2))
	LEFT JOIN prod
		ON(cart_prod = prod_id
			AND substring(cart_no,1,4) = '2005')
GROUP BY date_mm.mm;

결과는 동일하다.

문제 4번

• 2005년도에 대한 전체 거래처별 총매출금액 조회하기
• 조회칼럼 : 거래처 코드, 거래처명, 총매출금액
• 정렬 : 총매출금액 - 내림차순

SELECT buyer_id, buyer_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM buyer 
	LEFT join prod
		ON (buyer_id = prod_buyer)
	LEFT join cart
		ON(prod_id = cart_prod AND substring(cart_no,1,4) = '2005' )
GROUP BY buyer_id
ORDER BY total DESC;

찾은 행 13개

문제

• 가장 최근에 입고된 상품의 상품명, 거래처명
  
• 가장 최근에 입고된 상품을 가장 많이 구매한 회원의 아이디, 이름, 마일리지, 해당 상품 구매수량, 회원등급 구하기
• 조회 컬럼 : 상품명, 거래처명, 회원아이디, 회원이름, 마일리지, 해당 상품 구매수량, 회원 등급
• 등급 조건

• ERD

작성한 코드

코드 1

SELECT  AA.prod_name, AA.buyer_name, AA.mem_id, AA.mem_name, AA.mem_mileage, AA.cart_qty,
(CASE
        WHEN AA.mem_mileage BETWEEN 1000 AND 2000 THEN '브론즈'
        WHEN AA.mem_mileage BETWEEN 2000 AND 3000 THEN '실버'
        WHEN AA.mem_mileage BETWEEN 3000 AND 4000 THEN '골드'
        WHEN AA.mem_mileage BETWEEN 4000 AND 5000 THEN '플래티넘'
        WHEN AA.mem_mileage >= 5000 THEN '다이아'
    	ELSE '아이언'
	 END) AS grade 
FROM (
	SELECT prod_name, buyer_name, mem_id, mem_name, mem_mileage, cart_qty, cart_prod
   	FROM member, cart, prod, buyer
    		WHERE mem_id = cart_member
            AND cart_prod = prod_id
            AND prod_buyer = buyer_id 
      ) AA,
     (
	SELECT cart_prod, MAX(cart_qty) AS cart_qty
		FROM cart
    		WHERE cart_prod IN (
           				 SELECT B.buy_prod
           					 FROM (SELECT MAX(buy_date) AS max_date
                  					FROM buyprod) A,
                 					(SELECT buy_prod, buy_date
                  					FROM buyprod) B
            					WHERE A.max_date = B.buy_date)
    			GROUP BY cart_prod ) BB
	WHERE AA.cart_prod = BB.cart_prod 
      AND AA.cart_qty = BB.cart_qty
	ORDER BY prod_name;

코드 2

출처 미니 조코딩 님

https://sujakjil.tistory.com/54

SELECT prod_name ,buyer_name ,mem_id , mem_name , mem_mileage , cart_qty ,
      (case 
      when mem_mileage BETWEEN 1000 AND 1999 then '브론즈'
      when mem_mileage BETWEEN 2000 AND 2999 then '실버'
      when mem_mileage BETWEEN 3000 AND 3999 then '골드'
      when mem_mileage BETWEEN 4000 AND 4999 then '플래티넘'
      ELSE '다이아' 
      END) AS 'grade'
FROM member , cart , prod , buyer
WHERE prod_id = cart_prod
AND mem_id = cart_member
AND prod_buyer = buyer_id
AND prod_id IN (SELECT prod_id FROM buyprod , prod 
               WHERE prod_id = buy_prod
               AND buy_date = (SELECT MAX(buy_date)
               FROM buyprod))
AND cart_qty in (SELECT  MAX(cart_qty)
FROM cart , prod
WHERE cart_prod = prod_id
and prod_id IN (SELECT prod_id FROM buyprod , prod 
               WHERE prod_id = buy_prod
               AND buy_date = (SELECT MAX(buy_date)
               FROM buyprod))
GROUP BY prod_id);

결과

⛄ JOIN

• 조인(join) 구문 작성하는 방법
• Inner Join 방식은 일반방식 or 표준방식 모두 표준처럼 사용됨
• Inner Join - 일반방식
  - 두 테이블 간에 PK = FK인 조건을 제시
  - from절 뒤에 테이블들을 ,로 구분하여 사용
  - where절에 관계 조건식(PK=FK)을 추가함
  - 관계조건식은 최소한 (사용된 테이블의 갯수 -1) 만큼 제시되어야 함

select 테이블1.컬럼1...테이블n.컬럼1
 From 테이블1, 테이블2 ... 테이블n
 Where 관계조건(PK = FK)
   And 일반조건

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member, cart
-- 관계조건식 추가(PK = FK)
WHERE mem_id = cart_member
	-- 일반조건 추가
	AND mem_add1 LIKE '서울%';

• Inner Join - 표준방식
  - 일반 조건을 where절에 작성하는 것도 가능하다.

select 테이블1.컬럼1...테이블n.컬럼1
 From 테이블1 Inner Join 테이블2
    On(관계조건(PK = FK)
     And 일반조건)
     Inner join 테이블N
    On(관계조건(PK = FK)
      And 일반조건)
 Group by ... 그룹이 있는 경우
 Having ...그룹 조건이 있는 경우
 Order By ...정렬이 있는 경우

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member INNER JOIN cart
		on (mem_id = cart_member
       	 		-- 일반조건 추가
			AND mem_add1 LIKE '서울%')
	    INNER join prod
		on (cart_prod = prod_id);

• cross join
  - 행의 갯수 = 테이블행 전체갯수 * 테이블행 전체갯수
  - 아래와 같이 두 테이블 간의 관계 조건 없이 사용한 경우

SELECT mem_id, cart_member
FROM member, cart;

JOIN 문제

📝문제 1 

• 조회컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회
• 조건
  1. 주문내역이 있는 회원이어야 하고 회원 거주지가 서울에 거주하는 회원이어야 한다.
  2. 상품명에 컴퓨터라는 단어가 포함되어있어야 한다.
  3. 일반방식과 표준방식으로 작성하기
  4. 주문수량을 기준으로 내림차순

✏️일반방식 

SELECT mem_id, mem_name, cart_no, cart_qty, prod_name
FROM member, cart, prod
WHERE mem_id = cart_member
	AND cart_prod = prod_id
   	AND mem_add1 LIKE '서울%'
	AND prod_name like '%컴퓨터'
order by cart_qty DESC;

✏️표준방식 

SELECT mem_id, mem_name, cart_no, cart_qty, prod_name
FROM member 
	INNER JOIN cart
 		ON (mem_id = cart_member AND mem_add1 LIKE '서울%')
  	INNER JOIN prod
 		ON (cart_prod = prod_id AND prod_name like '%컴퓨터')
order by cart_qty DESC;

조회되는 데이터 없음

📝문제 2 

• 조회 컬럼 : 상품분류코드, 상품분류명, 분류별 상품의 수
• 조건
  1. 상품 분류별 상품의 수를 조회
  2. 분류별 상품의 수 내림차순

✏️일반방식 

SELECT lprod_gu, lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod, prod
WHERE lprod_gu = prod_lgu
GROUP BY lprod_gu
ORDER BY cnt DESC;

✏️표준방식 

SELECT lprod_gu, lprod_nm, COUNT(lprod_gu) AS cnt
FROM lprod
	INNER JOIN prod
		ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt DESC;

📝문제 3

• 조회 컬럼 : 회원아이디, 회원이름, 주문 수량, 상품명
• 조건
  1. 구매상품의 거래처 주소가 서울, 대전, 광주
  2. 상품 분류명에 전자가 포함
  3. 주문수량이 5이상
  4. 아이디 오름차순, 주문수량 내림차순

✏️일반방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member, cart, prod, lprod, buyer
WHERE mem_id = cart_member
	AND cart_prod = prod_id
	AND prod_lgu  = lprod_gu
	AND Prod_buyer = buyer_id
	AND substring(buyer_add1,1,2) IN ('서울', '대전','광주')
	AND lprod_nm LIKE '%전자%'
	AND cart_qty >= 5
ORDER BY mem_id, cart_qty DESC;

✏️표준방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member
		INNER JOIN cart
 			ON(mem_id = cart_member AND cart_qty >= 5)
 		INNER JOIN prod
 			ON(cart_prod = prod_id)
		INNER JOIN lprod
			ON(prod_lgu  = lprod_gu AND lprod_nm LIKE '%전자%')
		INNER JOIN buyer
		 	ON(Prod_buyer = buyer_id AND substring(buyer_add1,1,2) IN ('서울', '대전','광주'))
ORDER BY mem_id, cart_qty DESC;

조회되는 데이터 없음

📝문제 4

• 주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회
• 조회컬럼 : 회원아이디, 회원이름, 총지출액

✏️일반방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member, cart, prod
WHERE mem_id = cart_member
	AND cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

✏️표준방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member 
	inner join cart
		ON mem_id = cart_member
	inner join prod
		ON cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

⛄ Inline View

Inline View 문제

✏️문제 1

• JOIN 문제 4번에 이어서 지출이 가장 큰 값과, 가장 작은 값을 조회하기
• 조회 컬럼 : 최댓값, 최솟값

SELECT MAX(A.total) AS total_max
			, MIN(A.total) AS total_min
FROM (
    SELECT SUM(prod_sale * cart_qty) AS total
    FROM member
        INNER JOIN cart 
		  ON mem_id = cart_member
        INNER JOIN prod 
		  ON cart_prod = prod_id
    GROUP BY cart_member) A;

✏️문제 2 

- Inline View 1번 문제의 결과에서 최대값을 가지는 회원아이디와 이름 조회

SELECT AA.mem_id, AA.mem_name, AA.total
FROM(
	SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
			GROUP BY cart_member) AA,
	(
	SELECT MAX(A.total) AS total_max, MIN(A.total) AS total_min
		FROM (SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
				GROUP BY cart_member) A) BB
WHERE AA.total = BB.total_max;