PowerPoint Presentation

Transcription

1 롯데마트 CRM 데이터분석교육 고객분석 USING SQL 2014 년 12 월 > RE::VISION ~ 전용준리비젼컨설팅대표 xyxonxyxon@empal.com

2 교육과정구성 2/2 - Adjusted 주제 세부교육내용 3 캠페인설계와예측모델링 타겟캠페인설계와사후분석 [2H] 캠페인설계, 타게팅, 사후분석에대한이론 - 타겟캠페인실행사례이해 5H ( 이론 + 실습 ) 예측모델개발실습 [2H] - RFM 분석예제데이터생성연습 - 예측모델링개념이해 - 추가파생변수생성을통한간단한예측모델개발실습 4 탐색적분석응용과 CRM 데이터분석확대방안 탐색적데이터분석응용 [3H] 기간비교를필요로하는탐색적분석개념이해및실습 - 데이터재분류 ( 분석용임시코드체계생성 ) 활용한분석실습 - 탐색적분석결과를바탕으로한분석보고서작성실습 RFM + 추가파생변수예측모델링 [2H] - RFM 기반모델링 + Data Step + SQL 보충 5H ( 이론 + 실습 ) CRM 2.0 : CRM 업그레이드방안 [0.5H] 실전적용과고도화에서의 CRM 전반이슈와해결방안이해 CRM 사례연구 [1H] 롯데마트, 타유통사, 온라인유통에서의 CRM 빅데이터와데이터사이언스 [0.5H] 개념과사례, 이슈, 실전적용방안. Mash Up, 예측, 비정형데이터활용방안 2H (Vedio) 1

3 DAY 1.1 1B :: SAS 분석도구소개및 데이터처리기초 2

4 SAS : Statistical Analysis System SAS 는압도적인시장점유율 1 위의분석소프트웨어 대규모데이터통계처리에강점 데이터집계, 가공, 연결, 예측, 시각화, 통계치산출등다양한기능제공 SAS 고유의 Script 언어를사용 RDBMS 와는다른고유한데이터저장방식사용 실제데이터분석에서데이터처리에는 ANSI SQL 의변형인 Proc SQL 주로사용 3

5 SAS 활용한데이터처리기초 데이터로딩 간단한데이터처리 ( 조회, Sort, 집계, 선택 ) 데이터타입변환 4

6 SAS 활용의첫단계 라이브러리생성 / 지정 로컬의텍스트파일데이터불러오기 서버의데이터가져오기 데이터내보내기 변수정의 간단한계산 5

7 데이터테이블생성시 Tip 변수명은 32 자이내 숫자로시작불가! 특수기호사용불가! (except _ ) 자료의입력은되도록숫자변수로 주민등록번호나전화번호등은문자변수로입력 날짜변수는 mm dd yy로나누어서입력 한셀에입력했을경우에는 SAS에서구분가능 문자변수는되도록자릿수를맞춰서입력 6

8 로컬의텍스트 (ASCII) 파일데이터불러오기 메뉴에서 [ 파일 > 데이터가져오기 > ] 실행시위저드가실행됨 파일형식 ( 주로 CSV 또는 TDF ) 을지정해주고, 파일위치를지정해서선택 <141205_SAS 교육샘플데이터 > 라는이름의텍스트파일 7

9 스크립트로데이터불러오기 라이브러리명과파일이름을지정하고, 데이터위치와데이터파일형식을지정 주로 CSV 형식의텍스트파일을불러오는방식을사용 ( 엑셀버전이나연동관련된이슈가없음 ) GETNAMES 명령을지정해데이터파일로부터필드명을바로가져다사용 PROC IMPORT OUT= WORK.aaa02 DATAFILE= "C:\Users\revision\Desktop\rvc_kb\02_EnGageMent\01_13_ 롯데마트분석교육 \a01_test.csv" REPLACE; GETNAMES=YES; RUN; 8

10 스크립트로데이터불러오기 1 라이브러리명과파일이름을지정하고, 데이터위치와데이터파일형식을지정 주로 CSV 형식의텍스트파일을불러오는방식을사용 ( 엑셀버전이나연동관련된이슈가없음 ) GETNAMES 명령을지정해데이터파일로부터필드명을바로가져다사용 PROC IMPORT OUT= LM_POS01.aaa02 DATAFILE= "C:\SASTEST_201412\a01_test.csv" REPLACE; GETNAMES=YES; RUN; 9

