[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
)
, mst_users_with_category AS (
	SELECT user_id
		, sex
		, age
		, concat(
			CASE WHEN 20 <= age THEN sex
			ELSE ''
			END 
		, CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
		 	  WHEN age BETWEEN 13 AND 19 THEN 'T'
		 	  WHEN age BETWEEN 20 AND 34 THEN '1'
		 	  WHEN age BETWEEN 35 AND 49 THEN '2'
		 	  WHEN age >= 50 THEN '3' END
		) AS category
	FROM mst_users_with_age
)
-- 여기서부터 집계 쿼리 
SELECT p.category AS product_category
	, u.category AS user_category
	, count(*) AS purchase_count
FROM action_log AS p
JOIN mst_users_with_category AS u ON p.user_id = u.user_id
WHERE ACTION = 'purchase'
GROUP BY p.category, u.category
ORDER BY p.category, u.category
;

 

👉 이러한 카테고리 분석시 ABC 분석과 구성비누계를 리포트에 추가하면 전달성을 향상시킬 수 있다.