[MYSQL] 쿼리문으로 연령대별 인원수 평균점수 구하기

MYSQL 쿼리문을 활용하여 연령대별로 회원의 인원수와 점수의 평균을 계산하는 것은 데이터 분석에 매우 유용한 작업입니다. 여러분이 진행한 쿼리문 수정 과정은 이러한 데이터를 추출하는 데 필요한 중요한 단계를 잘 보여줍니다. 아래에서는 연령대별 인원수와 평균 점수를 계산하기 위한 최종 쿼리문을 제시하고 설명하겠습니다.

 

 

연령대별로 구하기

연령대별로 구하는건 오늘 날짜에서 생일의 연도를 빼는 걸로 간단하게 구현해보도록 하겠습니다.

그런 다음 /10 을 해서 그룹으로 묶으면 연령대별로 생성이 가능합니다. 

이 때 평균을 내는 쿼리문에서 사용하는 함수는 "AVG"입니다.

SELECT
     FLOOR((date_format(now(),'%Y')-substring(birth,1,4))/10)*10 as age, 
     COUNT(*) AS total, 
     round(AVG(sum1),2) AS avg
   FROM table
   GROUP BY age

위의 쿼리문을 실행해 보면 아래와 같은 결과가 나옵니다.

age	total	avg
-10	3	1.3
10	8	5
20	6	3
40	5	4
2010	4	1.5

-10살과 2010살이 나오네요. 이부분에 대한 오류는 왠지 생일의 값을 잘못입력되었을 경우일거 같습니다. 값이 잘못 입력이 되지 않도록 스크립트에서 에디트체크를 잘 해줘야겠네요.

 

조금 더 수정을 해보았습니다. age에 범위를 줘서 0~150살까지 출력해보도록 하겠습니다. (지금 태어난 아이들은 150살까지 산다고 라디오광고에서 말하네요.)

SELECT * from  
  (SELECT
     FLOOR((date_format(now(),'%Y')-substring(birth,1,4))/10)*10 as age, 
     COUNT(*) AS total, 
     round(AVG(sum1),2) AS avg
   FROM table
   GROUP BY age 
  ) S
 WHERE S.age>=0 AND S.age<=150

위의 쿼리문을 실행해 보면 아래와 같은 결과가 나옵니다.

age	total	avg
10	8	5
20	6	3
40	5	4

from 절에서 select 를 다시 해서 age를 where로 사용할 수 있도록 수정해주었습니다. 

 

모든연령대 출력하기

이번엔 전체 연령을 순차적으로 돌면서 출력해보도록 하겠습니다. n을 -10 부터 시작해서 10씩 더해서 16번을 돌리면 0부터 150까지 숫자 생성하고 LEFT JOIN으로 위에서 했던 구문을 넣어주었습니다.

 SELECT *
 FROM 
   (
     SELECT @N := @N +10 AS n from table, 
     (select @N:=-10 from DUAL ) NN LIMIT 16
   ) AS T
 LEFT JOIN
 (
   SELECT 
     FLOOR((date_format(now(),'%Y')-substring(birth,1,4))/10)*10 as age, 
	   COUNT(*) AS total, 
	   round(AVG(sum1),2) AS avg
     FROM table 
     GROUP BY age 
  ) S
  ON T.n = S.age

위의 쿼리문을 실행해 보면 아래와 같은 결과가 나옵니다.

n	age	total	avg
0	(NULL)	(NULL)	(NULL)
10	10	8	5
20	20	6	3
30	(NULL)	(NULL)	(NULL)
40	40	5	4
50	(NULL)	(NULL)	(NULL)
60	(NULL)	(NULL)	(NULL)
70	(NULL)	(NULL)	(NULL)
80	(NULL)	(NULL)	(NULL)
90	(NULL)	(NULL)	(NULL)
100	(NULL)	(NULL)	(NULL)
110	(NULL)	(NULL)	(NULL)
120	(NULL)	(NULL)	(NULL)
130	(NULL)	(NULL)	(NULL)
140	(NULL)	(NULL)	(NULL)
150	(NULL)	(NULL)	(NULL)

이 쿼리문에서 (NULL) 값은 첫줄인 select * 에서 IFNULL 절로 해결해주면 0이 출력되는 좀 더 이쁜 결과가 출력될 것 같습니다.

 

최종쿼리문

SELECT 
  IFNULL(T.n, 'No Data') as age_range,
  IFNULL(S.total, 0) AS total,
  IFNULL(S.avg, 0) AS avg_score
FROM 
  (SELECT @N := @N + 10 AS n 
   FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1, 
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2, 
   (SELECT @N:=-10) NN 
   LIMIT 16) AS T
LEFT JOIN
  (SELECT 
     FLOOR((YEAR(CURDATE()) - YEAR(birth))/10)*10 as age, 
     COUNT(*) AS total, 
     ROUND(AVG(sum1), 2) AS avg
   FROM your_table
   GROUP BY age 
  ) S ON T.n = S.age
WHERE T.n >= 0 AND T.n <= 150
ORDER BY T.n;

 

1. 임시 테이블 생성

0부터 150까지의 연령대를 생성하기 위해 UNION과 LIMIT을 사용하여 숫자 시퀀스를 생성합니다.

2. LEFT JOIN

생성된 연령대 시퀀스와 연령대별 계산 결과를 LEFT JOIN으로 연결합니다.

이렇게 하면 모든 연령대에 대한 데이터가 표시됩니다.

3. 연령대 계산

연도의 차이를 이용하여 연령대를 계산하고, 10으로 나누어 각 연령대 그룹을 만듭니다.

4. IFNULL 처리

데이터가 없는 연령대에 대해 NULL 대신 'No Data', 0 또는 기본값을 표시합니다.

5. 결과

• age_range: 연령대를 나타냅니다. 데이터가 없는 경우 'No Data'로 표시됩니다.
• total: 해당 연령대의 총 인원수를 나타냅니다.
• avg_score: 해당 연령대의 평균 점수를 나타냅니다.

 

최종쿼리문은 아래의 파일을 다운받으시면 됩니다.

MYSQL 쿼리문 연령대별 인원수 평균점수 구하기.zip

0.00MB

 

결론

이 쿼리문은 연령대별로 구성된 인원수와 평균 점수를 체계적으로 나타내는 데 도움이 됩니다.

'your_table'과 'birth', 'sum1' 같은 필드 이름은 실제 데이터베이스의 테이블과 컬럼 이름에 맞게 조정해야 합니다.

이 쿼리문을 활용하여 다양한 연령대의 회원 데이터를 효과적으로 분석하고 관리할 수 있습니다.