11 CARDS 명령문을사용해직접입력 간단한데이터의경우구조를지정하고, CARDS 문을사용하여직접입력가능 DATA WORK.AAA03; INPUT SERIALNO AGE SEX $ EDUC MARRI; LABEL SEX=' 성별 '; CARDS; F F M F F F M F F M 2 2 ; RUN; 10

12 PRINT 문으로데이터확인 탐색기상의아이콘을클릭하여테이블보기로보거나, 생성된데이터 ( 테이블 ) 의내용을확인 화면에프린트 / 출력 PROC PRINT DATA=AAA03; RUN; 11

13 로컬 PC 로내보내기 - CSV 파일 메뉴에서 [ 파일 > 데이터내보내기 > ] 실행시위저드가실행됨 파일형식을지정해준후 ( 주로엑셀에서사용하므로 CSV 로설정 ) 파일위치를지정 12

14 데이터특성에대한기초적확인 PROC FREQ 를사용하여필드별값별빈도수를확인 PROC FREQ DATA=AAA03; TABLES age sex; RUN; 13

15 데이터특성에대한기초적확인 PROC FREQ 를사용하여필드별값별빈도수를확인 PROC FREQ DATA=AAA03; TABLES age sex; RUN; 14

16 데이터특성에대한기초적확인 PROC FREQ 를사용하여실습용실제 POS 데이터필드별빈도수를확인 PROC FREQ DATA=LM_POS01.AAA02; TABLES str_cd pos_sys_dy ; RUN; Str_cd ( 점포코드 ), Pos_Sys_dy ( 포스기준일자 ) 두필드에대한값분포를확인 ~ ( 총 5 일간 ) 로부터추출된데이터임을확인 필드의특성 ( 값분포 ) 을확인 15

17 라이브러리의지정 라이브러리란? SAS dataset 이저장되는 ( 논리적인 ) 장소 Dataset 이름사용형식 <library.dataset> library : 영문 8자이내 dataset : 영문 32자이내 대소문자구분없음, 특수문자사용불가 ( 예외 :_) 맨첫글자에숫자사용불가 Library name 지정 LIBNAME 라이브러리이름 ' 위치 '; LIBNAME LM_POS01 'C:\SASTEST_201412'; 16

18 샘플데이터셋의구조 1000 건의거래내역을포함한 POS 이력 STR_CD 점포코드 SALE_DY 판매일자 POS_NO 포스번호 TRD_NO 거래번호 CUST_NO 고객번호 CANCEL_FG 취소여부 CUST_PNT 고객포인트 SALE_SALE_AMT 판매금액 SALE_PROFIT_AMT 이익금액 SALE_DC_AMT 할인금액 SALE_EMP_NO 판매직원번호 17

19 DAY 2 2A :: SAS PROC SQL 참조 :: [ 마트 SAS] SQL 레벨 다음블로그 ( 검색창에서 [ 마트 sas sql ] 로검색 ) 18

20 SQL 데이터처리의표준언어 SQL : Standard Query Language 데이터의조회, 정의, 제어의세가지기능을포함 데이터정의언어 (DDL : Data Definition Language) 데이터조작언어 (DML : Data Manipulation Language) 데이터제어언어 (DCL : Data Control Language) SQL SELECT * FROM LM_POS01.AAA02 WHERE STR_CD=402 AND POS_NO=6 ; CREATE TABLE LM_POS01.AAA021 AS SELECT STR_CD, SALE_DY FROM LM_POS01.AAA02 WHERE STR_CD=402 AND POS_NO<10 ; 단순히기존의테이블로부터특정조건에맞는행을추출 일부필드와조건에맞는행만으로새로운테이블생성 19

21 연습 :: 취소건만가지고오려면? SQL : Standard Query Language 데이터의조회, 정의, 제어의세가지기능을포함 데이터정의언어 (DDL : Data Definition Language) 데이터조작언어 (DML : Data Manipulation Language) 데이터제어언어 (DCL : Data Control Language) CREATE TABLE LM_POS01.AAA022 AS SELECT STR_CD, SALE_DY, SALE_SALE_AMT FROM LM_POS01.AAA02 WHERE CANCEL_FG = 2 AND STR_CD=402 ; CANCEL_FG 값중취소건을확인해서조건을지정 20

