컴공생의 개인공부일지

목차 1. 검색 기능 평가하기 2. 검색 결과의 포괄성을 지표화하기 3. 검색 결과 순위와 관련된 지표 계산하기 4. 데이터 마이닝 5. 추천 시스템 구성하기 6. 점수 계산하기 7. 거대한 숫자 지표 쉽게 가공하기 실습에 필요한 테이블 및 자료 검색 기능 평가하기 검색하는 사용자의 행동 사용자가 무엇을 검색하고 검색 결과에 대해 어떤 행동을 취하는지 구분할 수 있으면, 엔지니어에게 기능 개선등을 요청 가능하다. 검색 기능 개선 방법 검색 키워드의 흔들림을 흡수할 수 있게 동의어 사전 추가하는 방법이 있다. 검색 키워드를 검색 엔진이 이해할 수 있게 사용자 사전 추가하는 방법이 있다. Ex.) 위스키 검색 시 위, 스키 단어로 분해 → 스키 검색 시 위스키로 결과나오는 경우 이런 경우를 방지하기 위해 사..

목차 1. IP 주소로 국가와 지역 정보 확인하기 2. 주말과 공휴일 판단하기 3. 이상값 검출하기 4. 크롤러 봇 판단하기 5. 데이터 타당성 확인하기 6. 데이터 중복 검출하기 7. 여러 개의 데이터셋 비교하기 실습에 필요한 테이블 및 자료 DROP TABLE IF EXISTS mst_city_ip; CREATE TABLE mst_city_ip( networkinet PRIMARY KEY , geoname_idinteger , registered_country_geoname_idinteger , represented_country_geoname_idinteger , is_anonymous_proxyboolean , is_satellite_providerboolean , postal_codevarcha..

목차 1. 날짜별 접근 데이터를 집계하는 쿼리 2. 경로 별 집계하기 3. 유입원별로 방문 횟수 또는 CVR 집계하기 4. 사이트 내의 사용자 행동 파악하기 5. 이탈률과 직귀율 계산하기 6. 검색 조건들의 사용자 행동 가시화하기 7. 폴아웃 리포트를 사용해 사용자 회유를 가시화하기 8. 오류율 집계하기 실습에 필요한 테이블 날짜별 접근 데이터를 집계하는 쿼리 로그인을 한 뒤 사용하는 이용자와 로그인을 하지 않고 사용하는 이용자를 나누는 것이 집계할 떄 의미있는 결과를 도출한다. 사이트에서 로그인 기능을 제공하는 경우, 로그인 UU와 비로그인 UU도 동시에 집계되어야 한다. SELECT substring(stamp, 1, 10) AS dt -- 쿠키 계산하기 , COUNT(DISTINCT long_ses..

목차 1. RFM 분석에서 통합 랭크를 계산하는 쿼리 2. 날짜별 등록 수 추이 3. 지속률과 정착률 산출하기 4. 액션 수에 따른 정착률 집계하기 5. 사용 일수에 따른 정착률 집계하기 6. 사용자 잔존율 집계하기 7. 방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기 실습에 필요한 테이블 RFM 분석에서 통합 랭크를 계산하는 쿼리 다음 사진과 같이 RFM 분석을 3차원으로 표현하면 125개의 그룹이 생겨 관리하기가 어려워진다. 따라서, 사용자를 1차원으로 구분하여 R+F+M값을 통합 랭크로 계산을 하는 방법이 있다. 이렇게 관리하게 되면 13개의 그룹으로 나누어 관리하게 되므로 보다 관리하기 수월해진다. WITH user_rfm AS ( SELECT user_id , MAX(dt) AS recen..

목차 1. 사용자의 액션 수와 비율 계산하기 2. 로그인 사용자와 비로그인 사용자를 구분해서 집계하기 3. 연령별 구분 집계하기 4. 한 주에 며칠정도 사용되었는지를 집계하기 5. 구성비와 구성비누계 산출하기 6. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기 7. RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기 실습에 필요한 테이블 사용자의 액션 수와 비율 계산하기 액션과 관련된 지표를 집계하는데에는 사용률(usage_rate)이 필요하다 특정 액션 UU를 전체 액션 UU로 나눈 것을 사용률(usage_rate)이라고 한다. UU : Unique Users 중복없이 집계된 사용자 수 WITH stats AS ( -- 로그 전체의 유니크 사용자 수 구하기 SELECT COUNT(DI..

목차 1. 당월 매출 누계 구하기 2. Z 차트로 업적의 추이 확인하기 3. 카테고리별 매출과 소계 계산하기 4. ABC 분석으로 잘 팔리는 상품 판별하기 5. 팬 차트로 상품의 매출 증가율 확인하기 6. 데이터의 계층을 구하는 쿼리 실습에 필요한 테이블 당월 매출 누계 구하기 월별로 목표로 설정하는 현장에서는 해당 월에 어느 정도의 매출이 누적되었는지를 확인할 수 있어야 한다 윈도 함수를 사용해서 표현한다 날짜별 매출과 월별 누계 매출을 동시에 집계하고자 substring 함수 사용한다 매월 누계를 구하기 위해 OVER 구에 PARTITION BY substiring을 사용해 월별로 파티션을 생성한다 SELECT dt , substring(dt, 1, 7) AS year_month , SUM(purch..

목차 1. 마스터 테이블의 행 수를 변경하지 않고 여러 개의 테이블을 가로로 정렬하는 쿼리 2. 조건 플래그를 0과 1로 표현하기 3. 계산한 테이블에 이름을 붙여 재사용하기 4. 순번을 사용해 테이블 작성하기 실습에 필요한 테이블 마스터 테이블의 행 수를 변경하지 않고 여러 개의 테이블을 가로로 정렬하는 쿼리 일반적으로 테이블을 가로로 정렬 할 시에 JOIN을 사용하게 되는데 이로 인해 중복데이터, 결합하지 못한 데이터가 발생 됨 따라서 LEFT JOIN을 사용하여 마스터 테이블은 그대로 놔두고 합치려는 테이블 데이터만 변경 이로 인해 테이블 누락, 중복을 회피 SELECT m.category_id , m.name , s.sales , r.product_id AS top_sale_product FROM..

목차 1. 그룹핑한 데이터 특징량 계산하기 2. ORDER BY 구문을 통해 테이블 내부의 순서를 다루는 쿼리 3. 각 카테고리의 상위 n개 추출하기 4. 가로 기반 데이터를 세로로 변환하기 실습에 필요한 테이블 그룹핑한 데이터 특징량 계산하기 GROUP BY 뒤에 집약 함수는 SELECT 구문 컬럼만 지정 가능 SELECT 구문 내부에서 product_id, score 동시 지정은 불가능 SUM, AVG 등 집계 함수를 이용해서 결과값 도출 SELECT user_id , COUNT(*) AS total_count , COUNT(DISTINCT user_id) AS user_count , COUNT(DISTINCT product_id) AS product_count , SUM(score) AS sum ,..

목차 1. 코드 값을 레이블로 변경하는 법 2. URL에서 경로와 요청 매개변수 값 추출하는 법 3. 날짜와 타임스탬프 다루는 법 4. 결손 값을 디폴트 값으로 대치하는 법 5. CTR의 비율 계산하기 실습에 필요한 테이블 코드 값을 레이블로 변경하는 법 코드 값을 레이블로 변경하기 위해ㅇ서 CASE WHEN THEN 을 이용 SELECT user_id ,CASE WHEN register_device = 1 THEN '데스크톱' WHEN register_device = 2 THEN '스마트폰' WHEN register_device = 3 THEN '애플리케이션' END AS device_name FROM mst_users; URL에서 경로와 요청 매개변수 값 추출하는 법 분석 현장에서는 로그 조건과 분석..

데이터의 종류 업무에 필요한 데이터 : 업무 데이터 업무에 직접적 필요 X, 분석을 위해 추출해야 하는 데이터 : 로그 데이터 업무 데이터 서비스와 시스템을 운용하기 위한 목적으로 구축된 데이터베이스에 존재하는 데이터 대부분 갱신형 데이터(가격 변경 등 이벤트 발생 시 삽입 X → 기존의 데이터를 갱신) 트랜잭션 데이터, 마스터 데이터로 분류 트랜잭션 데이터 구매 데이터, 리뷰 데이터와 같은 서비스와 시스템을 통해 사용자의 행동을 기록한 데이터 날짜, 시각, 마스터 데이터의 회원 ID, 상품 ID 포함되는 경우가 많음 이걸 기반으로 리포트 만드는 경우가 많음 → 바로 추출 X, 마스터 데이터 필요 마스터 데이터 카테고리 마스터, 상품 마스터처럼 서비스와 시스템이 정의하고 있는 데이터 트랜잭션 데이터에는 ..