[6] SQL로 분석하고 Tableau로 시각화하자 - VIEW,PROCEDURE,데이터 마트, 데이터 정합성

효율화 & 자동화에 필요한 SQL 문법

 

VIEW

 

가상 테이블 또는 저장된 SQL명령어이다.

CREATE VIEW [Order_Member]
AS
SELECT A.*
	,B.gender
	,B.ageband
	,B.join_date
	FROM [Order] A
	LEFT
	JOIN [Member] B
	ON A.mem_no = B.mem_no

 

A는 전부(*) 셀렉트하고, B에서는 mem_no를 제외한 이유는 [Order]테이블의 mem_no와 중복되는 열이기 때문이다.

CREATE VIEW [Order Member]
AS
SELECT *
FROM [Order] A
LEFT
JOIN [Member] B
	ON A.mem_no = B.mem_no

반면에 B에서 mem_no를 제외하지 않고 위처럼 입력하면 에러가 난다.

SELECT *
	FROM [Order_Member]

위에서 만들어진 가상 테이블 VIEW에서 바로 조회할 수 있다.

 

alter view사용, 그리고 where절을 추가하여 view를 수정할 수 있다.

ALTER VIEW [Order_Member]
AS
SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
	FROM [Order] A
	LEFT
	JOIN [Member] B
	ON A.mem_no = B.mem_no
	WHERE A.channel_code = 1

VIEW 삭제

DROP VIEW [Order_Member]

 

PROCEDURE

 

프로시저는 뷰와 같이 SQL명령어를 저장하지만, 매개변수라는 프로그래밍 기능이 추가된다. 자주 사용하는 SQL명령어를 자동적으로 실행할 수 있게 해준다.

--PROCEDURE 생성
CREATE PROCEDURE [Order_Member]
(
@channel_code AS INT
)
AS 
SELECT * 
	FROM [Order] A
	LEFT
	JOIN [Member] B
	ON A.mem_no = B.mem_no
WHERE A.channel_code = @channel_code


--PROCEDURE 실행
EXEC [Order_Member] 3

1. 실행시 EDU 데이터베이스에 [Order_Member]가 프로시저로 프로그래밍 기능이 생성된다. 
2. PROCEDURE에서는 열 이름 중복임에도 불구하고 모든 열을 조회할 수 있는데, 그 이유는 VIEW와 다르게 가상테이블이 아니기 때문이다. 
3. 변수 3으로 입력했기 때문에 channel_code = 3 으로 필터되어 조회된다.

 

PROCEDURE 수정

 

--PROCEDURE 수정
ALTER PROCEDURE [Order_Member]
(
@channel_code AS INT
,@YEAR_order_date AS INT
)
AS
SELECT *
	FROM [Order] A
	LEFT
	JOIN [Member] B
	ON A.mem_no = B.mem_no
	WHERE A.channel_code = @channel_code
	AND YEAR(order_date) = @YEAR_order_date

EXEC [Order_Member] 3,2021

1. 위에서 매개변수(@YEAR_order_date) 및 where절(AND YEAR(order_date) = @Year_order_date)만 추가한 명령어이다.
2. 변수는 3, 2021로 입력했기 때문에 channel_code = 3 및 YEAR(order_date) = 2021로 필터되어 조회된다.

 

프로시저 삭제

 

DROP PROCEDURE [Order_Member]

 

데이터 마트

 

분석 목적에 맞게 데이터를 가공한 분석용 데이터 세트이다.

 

예제) " 2020년도 주문금액 및 건수를 회원 프로파일 " 이라는 분석 목적에 맞게 데이터 마트를 생성해보자.

 

1. [Order]테이블의 [mem_no]별 [sales_amt] 합계 및 [order_no] 개수

 

*조건 : [order_date]는 2020년

* 열 이름 : [slaes_amt]의 합계는 [tot_amt] / [order_no] 개수는 [tot_tr]

 

--1. [order]테이블에서 [mem_no]별로 [sales_amt]합계 및 [order_no]의 개수 구하기
SELECT mem_no
		,SUM(sales_amt) AS tot_amt
		,COUNT(order_no) AS tot_amt
	FROM [Order]
	WHERE YEAR(order_date) = 2020
	GROUP 
		BY mem_no

 

