🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다. 시계열에 따른 사용자 전체의 상태 변화 찾기 사용자는 서비스 사용 시작일부터 시간이 지나면서 ‘충성도 높은 사용자’, ‘사용을 중지’, ‘휴면’ 등으로 상태가 변화 사용자가 계속해서 사용(리피트) 사용자가 사용을 중단(탈퇴/휴면) 서비스 제공자는 사용자가 어느 정도에서 계속 사용하고 있는지 파악하고 목표와의 괴리를 어떻게 좁힐 것 인지 방안을 검토 휴면 사용자를 어떻게 다시 사용하게 만들지도 생각 완전 탈퇴의 경우 복귀는 어려우나 휴면 사용자는 메일 매거진/CM/광고 등을 활용하여 다시 사용하게 유도 샘플 데이터 SNS 사용자 마스터 테이블 SNS 액션 로그 테이블 사용자 마스터 테이블과 액션 로그 테이블 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10') , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2016-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2016-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2016-10-10', 'pc' , '2016-10-10') , ('U007', 'F', '1950-07-18', '2016-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2016-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2016-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2016-10-16', 'pc' , NULL ) , ('U011', 'F', '1993-10-21', '2016-10-18', 'pc' , NULL ) , ('U012', 'M', '1993-12-22', '2016-10-18', 'app', NULL ) , ('U013', 'M', '1988-02-09', '2016-10-20', 'app', NULL ) , ('U014', 'F', '1994-04-07', '2016-10-25', 'sp' , NULL ) , ('U015', 'F', '1994-03-01', '2016-11-01', 'app', NULL ) , ('U016', 'F', '1991-09-02', '2016-11-01', 'pc' , NULL ) , ('U017', 'F', '1972-05-21', '2016-11-01', 'app', NULL ) , ('U018', 'M', '2009-10-12', '2016-11-01', 'app', NULL ) , ('U019', 'M', '1957-05-18', '2016-11-01', 'pc' , NULL ) , ('U020', 'F', '1954-04-17', '2016-11-03', 'app', NULL ) , ('U021', 'M', '2002-08-14', '2016-11-03', 'sp' , NULL ) , ('U022', 'M', '1979-12-09', '2016-11-03', 'app', NULL ) , ('U023', 'M', '1992-01-12', '2016-11-04', 'sp' , NULL ) , ('U024', 'F', '1962-10-16', '2016-11-05', 'app', NULL ) , ('U025', 'F', '1958-06-26', '2016-11-05', 'app', NULL ) , ('U026', 'M', '1969-02-21', '2016-11-10', 'sp' , NULL ) , ('U027', 'F', '2001-07-10', '2016-11-10', 'pc' , NULL ) , ('U028', 'M', '1976-05-26', '2016-11-15', 'app', NULL ) , ('U029', 'M', '1964-04-06', '2016-11-28', 'pc' , NULL ) , ('U030', 'M', '1959-10-07', '2016-11-28', 'sp' , NULL ) ; 12.

🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다. 11. 사용자 전체의 특징과 경향 찾기 서비스를 제공하는 것은 사용자에게 가치를 제공하는 것 서비스 제공자 측에서 관련 정보로 알고 싶은 것 사용자의 속성(나이, 성별, 주소지) 사용자의 행동(구매한 상품, 사용한 기능, 사용한 빈도) 샘플 데이터 사용자 마스터 테이블 가입시 사용자 성별, 가입 날짜, 사용 장치 등 탈퇴시는 날짜만 기록 액션 로그 테이블 페이지 열람, 관심 상품 등록, 카트 추가, 구매, 리뷰 등 각 액션에 이름을 명시 user_id가 NULL 인 경우 비로그인 회원 액션 로그 테이블을 따로 만들고 액션 내용을 적어두면 별도의 JOIN, UNION 없이 데이터를 다룰 수 있음 사용자 마스터 테이블과 액션 로그 테이블 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 -- 사용자 마스터 정보 DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10') , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2016-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2016-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2016-10-10', 'pc' , '2016-10-10') , ('U007', 'F', '1950-07-18', '2016-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2016-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2016-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2016-10-16', 'pc' , NULL ) ; -- 액션 로그 DROP TABLE IF EXISTS action_log; CREATE TABLE action_log( session varchar(255) , user_id varchar(255) , action varchar(255) , category varchar(255) , products varchar(255) , amount integer , stamp varchar(255) ); INSERT INTO action_log VALUES ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('989004ea', 'U001', 'view' , NULL , NULL , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'favorite', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'review' , 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 18:01:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001,D002', NULL, '2016-11-03 18:02:00') , ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 19:00:00') , ('47db0370', 'U002', 'purchase', 'drama' , 'D001' , 1000, '2016-11-03 20:00:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 20:30:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A004' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A005' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A006' , NULL, '2016-11-04 12:00:00') , ('9afaf87c', 'U002', 'purchase', 'drama' , 'D002' , 1000, '2016-11-04 13:00:00') , ('9afaf87c', 'U001', 'purchase', 'action', 'A005,A006', 1000, '2016-11-04 15:00:00') ; 11.