22 테이블의생성 CREATE TABLE 문은라이브러리와테이블명을지정해주면해당테이블을생성하는명령 주의 : 모든필드를선택하여새로운테이블을반복해서생성하게되면기하급수적으로데이터물리적사이즈증가 CREATE TABLE LM_POS01.STRPOS AS SELECT STR_CD, POS_NO FROM LM_POS01.AAA02; 21

23 SAS 언어와 SQL 혼합지원 표준 SQL(ANSI SQL) 에 SAS 고유의언어를혼용가능 SAS keyword expressions ( 예 : DROP, KEEP, RENAME, WHERE) 를 SQL 에서사용가능함 CREATE TABLE LM_POS01.AAA022 AS SELECT * FROM AAA02(DROP=STR_CD ); AAA02 테이블에서 STR_CD 필드는제외하고나머지필드들만가져옴 22

24 Duplicates 중복제거 DISTINCT 문은같은필드에반복해서나오는항목을제거하고종류만골라내는기능 CREATE TABLE LM_POS01.TEMP001 AS SELECT STR_CD FROM LM_POS01.AAA02; CREATE TABLE LM_POS01.STR_CD_TB AS SELECT DISTINCT STR_CD FROM LM_POS01.AAA02; 점포코드만리스트를추출하여테이블로생성 CREATE TABLE LM_POS01.STR_CD_TB003 AS SELECT DISTINCT STR_CD, SALE_DY FROM LM_POS01.AAA02; 23

25 Sorting 정렬 ORDER BY 문을사용하여데이터정렬 Default 는오름차순, DESC 를주면역순 ( 내림차순 ) CREATE TABLE LM_POS01.STR_CD_TB AS SELECT DISTINCT STR_CD, POS_NO FROM LM_POS01.AAA02 ORDER BY STR_CD, POS_NO DESC; STR_CD 는오름차순, POS_NO 는내림차순으로정렬한테이블생성 24

26 선택및연산 Sub-setting and Calculating +, -, *, / 등의사칙연산을사용가능 () 를사용해서반복적인표현사용가능 WHERE 절에 IN() 함수를사용하여복수의값선택가능 CREATE TABLE LM_POS01.STR_CD_TB01 AS SELECT STR_CD, SALE_DY, (SALE_SALE_AMT-100)/1000 AS TEMP_SALEAMT FROM LM_POS01.AAA02 WHERE STR_CD IN ( ) ORDER BY STR_CD, SALE_DY; 통상적인사칙연산을 SELECT 절에적용하여새로운필드생성가능 WHERE 절을이용하여일부값에맞는레코드들만선별가능 25

27 Aggregation GROUP BY 는분류별 ( 예 : 지역, 점포, 연도, 고객등급등 ) 진계에주로사용되는구문 COUNT(), SUM(), AVG() 등이집계 ( 즉, 대표값 계산 ) 를위해많이사용됨 CREATE TABLE LM_POS01.STR_CD_TB AS SELECT STR_CD, AVG(SALE_SALE_AMT) AS SSAMT_AVG FROM LM_POS01.AAA02 GROUP BY STR_CD; 26