2. [Member] 테이블을 왼쪽으로 하여 (1) 테이블을 LEFT JOIN

 

--2. [member]테이블을 왼쪽으로, (1)을 left join
SELECT A.*
	,B.tot_amt
	,B.tot_tr
	FROM [Member] A
	LEFT
	JOIN (
		SELECT mem_no 
			,SUM(sales_amt) AS tot_amt
			,COUNT(order_no) AS tot_tr
			FROM [Order]
			WHERE YEAR(order_date) = 2020
			GROUP
				BY mem_no
				)B
			ON A.mem_no = B.mem_no

 

3. (2)를 활용하여 구매여부 열 추가 (case when 이용)

 

--3. (2)를 활용하여 구매 여부 열 추가
SELECT A.*
		,B.tot_amt
		,B.tot_tr
		,CASE WHEN B.mem_no IS NOT NULL THEN '구매자'
			ELSE '미구매자' END AS pur_yn
		FROM [Member] A
		LEFT
		JOIN ( 
				SELECT mem_no
					,SUM(sales_amt) AS tot_amt
					,COUNT(order_no) AS tot_tr
				FROM [Order] 
				WHERE YEAR(order_date) = 2020
				GROUP
					BY mem_no
					)B
				ON A.mem_no = B.mem_no

 

4. (3)의 조회된 결과 테이블 생성 (INTO 이용)

 

--4. (3)의 조회된 결과 테이블 생성
SELECT A.*
		,B.tot_amt
		,B.tot_tr
		,CASE WHEN B.mem_no IS NOT NULL THEN '구매자'
				ELSE '미구매자' END AS pur_yn
		INTO [MART_2020]
		FROM [Member] A
		LEFT 
		JOIN ( 
			SELECT mem_no
				,SUM(sales_amt) AS tot_amt
				,COUNT(order_no) AS tot_tr
			FROM [Order]
			WHERE YEAR(order_date) = 2020
			GROUP 
				BY mem_no
				)B
			ON A.mem_no = B.mem_no

 

데이터 정합성

 

데이터 정합성은 분석 데이터 값들이 일관되게 일치함을 나타내는 정도이다.

 

위 데이터 마트에서 만든 [MART_2020] 을 통해 데이터 정합성을 확인해보자.

 

확인 사항 : [MART_2020]의 회원 및 주문 수가 정확한 데이터인가?

 

1. [MART_2020] 데이터마트의 회원수 중복이 없는지 확인

 

--1. [MART_2020]데이터 마트의 회원수 중복은 없는지 확인 (DISTINCT 이용)
SELECT COUNT(mem_no) AS 회원수
	,COUNT(DISTINCT mem_no) AS 회원수_중복제거
	FROM [MART_2020]

 

2. [Member] 테이블과 [MART_2020] 데이터 마트의 회원수 차이는 없는지 확인(FROM열만 변경)

 

--2.[Member] 테이블과 [MART_2020] 데이터 마트의 회원수 차이는 없는지 확인(FROM열만 변경)
SELECT COUNT(mem_no) AS 회원수
	,COUNT(DISTINCT mem_no) AS 회원수_중복제거
	FROM [Member]

 

3. [Order]테이블과 [Mart_2020]데이터 마트의 주문 수 차이는 없는지 확인

 

--3.[Order]테이블과 [Mart_2020]데이터 마트의 주문 수 차이는 없는지 확인
SELECT SUM(tot_tr) AS 주문수
	FROM [MART_2020]
SELECT COUNT(order_no) AS 주문수
		,COUNT(DISTINCT order_no) AS 주문수_중복제거
	FROM [Order]
	WHERE YEAR(order_date) = 2020

 

4. [MART_2020]데이터 마트의 미구매자는 [Order] 테이블에서 2020년에 구매가 없는지 확인

 

--4.[MART_2020]데이터 마트의 미구매자는 [Order] 테이블에서 2020년에 구매가 없는지 확인
SELECT *
	FROM [Order]
	WHERE mem_no IN ( SELECT mem_no FROM [MART_2020] WHERE pur_yn = '미구매자')
	AND YEAR(order_date) = 2020