SQL 실무 : 나이, 연령대, 연령대별 회원수 계산

 

오늘 살펴볼 내용은 CRM 업무를 담당하거나 고객 데이터를 분석해야 할 때 자주 쓰이는 고객의 나이, 연령대, 연령대별 인원 계산입니다. 기본적인 SQL 문법과 간단한 서브쿼리를 이용하면 매우 간단하게 계산할 수 있고, 연령대별 분석은 실무에서 매우 자주 사용되는 만큼 알아두면 굉장히 유익할 것 입니다.

 

아래와 같이 고객의 ID, 성별, 생년월일, 회원가입일, 가입경로의 정보가 포함되어 있는 고객 데이터가 있다고 가정하겠습니다. 우리는 여기서 생년월일(birth_date) 컬럼을 이용해 나이, 연령대 분석을 해보도록 하겠습니다.

 

고객 데이터(예제)

 

user_id	sex	birth_date	register_date	register_device
U001	M	1977-06-17	2016-10-01	pc
U002	F	1953-06-12	2016-10-01	sp
U003	M	1965-01-06	2016-10-01	pc
U004	F	1954-05-21	2016-10-05	pc
U005	M	1987-11-23	2016-10-05	sp
U006	F	1950-01-21	2016-10-10	pc

 

1. 생년월일을 활용한 회원 나이 계산

 

SELECT user_id, sex, birth_date, date_format(now(), '%Y') - date_format(birth_date, '%Y') AS age
FROM mst_users;

 

date_format() 함수를 이용해 현재 년도에서 각 회원들의 출생년도를 빼주면 아래와 같이 나이를 계산해주는 쿼리가 실행 됩니다.

 

 

✏️ 실행결과

 

user_id	sex	birth_date	age
U001	M	1977-06-17	45
U002	F	1953-06-12	69
U003	M	1965-01-06	57
U004	F	1954-05-21	68
U005	M	1987-11-23	35
U006	F	1950-01-21	72

 

2. 회원 나이를 기준으로 연령대 분류

 

SELECT min(age) AS min_age, max(age) max_age
FROM(
     SELECT user_id, sex, birth_date, date_format(now(), '%Y') - date_format(birth_date, '%Y') AS age
     FROM mst_users
) AS c;

 

회원들의 연령대를 계산하기 전에 먼저 회원들 중 최연장자와 최연소자의 나이가 몇 살인지 확인해 연령대를 어떤 기준으로 잡을지 판단하겠습니다. 

 

✏️ 실행결과

 

min_age	max_age
13	72

 

데이터 상 20대 미만 회원도 있기 때문에 연령대를 10대 부터 계산하고 60세가 넘어가는 회원들은 모두 60대 이상으로 분류해주겠습니다.

 

SELECT *
	 , CASE WHEN age < 20 THEN '10대'
		WHEN age BETWEEN 20 AND 29 THEN '20대'
                WHEN age BETWEEN 30 AND 39 THEN '30대'
		WHEN age BETWEEN 40 AND 49 THEN '40대'
		WHEN age BETWEEN 50 AND 59 THEN '50대'
		WHEN age >= 60 THEN '60대 이상'
            END AS age_group
FROM(
     SELECT user_id, sex, birth_date, date_format(now(), '%Y') - date_format(birth_date, '%Y') AS age
     FROM mst_users
    ) AS c;

 

✏️ 실행결과

 

user_id	sex	birth_date	age	age_group
U001	M	1977-06-17	45	40대
U002	F	1953-06-12	69	60대 이상
U003	M	1965-01-06	57	50대
U004	F	1954-05-21	68	60대 이상
U005	M	1987-11-23	35	30대
U006	F	1950-01-21	72	60대 이상
U007	F	1950-07-18	72	60대 이상
U008	F	2006-12-09	16	10대
U009	M	2004-10-23	18	10대
U010	F	1987-03-18	35	30대
U011	F	1993-10-21	29	20대
U012	M	1993-12-22	29	20대
U013	M	1988-02-09	34	30대
U014	F	1994-04-07	28	20대
U015	F	1994-03-01	28	20대
U016	F	1991-09-02	31	30대
U017	F	1972-05-21	50	50대
U018	M	2009-10-12	13	10대
U019	M	1957-05-18	65	60대 이상
U020	F	1954-04-17	68	60대 이상
U021	M	2002-08-14	20	20대
U022	M	1979-12-09	43	40대
U023	M	1992-01-12	30	30대
U024	F	1962-10-16	60	60대 이상
U025	F	1958-06-26	64	60대 이상
U026	M	1969-02-21	53	50대
U027	F	2001-07-10	21	20대
U028	M	1976-05-26	46	40대
U029	M	1964-04-06	58	50대
U030	M	1959-10-07	63	60대 이상

 

3. 연령대별 회원수 계산

 

SELECT CASE WHEN age < 20 THEN '10대'
            WHEN age BETWEEN 20 AND 29 THEN '20대'
            WHEN age BETWEEN 30 AND 39 THEN '30대'
            WHEN age BETWEEN 40 AND 49 THEN '40대'
            WHEN age BETWEEN 50 AND 59 THEN '50대'
            WHEN age >= 60 THEN '60대 이상'
            END AS age_group
	 , COUNT(*) total_cnt
FROM(
     SELECT user_id, sex, birth_date, date_format(now(), '%Y') - date_format(birth_date, '%Y') AS age
     FROM mst_users
    ) AS c
GROUP BY age_group
ORDER BY age_group;

 

회원들의 연령대를 계산했다면 그룹으로 묶어 연령대별로 몇 명이 존재하는지 확인해볼 수 있는 쿼리 입니다. CASE 조건문을 활용하고, age_group으로 GROUP BY()를 사용해주면 연령대별로 집계가 가능해집니다.

 

✏️ 실행결과

 

age_group	total_cnt
10대	3
20대	6
30대	5
40대	3
50대	4
60대 이상	9

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.