[PostgreSQL] 2. 연령별 구분 집계

연령별 구분 집계

시청률 분석에 많이 사용되는 연령별 구분을 집계해본다.

다음 표를 기반으로 사용자의 연령별 구분을 집계한다.

 

데이터는 다음과 같다.

---

사용자 나이 구하기

나이는 생일과 특정 날짜를 정수로 표현하고, 이 차이를 10,000으로 나누는 방법으로 간단히 구할 수 있다.

1. 먼저 특정 날짜를 정수로 표현한다(기준 날짜)
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
)
SELECT *
FROM mst_users_with_int_birth_date

 

3. 기준 날짜와 사용자 생년월일(정수형 변환 값)의 차이를 10000으로 나누고 내림하여 나이를 계산한다.

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
)
SELECT 
	user_id, sex, birth_date, age
FROM mst_users_with_age
;

 

---

성별과 연령으로 연령별 구분 계산

• 위의 쿼리를 활용하여 mst_users_with_category 테이블을 작성한다.
• CASE WHEN으로 연령 구분을 20세 기준으로 먼저 한 다음, 20살 이상은 남/여(M or F) 구분이 되게 하였고
• 또 한 번 CASE WHEN을 사용하여 연령대에 따라 C, T, 1, 2와 같은 값을 매칭하였다.
• 그리고 이 두 값을 CONCAT()으로 연결한다.

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 *
FROM mst_users_with_category
;

 

 

 

성별과 연령으로 연령별 구분 >> 각 그룹의 숫자 계산

• 상단 쿼리는 동일하고, category 별로 그룹화하여 count(1)을 하여 각 그룹에 속한 사용자수를 구할 수 있다.

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 category
	, count(1) AS user_count
FROM mst_users_with_category
GROUP BY category
;