28 GROUP BY 분류별집계활용 집계기준으로복수의키를사용할수있음 ( 예 : 점포별 > 주별 > ) 집계키에서는사용되는순서대로순서가적용됨 ( 오름차, 내림차정렬에따라결과가달라짐에유의필요 CREATE TABLE LM_POS01.STR_DY_SSAMT001 AS SELECT STR_CD, SALE_DY SUM(SALE_SALE_AMT)/1000 AS STR_SALEAMT FROM LM_POS01.AAA02 GROUP BY STR_CD, SALE_DY ORDER BY SALE_DY DESC, STR_CD; 27

29 연습 :: Aggregation 평균, 합계, 건수기초적으로사용되는 대표값 산출방법예 : 건단가 10 만원초과고객머릿수산출출 CREATE TABLE LM_POS01.CUST_TMP001 AS SELECT CUST_NO, AVG(SALE_SALE_AMT) AS SSAMT_AVG FROM LM_POS01.AAA02 GROUP BY CUST_NO; CREATE TABLE LM_POS01.CUST_TMP002 AS SELECT CUST_NO, SSAMT_AVG FROM LM_POS01.CUST_TMP001 WHERE SSAMT_AVG > ORDER BY SSAMT_AVG DESC ; 28

30 연습 :: Aggregation 2 평균, 합계, 건수기초적으로사용되는 대표값 산출방법예 : 건단가 10 만원초과고객머릿수산출 CREATE TABLE LM_POS01.CUST_TMP001 AS SELECT CUST_NO, AVG(SALE_SALE_AMT) AS SSAMT_AVG FROM LM_POS01.AAA02 GROUP BY CUST_NO; CREATE TABLE LM_POS01.CUST_TMP003 AS SELECT COUNT(*) AS CNT FROM LM_POS01.CUST_TMP001 WHERE SSAMT_AVG > ; 고객별건별금액평균값산출 일정금액이상고객의수산출 일부고객의매출합계산출 CREATE TABLE LM_POS01.SALE_SUM01 AS SELECT SUM(SALE_SALE_AMT) AS SSA_SUM FROM LM_POS01.AAA02 WHERE SSAMT_AVG > ; 29

31 Joining Tables JOIN 은두개이상의테이블이가진데이터를연결시켜하나의테이블을생성하는명령 INNER, OUTER(LEFT), FULL 세가지존재 CREATE TABLE LM_POS01.STR_CD_TB03 AS SELECT A.*, B.* FROM LM_POS01.STR_CD_TB01 A INNER JOIN LM_POS01.STR_CD_TB02 B ON (A.STR_CD=B.STR_CD) WHERE A.STR_CD IN ( ); INNER JOIN 순서무관하게연결하는두테이블모두에존재하는레코드만을결과로반환 30

32 LEFT JOIN LEFT JOIN 은한쪽을기준으로다른테이블의레코드중일치하는부분만결합 예 : 매장포스에거래내역있는온라인회원의목록을포스거래내역과결합 ( 기준은매장포스거래내역 ) LEFT JOIN CREATE TABLE LM_POS01.STR_CD_TB04 AS SELECT A.*, B.* FROM LM_POS01.STR_CD_TB01 A LEFT JOIN LM_POS01.STR_CD_TB02 B ON (A.STR_CD=B.STR_CD) WHERE A.STR_CD IN ( ); 31

33 WEEK 2 연습문제 :: [ 문제 1] 점포별취소건의고객포인트최대값과최소값을산출하라 [ 문제 2] 고객만족도지수 50 점초과점포에한하여할인금액의일자별합계를산출하여점포명을함께표시한테이블을생성하라 [ 문제 3] 점포별일자별취소율 ( 취소건의비율 ) 을산출하라 [ 문제 4] 매출합계는점포중 5 위이내이면서, 인구수등급은중간에해당하는점포를찾아라 STR_CD SALE_DY POS_NO TRD_NO CUST_NO CANCEL_FG CUST_PNT SALE_SALE_AMT SALE_PROFIT_AMT SALE_DC_AMT SALE_EMP_NO 점포코드판매일자포스번호거래번호고객번호취소여부고객포인트판매금액이익금액할인금액판매직원번호 STR_CD STR_NM CSINDEX POPUL 점포코드점포명고객만족도지수인구수등급 32

34 DAY 2+ 2A+ :: SQL 33

35 THE NEW POS DATA SET SALE_HEAD SALE_PROD PRODUCT CD_CAT_DE PT POS 영수증 34

36 IMPORTING THE NEW POS DATA SET LIBNAME LM_POS01 'C:\SASTEST_201412'; PROC IMPORT OUT= LM_POS01.SALE_HEAD DATAFILE= "C:\SASTEST_201412\sale_head_ csv" REPLACE; GETNAMES=YES; RUN; PROC IMPORT OUT= LM_POS01.SALE_PROD DATAFILE= "C:\SASTEST_201412\sale_prod_ csv" REPLACE; GETNAMES=YES; RUN; PROC IMPORT OUT= LM_POS01.PRODUCT DATAFILE= "C:\SASTEST_201412\product.csv" REPLACE; GETNAMES=YES; RUN; PROC IMPORT OUT= LM_POS01.CD_CAT_DEPT DATAFILE= "C:\SASTEST_201412\cd_cat_dept.csv" REPLACE; GETNAMES=YES; RUN; 35

37 반복적인 JOIN 응용연습 고객별로몇개의상품을구매했는가? 가장많은개수의상품을구매한점포는? 상품군별로고객수는? 취소고객수가가장많은상품군명은? 36

38 SUBSTR() 함수활용 SUBSTR( 필드명, 시작자리, 문자열길이 ) CREATE TABLE LM_POS01.SPTMP008 AS SELECT PROD_NM, SUBSTR(PROD_NM,3,6) AS SPROD_NM FROM LM_POS01.PRODUCT ; 37

39 CASE 문의활용 CASE 문은조건부로값을계산하기위해사용 CASE WHEN THEN WHEN THEN. ELSE END 의형식 CREATE TABLE LM_POS01.SPTMP009 AS SELECT TRD_NO, CUST_NO, SALE_SALE_AMT, CASE WHEN SALE_SALE_AMT > THEN 1 ELSE 0 END AS IS_BIG FROM LM_POS01.SALE_HEAD; 38

40 UPDATE 문을활용한 NULL 처리 UPDATE 문은값을변경시키기위해사용 ( 예 : 일괄적으로 NULL 을채우기 UPDATE 문은수정이므로반복해서일부분을수정해서계층적으로만드는것도가능 UPDATE LM_POS01.SPTMP009 SET IS_BIG = 3 WHERE SALE_SALE_AMT > ; UPDATE LM_POS01.SPTMP009 SET IS_BIG = 8 WHERE SALE_SALE_AMT > ; 39

41 연습문제 :: [ 연습 1] 구매금액의합계는 5 위이내이나구매한고객수는비교적작은상품군은무엇인가? 세가지부문의이름을파악하라 [ 연습 2] 신선 으로시작되는명칭을가진 DEPT 의구매고객수와이익률은얼마인가? [ 연습 3] 일자별로구매단품수를기준으로상위 10% 에해당하는고객의절대인원수와해당고객의리스트 (CUST_NO) [ 연습 4] 구매건수 ( 영수증기준 ) 는많지않으나, 한영수증건당구매한고객수는전체점포중상위 30% 이내에해당하는많은구매고객수를가진점포의명치명칭을조회하라 [ 연습 5] 고객별로총이용액과포인트적립한금액을합산해서산출하고, 총이용액대비포인트금액의비율이가장높은 5 명의고객리스트를추출하라 [ 연습 6] 5 명의고객을구분하기위한필드를 BC001 이라하고, 7% 이상의총이용액대비포인트금액이되는고객을모두추출하라 [ 연습 7] 가장고객수가많은점포에서평균적인고객별구매일자 ( 날짜 ) 수가가장높은 ( 자주구매하는 ) 상품군 DEPT 의명칭을찾아라 [ 연습 8] 전체구매한고객중 2 일이상구매한고객의수비율은몇 % 인가? 40

42 SAS 남은항목들 Data Step 중요몇가지사용방법 Data type 변환 Sting, Numeric, Datetime Update 문활용 SQL 추가활용방법 Nested Query 작성 복수의테이블 Left Join 함수활용 : MONOTONIC, UNIQUE, RANUNI, NMISS 응용 기간구분한예측모델데이터셋준비 실전적인탐색적데이터분석연습 41

43 주요집계함수 : MAX, AVG, MIN, CV, COUNT 대표적인집계함수는최대, 최소, 평균, 레코드수, 변동성정도 CV = Coefficient of Variance = 표준편차 / 평균 CREATE TABLE LM_POS01.SPTMP011 AS SELECT DISTINCT IS_BIG, MAX(SALE_SALE_AMT) AS MAX_SSAMT, AVG(SALE_SALE_AMT) AS AVG_SSAMT, MIN(SALE_SALE_AMT) AS MIN_SSAMT, CV(SALE_SALE_AMT) AS CV_SSAMT, COUNT(*) AS CNT_SSAMT FROM LM_POS01.SPTMP009 GROUP BY IS_BIG; 42

44 문자 숫자변환함수활용 문자 숫자 :: INPUT ; 숫자 문자 :: PUT 자릿수의적절한지정필요 CREATE TABLE LM_POS01.SPTMP012 AS SELECT IS_BIG, PUT(IS_BIG*100, 3.) AS IS_BIG01 FROM LM_POS01.SPTMP011; CREATE TABLE LM_POS01.SPTMP013 AS SELECT IS_BIG, IS_BIG01, SUBSTR(IS_BIG01, 2,2) AS IS_BIG01A, INPUT(IS_BIG01, 6.) AS IS_BIG02 FROM LM_POS01.SPTMP012; 43

45 DATE Type Handling 날짜는 SAS 에서숫자형식으로관리하고표시형식을지정하여나타냄 CREATE TABLE LM_POS01.SPTMP014 AS SELECT *, INPUT(SUBSTR(STRIP('12/28/2014'),1,10),MMDDYY10.) AS CHECK_DAY, INPUT(SUBSTR(STRIP('12/28/2014'),1,10),MMDDYY10.) AS CHECK_DAY01 FORMAT MMDDYY10. FROM LM_POS01.SPTMP013; CREATE TABLE LM_POS01.SPTMP015 AS SELECT *, CHECK_DAY AS CHECKDAY00, CHECK_DAY AS CHECKDAY001 FORMAT MMDDYY10. FROM LM_POS01.SPTMP014 ; 44

46 DATE 표시형식변경 MMDDYY, YYMMDD 등표시형식을출력시지정, 변경가능 10 자리가되는이유는구분자도계산하기때문 CREATE TABLE LM_POS01.SPTMP016 AS SELECT *, CHECK_DAY AS CHECK_DAY02 FORMAT YYMMDD10. FROM LM_POS01.SPTMP014; CREATE TABLE LM_POS01.SPTMP016 AS SELECT *, CHECK_DAY AS CHECK_DAY02 FORMAT YYMMDD10., CHECK_DAY AS CHECK_DAY03 FORMAT YYMMDD8. FROM LM_POS01.SPTMP014; 45

47 DATETIME 과 DATE 의구분활용 DATETIME 포맷을적용하고있으나사실상활용은하지않고있음 불필요한시간부분을제외하기위해 DATEPART() 함수적용 CREATE TABLE LM_POS01.SPTMP042 AS SELECT DISTINCT STR_CD, DATEPART(SALE_DY) FORMAT YYMMDD10. AS SALE_DYA, SUM(SALE_SALE_AMT) AS SSAMT FROM LM_POS01.SALE_HEAD GROUP BY STR_CD, SALE_DY; 46

48 ROUND 와숫자자리수포맷표시 숫자의경우, 반올림, 올림, 내림등소수점표현처리필요 반올림등의자리수기준지정및표시소수점자릿수지정에옵션명시필요 CREATE TABLE LM_POS01.SPTMP033 AS SELECT DISTINCT PROD_NM, STR_NM, ROUND(AVG(SALE_SALE_AMT), 0.001) FORMAT=7.2 AS AVG_SSAMTR FROM LM_POS01.SPTMP031 WHERE STR_NM IN (' 서현점 ' ' 부평역점 ' ) GROUP BY STR_NM, PROD_NM ORDER BY PROD_NM; 47

49 NESTED / SUB QUERY 활용 하나의 SQL 질의안에일부로다른 SQL 질의가포함되는형태 JOIN 을명시적으로사용하지않고도필요한결과를한번에얻을수있음 CREATE TABLE LM_POS01.SPTMP021 AS SELECT DEPT_CD, DEPT_NM, SSAMT FROM (SELECT * FROM LM_POS01.SPTMP007 WHERE CCNT > 30) ; CREATE TABLE LM_POS01.SPTMP022 AS SELECT DEPT_CD, DEPT_NM, SSAMT, CCNT FROM LM_POS01.SPTMP007 WHERE DEPT_CD IN (SELECT DEPT_CD FROM LM_POS01.SPTMP007 WHERE CCNT > 50 AND SSAMT> ) ; 48

50 여러테이블을한번에 JOIN 기준이되는테이블을지정하고그와결합될두개이상테이블을 JOIN CREATE TABLE LM_POS01.SPTMP031 AS SELECT A.STR_CD, B.STR_NM, A.SALE_DY, A.PROD_CD, C.PROD_NM, A.SALE_SALE_AMT FROM LM_POS01.SALE_PROD AS A LEFT JOIN LM_POS01.FULL_STRCD AS B ON A.STR_CD=B.STR_CD LEFT JOIN LM_POS01.PRODUCT AS C ON A.PROD_CD=C.PROD_CD ; CREATE TABLE LM_POS01.SPTMP032 AS SELECT DISTINCT STR_NM, PROD_NM, SUM(SALE_SALE_AMT) AS SSAMT FROM LM_POS01.SPTMP031 WHERE STR_NM LIKE '%VIC%' GROUP BY STR_NM, PROD_NM; 문자중일부만알고있을때 LIKE 문을사용해검색 단, 속도문제! 49

51 SQL 내에서사용가능한함수활용 기준이되는테이블을지정하고그와결합될두개이상테이블을 JOIN RANUNI MONOTONIC UNIQUE NMISS 50

52 RANUNI 단순랜덤샘플링 0~1 사이의난수를발생시키는함수 난수를이용한무작위샘플링에활용가능 ( 예 : 전체고객중무작위 5%) CREATE TABLE LM_POS01.SPTMP051 AS SELECT DISTINCT CUST_NO, RANUNI(1234)*100 AS RNDM_ID FROM LM_POS01.SALE_HEAD; CREATE TABLE LM_POS01.SPTMP052 AS SELECT MAX(RNDM_ID) AS MAX_RNDM_ID, AVG(RNDM_ID) AS AVG_RNDM_ID, MIN(RNDM_ID) AS MIN_RNDM_ID FROM LM_POS01.SPTMP051; 51

53 RANUNI SHUFFLING RANUNI 문은 ORDER BY 절에서정렬순서를무작위로바꾸는데도활용가능 (SHUFFLING) 괄호안의숫자는 Seed OUTOBS 와함께사용하면랜덤샘플링의기능을사용가능 CREATE TABLE LM_POS01.SPTMP053 AS SELECT STR_CD, SUM(SALE_SALE_AMT) AS SSAMT FROM LM_POS01.SALE_HEAD GROUP BY STR_CD ORDER BY RANUNI(1234); 52

54 MONOTONIC 지정한순번의레코드추출 MONOTONIC 함수는특정한번호에해당하는순번의레코드조회 정렬이되어있는테이블에서앞에서부터 ( 위에서부터 ) 10~20% 구간내의행을가져오는식의요건처리에활용가능 CREATE TABLE LM_POS01.SPTMP055 AS SELECT CUST_NO FROM LM_POS01.SALE_HEAD WHERE MONOTONIC() BETWEEN 1 AND 10 ; 53

55 UNIQUE 와 DISTINCT 차이 DISTINCT 와동일한기능을하는 UNIQUE 단, 표준 SQL 의 DISTINCT 와는달리 UNIQUE 는함수형태 SAS PROC SQL 에서는함수로도 DISTINCT() 사용가능 CREATE TABLE LM_POS01.SPTMP061 AS SELECT UNIQUE(PROD_NM) AS PROD_NM FROM LM_POS01.PRODUCT ; CREATE TABLE LM_POS01.SPTMP062 AS SELECT DISTINCT(PROD_NM) AS PROD_NM FROM LM_POS01.PRODUCT; 54

56 UNIQUE 와 DISTINCT 활용 COUNT() 함수를활용하면한번에테이블내의복수필드에대한값의카테고리수통계산출가능 CREATE TABLE LM_POS01.SPTMP063 AS SELECT COUNT(UNIQUE(PROD_NM)) AS CNT_PROD_NM, COUNT(UNIQUE(PROD_CD)) AS CNT_PROD_CD, COUNT(UNIQUE(DEPT_CD)) AS CNT_DEPT_CD FROM LM_POS01.PRODUCT ; CREATE TABLE LM_POS01.SPTMP064 AS SELECT COUNT(DISTINCT PROD_NM) AS CNT_PROD_NM, COUNT(DISTINCT PROD_CD) AS CNT_PROD_CD, COUNT(DISTINCT DEPT_CD) AS CNT_DEPT_CD FROM LM_POS01.PRODUCT ; 55

57 NMISS MISSING RECORD 추출 COUNT는값이있는레코드건수를반환 * 또는특정필드를지정 NMISS는해당필드가 MISSING인가를반환 MISSING인레코드의숫자를세기위해사용 CREATE TABLE LM_POS01.SPTMP071 AS SELECT COUNT(*) AS CNT_REC, COUNT(CUST_NO) AS CNT_CUST_NO, NMISS(CUST_NO) AS CNT_CUST_NO_MSSNG, COUNT(STR_CD) AS CNT_STR_CD, NMISS(STR_CD) AS CNT_STR_CD_MSSNG FROM LM_POS01.SALE_HEAD ; 56

58 PROC SQL and SAS Macro language SAS Macro 를 SQL 구문내에서도사용할수있도록허용 반복된처리를프로그램화하는용도로사용 PROC SQL NOPRINT; SELECT DISTINCT PATIENT INTO :PAT1- :PAT999 FROM VITALS ORDER BY PATIENT; PROC SQL NOPRINT; SELECT DISTINCT PATIENT INTO :PATLIST SEPARATED BY, FROM VITALS ORDER BY PATIENT; 57

59 Data Table Management: INSERT 와 DELETE PROC SQL NOPRINT; INSERT INTO VITALS VALUES(102 20AUG2001 d ); INSERT INTO VITALS SET PATIENT=102, DATE= 27AUG2001 d, PULSE=77, TEMP=98.8, BPS=129, BPD=88; PROC SQL NOPRINT; DELETE FROM VITALS WHERE PATIENT = 101; 58

60 Data Table Management: ALTER TABLE & DROP TABLE DROP TABLE : 테이블의삭제 ALTER TABLE : 테이블의구조변경 PROC SQL NOPRINT; ALTER TABLE VITALS MODIFY DATE FORMAT=MMDDYY8. DROP TEMP; PROC SQL NOPRINT; DROP TABLE BP; 59

61 PROC SQL advantages over DATA step 인덱스관리가개선되면서오히려 DATA STEP 보다빠른처리가능해짐 테이블간의 JOIN 연결이간편함 ( 간단한스크립트로처리 ) SQL안에 SQL을넣는 Nest 방식활용가능 외부데이터베이스와의연결이용이 DATA STEP 은한테이블에행이많은경우에적합 60

62 [ 연습문제답 ] CUST_SEG EXAMPLE ( 연습용가상서점고객데이터베이스사용 ) CREATE TABLE LM_POS01.CUST_SEG03 AS SELECT SEX, CASE WHEN AGE < 30 THEN 1 WHEN AGE >=30 AND AGE <45 THEN 2 ELSE 3 END AS AGE_GRP, AVG(TOT_AMT) AS AVG_TOT_AMT, COUNT(*) AS CNT_CUST FROM LM_POS01.CUST_SEG01 GROUP BY SEX, AGE_GRP ; 61

63 연습문제 :: ADDITIONAL [ 연습 1] 취소건수가가장많은상위 50 명의고객중 7 명의고객을무작위로추출하여고객번호를하나의테이블로생성하라 [ 연습 2] 신선부문의상품을구매한영수증에서고객번호가 MISSING 인영수증건수를구하라 [ 연습 3] 판매액합계가가장큰두개의점포의점포명과해당점포의고객들의구매금액최대, 최소값과포인트합계를하나의테이블에표시하라 [ 연습 4] 이용고객수가가장많은점포 3 개의취소율 ( 총영수증건수중취소가포함된영수증건수비율 ) 을구하라 [ 연습 5] 구매가가장많은 5 개상품군에대하여각각별로가장취소율이낮은단품의목록을각각 2 개씩추출하여, 상품군명과단품명과함께표시하라 [ 연습 6] (AAA02 data set 을사용하여 ) 일자별, 점포별최대구매액을보였던고객의리스트를추출하라 [ 연습 7] 일자별로가장많은고객이이용한점포명과이용고객수, 그점포의다음날이용고객수를산출하라 62

64 DAY 2 2B :: 유통업에서의 고객데이터분석유형 63

65 유통업고객데이터분석기본패턴 1 차 :: 고객별구매액, 구매건수집계 2 차 :: 고객별구매패턴전반을영수증전체활용해서분석 3 차 :: 고객등급, 채널, 점포, 상품, 행사등각각을중심을기준으로분석 고객등급 고객 상품 가격 채널 구매 구매품목 점포 행사 64

66 유통업고객데이터분석의예 1 차 :: 고객별구매액, 구매건수집계 월별고객별구매액 연간전체고객평균구매일수 2차 :: 고객별구매패턴전반을영수증전체활용해서분석 최근 1년간고객별점포별구매일수 최근 3개월간행사상품구매구매건수및행사상품구매금액비중 최근 3개월십분위별 (Decile) 상품군별객단가와구매일수 3차 :: 고객등급, 채널, 점포, 상품, 행사등각각을중심을기준으로분석 고객등급별구매일수평균 점포별우수고객라면류구매금액비중 캠페인반응고객행사기간중인당구매금액평균 DM수신거부고객최종구매후경과기간평균 65

67 상품, 점포, 고객등급, 채널별등기본분석패턴 상품별분석 대분류상품군별구매금액합계 중분류상품군별구매고객수 점포별분석 최근 3 개월월별수도권점포매출비중 점포별고객구매주기 고객등급분석 전년대비우수고객등급이탈비율 고객등급별주구매상품군 채널분석 복수채널이용고객비율 온라인회원중오프라인점포이용고객비율 66

68 Thank You! contact: 전용준대표 / 컨설턴트 리비젼컨설팅 xyxonxyxon@empal.com Keyword: 예측모델링 데이터마이닝 빅데이터 67