구글 계정 생성 기본 90일 동안 $300 크레딧으로 무료 평가판을 사용할 수 있다. 무료 체험 기간이 지나면 무료 체험 중 생성한 리소스는 모두 중지 되며 유료 계정으로 업그레이드 하지 않으면 요금이 부과되지 않는다. 새 프로젝트 버튼을 클릭하여 프로젝트 생성하기 데이터 세트 생성하기 데이터 세트는 테이블과 뷰에 대한 액세스를 구성 및 제어하는 최상위 컨테이너이다. 테이블이나 뷰는 반드시 데이터 세트에 속해야 하므로 최소 한 개 이상의 데이터 세트를 구성해야 한다. Google Cloud Platform 콘솔 메뉴에서 BigQuery 메뉴를 선택

🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다. 10. 다면적인 축을 사용해 데이터 집약하기 매출의 시계열뿐만 아니라 상품의 카테고리, 가격 등을 조합하여 데이터의 특징을 추출해 리포팅 샘플 데이터 매출 데이터 로그 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 DROP TABLE IF EXISTS purchase_detail_log; CREATE TABLE purchase_detail_log( dt varchar(255) , order_id integer , user_id varchar(255) , item_id varchar(255) , price integer , category varchar(255) , sub_category varchar(255) ); INSERT INTO purchase_detail_log VALUES ('2017-01-18', 48291, 'usr33395', 'lad533', 37300, 'ladys_fashion', 'bag') , ('2017-01-18', 48291, 'usr33395', 'lad329', 97300, 'ladys_fashion', 'jacket') , ('2017-01-18', 48291, 'usr33395', 'lad102', 114600, 'ladys_fashion', 'jacket') , ('2017-01-18', 48291, 'usr33395', 'lad886', 33300, 'ladys_fashion', 'bag') , ('2017-01-18', 48292, 'usr52832', 'dvd871', 32800, 'dvd' , 'documentary') , ('2017-01-18', 48292, 'usr52832', 'gam167', 26000, 'game' , 'accessories') , ('2017-01-18', 48292, 'usr52832', 'lad289', 57300, 'ladys_fashion', 'bag') , ('2017-01-18', 48293, 'usr28891', 'out977', 28600, 'outdoor' , 'camp') , ('2017-01-18', 48293, 'usr28891', 'boo256', 22500, 'book' , 'business') , ('2017-01-18', 48293, 'usr28891', 'lad125', 61500, 'ladys_fashion', 'jacket') , ('2017-01-18', 48294, 'usr33604', 'mem233', 116300, 'mens_fashion' , 'jacket') , ('2017-01-18', 48294, 'usr33604', 'cd477' , 25800, 'cd' , 'classic') , ('2017-01-18', 48294, 'usr33604', 'boo468', 31000, 'book' , 'business') , ('2017-01-18', 48294, 'usr33604', 'foo402', 48700, 'food' , 'meats') , ('2017-01-18', 48295, 'usr38013', 'foo134', 32000, 'food' , 'fish') , ('2017-01-18', 48295, 'usr38013', 'lad147', 96100, 'ladys_fashion', 'jacket') ; 10.

🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다. 매출 데이터를 시계열로 집계 하기 매출 데이터를 시계열로 집계하면 규칙성을 찾거나, 어떤 기간과 비교했을 때 변화폭을 확인 할 수 있다. 9. 시계열 기반으로 데이터 집계하기 매출 데이터 로그 dt order_id user_id purchase_amount 2014-01-01 1 rhwpvvitou 13900 2014-01-01 2 hqnwoamzic 10616 2014-01-02 3 tzlmqryunr 21156 2014-01-02 4 wkmqqwbyai 14893 2014-01-03 5 ciecbedwbq 13054 2014-01-03 6 svgnbqsagx 24384 2014-01-03 7 dfgqftdocu 15591 2014-01-04 8 sbgqlzkvyn 3025 2014-01-04 9 lbedmngbol 24215 2014-01-04 10 itlvssbsgx 2059 2014-01-05 11 jqcmmguhik 4235 2014-01-05 12 jgotcrfeyn 28013 2014-01-05 13 pgeojzoshx 16008 2014-01-06 14 msjberhxnx 1980 2014-01-06 15 tlhbolohte 23494 2014-01-06 16 gbchhkcotf 3966 2014-01-07 17 zfmbpvpzvu 28159 2014-01-07 18 yauwzpaxtx 8715 2014-01-07 19 uyqboqfgex 10805 2014-01-08 20 hiqdkrzcpq 3462 2014-01-08 21 zosbvlylpv 13999 2014-01-08 22 bwfbchzgnl 2299 2014-01-09 23 zzgauelgrt 16475 2014-01-09 24 qrzfcwecge 6469 2014-01-10 25 njbpsrvvcq 16584 2014-01-10 26 cyxfgumkst 11339 9.

🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다. 데이터를 가공해야 하는 이유 다룰 데이터가 데이터 분석 용도로 상정되지 않은 경우 연산할 때 비교 가능한 상태를 만들고 오류를 회피하기 위한 경우 5. 하나의 값 조작하기 5.1 코드 값을 레이블로 변경하기 디폴트 값을 지정할 경우 ELSE 구문을 사용한다. 1 2 3 4 5 6 7 8 9 SELECT user_id , CASE WHEN register_device = 1 THEN '데스크톱' WHEN register_device = 2 THEN '스마트폰' WHEN register_device = 3 THEN '애플리케이션' ELSE '' END as device_name FROM mst_users; 5.