YJ's Blog

* 재구매율 및 구매주기 분석 기준 - 재구매율 및 구매주기를 분석하기 위해서는 먼저 기준을 정해야 한다. - 재구매자: 최초 구매일 이후, +1일 후 구매자 - 구매주기: 구매간격(최근 구매일자 - 최초 구매일자) / (구매횟수 - 1) 1. 데이터 테이블 생성 - DATE_ADD: 시간 더하기. 앞에는 기준 날짜를 넣고 그 다음에는 더하고 싶은 시간. 즉, INTERVAL (SECOND~YEAR까지 모두 가능) - DATEDIFF: 날짜 차이 구하기 CREATE TABLE RE_PUR_CYCLE AS SELECT *, CASE WHEN DATE_ADD(최초구매일자, INTERVAL +1 DAY) 0; SELECT 구매주기_구간, COUNT(MEM_NO) AS 회원수 FROM (SELECT *, CAS..

* RFM이란? - 고객의 가치를 분석할 때 사용되는 고객가치 평가모형 - Recency: 최근성 - Frequence: 구매 빈도 - Monetary: 구매 금액 1. RFM 분석용 데이터 테이블 생성 CREATE TABLE RFM AS SELECT A.*, B.구매금액, B.구매횟수 FROM CUSTOMER AS A LEFT JOIN (SELECT A.MEM_NO, SUM(A.SALES_QTY * B.PRICE) AS 구매금액, # Monetary: 구매금액 COUNT(A.ORDER_NO) AS 구매횟수 #Frequency: 구매 빈도 FROM SALES AS A LEFT JOIN PRODUCT AS B ON A.PRODUCT_CODE = B.PRODUCT_CODE WHERE YEAR(A.ORDER_..

1. 간단한 데이터 마트 생성 - 회원 테이블과 매출 테이블을 조인한 후, 이를 바탕으로 가입년월, 나이, 연령대, 구매여부를 추가로 구함 CREATE TABLE CUSTOMER_PROFILE AS SELECT A.*, date_format(join_date, '%Y-%m') as 가입년월, 2021 - year(birthday) + 1 as 나이, CASE WHEN 2021 - year(birthday) + 1 < 20 THEN '10대 이하' WHEN 2021 - year(birthday) + 1 < 30 THEN '20대' WHEN 2021 - year(birthday) + 1 < 40 THEN '30대' WHEN 2021 - year(birthday) + 1 < 50 THEN '40대' ELSE ..

1. 일자별 첫 구매자 수 select start_date, count(distinct customerid) user_num from (select customerid, min(invoicedate) start_date from dataset3 group by 1) a group by 1 order by 1; 2. 상품별 첫 구매 고객 수 - 첫 구매가 가장 많이 이루어진 상품을 찾을수 있다면, 첫 구매를 유도한 상품의 특징을 파악할 수 있을 것 - 고객별로 구매일 기준으로 순위를 생성하면 고객의 최초 구매 상품을 조회할 수 있다. 다음으로 최초 구매 상품으로 데이터를 그룹핑하고 고객 수를 집계하면 상품별 최초 구매 고객 수를 계산할 수 있다 select stockcode, count(distinct C..

1. 국가별, 상품별 구매자 수 및 매출액 select country, StockCode, count(distinct CustomerID) bu, round(sum(Quantity*UnitPrice),2) sales from dataset3 group by 1,2 order by 3 desc, 4 desc; 2. 특정 상품 구매자가 많이 구매한 상품 - '장바구니 분석'의 핵심은 고객의 구매 내역을 보고 상품과 상품 사이에 상관관계가 있는지 살펴보는 것 - 특정 상품을 구매한 구매자가 어떤 상품을 많이 구매하는지 살펴보기 위해 먼저 가장 많이 판매된 Top 2 상품을 모두 구매한 고객이 구매한 상품 코드를 조회한다. 1) 가장 많이 판매된 2개 상품 조회 # limit 사용 select StockCode..

1. 구매자 분석 * 10분위 분석 - 고객별로 주문 건수를 계산한 뒤, 주문 건수를 기준으로 각 고객이 어떤 그룹에 속하는지 구한다. 다음으로 각 분위 위수별 주문 건수의 합을 구하면, 전체 주문 건이 어떤 그룹에 얼마나 집중되어 있는지 계산할 수 있다. 1) 주문 건수에 따른 rank 생성 select *, row_number() over(order by f desc) rnk from (select user_id, count(distinct order_id) f from orders group by 1) a; 2) 전체 고객 수를 계산하여 10%씩 나누고 임시 테이블 생성 - ex) 전체 고객 수가 3159명이면 1분위는 1~316명까지, 2분위는 317~632명까지 지정하는 식으로 반복 create..

1. 구매 지표 추출 1) 매출액(일별, 월별, 연도별) - 일별 select orderdate, sum(priceeach*quantityordered) from classicmodels.orders a left join classicmodels.orderdetails b on a.orderNumber = b.orderNumber group by orderdate order by orderdate; - 월별 select substr(a.orderdate, 1,7) as MM, sum(priceeach*quantityordered) as sales from classicmodels.orders a left join classicmodels.orderdetails b on a.orderNumber = b.o..

* 행 정렬 1. 검색 결과 오름차순 정렬 (order by) select 열명 from 테이블명 (where 조건식) order by 열명 ex) select * from sample31 order by age; 2. 내림차순 정렬 (order by desc) select 열명 from 테이블명 (where 조건식) order by 열명 desc + desc는 descendant(하강)의 약자! ​ *주의! 수치형과 문자열형 데이터는 대소관계의 계산 방법이 다르다. 문자열형 데이터는 사전식 순서로 정렬되므로 숫자 데이터가 문자열형 열에 입력되어 있을 경우 정렬 결과에 유의해야 한다. *복수 열 지정하여 정렬 select 열명 from 테이블명 order by 열명1, 열명2... 이 때, 정렬 순서는 ..

* 검색 조건 지정하기 행을 선택할 때는 where구 열을 선택할 때는 select구 ​ 1. select구에서 열 지정 select 열1, 열2,... from 테이블명 ​ 2. where구에서 행 지정 select 열 from 테이블명 where 조건식 ex) select * from sample21 where no=2; ​ +) = 값이 서로 다른 경우 참이 되는 연산자 응용) select * from sample21 where no2; +) 문자열형을 비교할 경우, 따옴표로 둘러싸 표기 (ex: name = '박준용') +) 날짜시간형도 동일. 이때, 연월일을 하이픈(-)으로 구분하며 시각은 시분초를 콜론(:)으로 구분하여 표기 ​ 3. Null 값 검색 - is null ex) select * ..

1. SELECT 예시) SELECT * FROM sample21; ​ SELECT = 명령의 한 종류로 데이터베이스의 데이터를 읽어오는 것 * = '모든 열'을 의미 FROM = 처리 대상 테이블 지정하는 키워드 SQL 명령의 마지막에는 세미콜론(;)을 붙임 +) 대소문자 상관 없음 ​ 2. DESC (SQL 명령은 아님) 예시) DESC sample21; 테이블에 대한 정보 확인 ​ Field: 열 이름 Type: 해당 열의 자료형 Null: null 값 사용 여부 Key Default: 기본값 ​ +) 자료형 종류 1. Integer 정수값 저장 2. Char 문자열 저장 (열의 최대 길이 지정해야 함) 고정 길이 문자열 최대 길이보다 작은 문자열을 저장할 경우, 공백문자로 나머지를 채운 후 저장 ..