[MySQL] 생년월일 데이터 다루기 > 연령대별 변수 생성

안녕하세요.

 

오늘은 생년월일이 포함된 데이터 테이블에서 MySQL을 활용하여 연령대로 구분하는 쿼리를 소개하고자 포스팅하게 되었습니다. 생년월일 등 날짜 데이터는 우리가 흔히 만날 수 있는 데이터 타입으로 보는 사람의 입맛에 맞게 잘 다룰 줄 알아야 합니다.

 

또한, 집단별로 원하는 변수를 추출, 생성하는 query는 sql의 기본 중에 기본이므로 잘 알아둬야 하므로 샘플 데이터와 문제를 만들어보았습니다.

 

※ 간단한 SQL 쿼리 예시를 보여드리기 위해 자주 애용하는 SQL Test site를 사용했습니다.

SQL Test Site :  http://sqlfiddle.com

▶ Sample Data

* 해당 Sample Data는 제가 임의로 만든 값이므로 큰 의미가 없습니다.

Table : emp

# 데이터 테이블 emp 생성
CREATE TABLE emp
    (`empno` int, `ename` varchar(6), `birth` datetime, `job` varchar(8), `salary` int)
;
    
INSERT INTO emp
    (`empno`, `ename`, `birth`, `job`, `salary`)
VALUES
    (7902, 'Ford', '1965-04-15 00:00:00', 'Salesman', 7400),
    (7788, 'Hoon', '1994-12-16 00:00:00', 'Clerk', 3500),
    (8864, 'Shen', '1985-03-12 00:00:00', 'Clerk', 3700),
    (8411, 'Blake', '1981-01-01 00:00:00', 'Manager', 5300),
    (9417, 'Suzy', '1975-04-02 00:00:00', 'Manager', 5800),
    (2414, 'Jones', '1971-02-17 00:00:00', 'Manager', 6000),
    (4412, 'Martin', '1968-05-22 00:00:00', 'Manager', 6400),
    (2411, 'Carry', '1970-06-27 00:00:00', 'Senior', 7100),
    (5410, 'Kane', '1998-01-27 00:00:00', 'Junior', 3100),
    (5274, 'Jerry', '1968-04-15 00:00:00', 'Senior', 7500),
    (6384, 'Betty', '1960-01-16 00:00:00', 'Master', 9800),
    (1274, 'Lee', '1961-12-17 00:00:00', 'Master', 10900),
    (5274, 'Sony', '1978-11-01 00:00:00', 'Senior', 7800),
    (5274, 'Han', '1988-12-31 00:00:00', 'Manager', 5100)

 

▶ Problem : 임직원들의 연령대별 직원수 및 평균 월급 구하기

<MySQL Query>

# 생년월일 전처리 후 연령대별 평균 월급, 임직원 수 구하기
SELECT CASE
        WHEN age < 30 THEN '20대'
        WHEN age < 40 THEN '30대'
        WHEN age < 50 THEN '40대'
        WHEN age < 60 THEN '50대'
        WHEN age < 70 THEN '60대'
       END AS age_group,
       AVG(salary) average_salary,
       count(*) total
FROM (SELECT *, FLOOR(date_format(now(), '%Y')-substring(birth,1,4)) age FROM emp) a
GROUP BY age_group

<쿼리 풀이>

먼저, 연령대별로 나이를 나눠야하기에 생년월일에 대한 전처리를 서브쿼리로 진행했습니다.

substring으로 연도만 추출하여 나이를 계산해주었습니다.

해당 subquery table을 바탕으로 CASE WHEN 구문으로 20대부터 60대까지 연령대(age_group)를 구분해주었고

GROUP BY문으로 연령대별 평균 월급과 직원수를 SELECT하는 쿼리를 완성했습니다.

 

<결과>

age_group	average_salary	total
20대	3300	2
30대	4400	2
40대	6300	3
50대	6880	5
60대	10350	2