본문 바로가기

SQL71

[PostgreSQL] 5. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기 Decile 분석 사용자 특징 분석시 성별과 연령 등의 데이터가 있다면 이러한 속성에 따른 특징을 확인할 수 있다. 데모그래픽한 데이터가 존재하지 않는 경우, 사용자 액션으로 속성을 정의해 보는 것도 좋다. 데이터를 10단계로 분할해서 중요도를 파악하는 것을 'Decile 분석'이라고 한다. 여기서는 사용자의 구매 금액에 따라 순위를 구분하고 중요도를 파악하는 리포트를 만들어본다. 📢 단, Decile 분석은 데이터 검색 기간에 따라 문제가 있다. 검색 기간이 너무 장기간이면 과거의 우수 고객이었지만, 현재는 휴면인 고객이 포함될 가능성이 있다. 반대로 검색 대상이 단기간이라면 정기적으로 구매하는 안정 고객이 포함되지 않고, 해당 기간 동안에만 일시적으로 많이 구매한 사용자가 우수 고객으로 취급 될 수 .. 2024. 1. 30.
[PostgreSQL] 4. 사용자의 방문 빈도 집계하기 사용자가 일주일 또는 한 달 동안 서비스를 얼마나 쓰는지 알면 분석에 큰 도움이 된다. 이번엔 '서비스를 한 주 동안 며칠 사용하는 사용자가 몇 명인지' 집계하는 방법을 알아본다. 👆 위 데이터를 바탕으로 한 주 동안 매일 몇 명의 사용자가 방문했는지 파악하고 구성비누계도 계산한다. 한 주 동안 며칠 방문했는지 사용자 수를 집계 먼저 timestamp에서 날짜만 추출한다. 유저 별로, 일주일 동안 며칠 방문했는지 count 한다. (Where 조건과, count(distinct) 사용) 카운트 된 일 수(방문 일 수) 기준으로 유저 아이디 count를 한다. 👉 결과 예로, 7일 중 2일 방문한 사용자가 2명이다. WITH action_log_with_dt AS ( SELECT * , substring(.. 2024. 1. 29.
[PostgreSQL] 3. 연령별 특징 추출하기 2에서 사용한 연령별 구분을 기반으로 각각 구매한 상품의 카테고리를 집계해보자. 연령과 나이를 구분하여 카테고리를 만드는 것까지는 이전 포스팅과 동일하다. WITH mst_users_with_int_birth_date AS ( SELECT * , 20170101 AS int_specific_date ,CAST(replace(substring(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date FROM mst_users ) , mst_users_with_age AS ( SELECT * , floor((int_specific_date - int_birth_date)/10000) AS age FROM mst_users_with_int_birth_date ).. 2024. 1. 29.
[PostgreSQL] 2. 연령별 구분 집계 연령별 구분 집계 시청률 분석에 많이 사용되는 연령별 구분을 집계해본다. 다음 표를 기반으로 사용자의 연령별 구분을 집계한다. 데이터는 다음과 같다. 사용자 나이 구하기 나이는 생일과 특정 날짜를 정수로 표현하고, 이 차이를 10,000으로 나누는 방법으로 간단히 구할 수 있다. 먼저 특정 날짜를 정수로 표현한다(기준 날짜) 사용자 생년월일(문자형)을 다음과 같이 정수 표현으로 반환한다. WITH mst_users_with_int_birth_date AS ( SELECT * , 20170101 AS int_specific_date ,CAST(replace(substring(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date FROM mst_users .. 2024. 1. 29.
[PostgreSQL] 1. 사용자의 액션 수 집계 사용자 데이터 서비스를 제공하는 측에서 사용자와 관련된 정보로 알고 싶은 것을 정리해보면 다음과 같다. 사용자의 속성(나이, 성별, 주소지 등) 사용자의 행동(구매한 상품, 사용한 기능, 사용하는 빈도) 👉 어떤 속성의 사용자가 사용중인가?와 어떻게 사용하는가?를 파악하지 않고 서비스 개선을 검토할 수는 없다! 사용자 마스터 테이블 일반적으로 이커머스 사이트는 가입한 뒤, 로그인을 하고 사용한다. 따라서 사용자 정보를 저장하는 '사용자 마스터 테이블'이 존재한다. 예시는 다음과 같다. 액션 로그 테이블 일반적으로 서비스와 관련된 업무 데이터가 저장된 데이터베이스에 관심 상품 등록, 카트 추가, 구매, 댓글 등 각각의 테이블이 있을 것이다. 그래도 다음과 같이 액션 로그 테이블을 따로 만들어 내부에 별도 .. 2024. 1. 29.
[PostgreSQL] 히스토그램용 데이터 가공(width_bucket) 히스토그램 차트를 위한 데이터 전처리 상품 가격대별로 히스토그램을 그려하기 때문에, 가격의 상한/하한 값을 구하고 그 차이를 계산한다. WITH stats AS ( SELECT max(price) AS max_price , min(price) AS min_price , max(price) - min(price) AS price_range , 10 AS bucket_num FROM purchase_detail_log ) SELECT * FROM stats ; 2. 상한값에 + 1 을 하여 최댓값도 구간에 포함되도록 한다. 3. 각 가격에서 min_price를 뺀 값을 Diff로 구한다. 4. 계층 범위는 가격 구간을 구간 개수로 나누어 구한다. 5. (diff / 계층 범위) 결과를 내림하면 값의 상대적인.. 2024. 1. 27.
[PostgreSQL] 팬 차트로 매출 증가율 확인(FIRST VALUE 함수) 팬차트란? 팬차트 : 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 하는 그래프이다. 계절 변동이 적은 평균적인 달을 기준으로 하는 것이 좋다. 기준 달에 따라 결과가 달라지므로 근거를 잘 생각하고 작성한다. 팬차트 작성을 위해 필요한 데이터를 가공해보자. 데이터가 다음과 같이 정리되어야 팬차트를 만들 수 있다. 먼저 날짜에서 연/월/일을 추출하고 일자별 매출을 합산한다. 일자별 매출을 토대로, 연/월을 기준으로 카테고리별 매출을 다시 합산한다. 윈도우 함수로, FIRST VALUE를 사용하여 기준 연/월, 카테고리별 매출 값을 구한다. 기준점 대비 비율을 구하기 위해 100 * 연/월 매출 / 기준점 매출을 계산한다. 마지막으로, 연/월과 카테고리로 order by 한다. WI.. 2024. 1. 27.
[PostgreSQL] 카테고리별 매출 총계와 소계 / ABC 분석 매출에 시계열뿐만 아니라 상품의 카테고리, 가격 등을 조합해서 데이터의 특징을 추출해 리포팅한다. 1. 카테고리별 매출(총계)과 소계 계산하기 🎯 대분류(카테고리) > 소분류(하위 카테고리) > 매출 순으로 계산되게 한다. 🎯 작은 단위로 집계한 후 >> UNION ALL 구문을 사용 WITH sub_category_amount AS ( -- 소 카테고리 매출 집계 SELECT category AS category , sub_category AS sub_category , SUM(price) AS amount FROM purchase_detail_log GROUP BY category , sub_category ), -- 카테고리 매출 집계 category_amount AS ( SELECT categor.. 2024. 1. 26.
[PostgreSQL] 매출을 파악하기 위한 데이터 추출2 (종합 쿼리) ▶︎ 연/월별로 다음 데이터를 뽑는 종합 쿼리 판매 횟수 평균 구매액 매출액 누계 매출액 작년 (동월) 매출액 작년 (동월) 비율 with daily_purchase as ( select dt , substring(dt, 1, 4) as year , substring(dt, 6, 2) as month , substring(dt, 9, 2) as date , sum(purchase_amount) as purchase_amount , count(order_id) as orders from purchase_log group by 1 ), monthly_purchase as( select year , month , sum(orders) as orders , avg(purchase_amount) as avg_am.. 2024. 1. 26.
728x90

티스토리툴바