중복된 ID가 여러 번 등록되어 있는 데이터에서 각 ID별 평균값을 구하려면 AVG() 함수를 사용하면 됩니다. 이후 GROUP BY로 ID를 그룹화하고, 필요에 따라 HAVING 절로 조건을 걸어 원하는 결과만 추출할 수 있습니다.
이 글에서는 플레이어별 점수 데이터를 예시로, 중복 ID의 평균 점수를 계산하고 최대 평균을 가진 ID를 조회하는 방법을 단계별로 살펴보겠습니다.
1. 테이블 생성하기
먼저 플레이어 ID와 점수를 저장할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> PlayerId int,
-> PlayerScore int
-> );
Query OK, 0 rows affected (0.55 sec)
2. 샘플 데이터 삽입하기
INSERT 명령어를 사용해 일부러 중복된 ID(1번, 2번)가 포함된 레코드를 삽입합니다.
mysql> insert into DemoTable values(1,78);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(2,82);
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable values(1,45);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(3,97);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values(2,79);
Query OK, 1 row affected (0.12 sec)
3. 전체 레코드 확인하기
SELECT 문으로 테이블의 모든 데이터를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+----------+-------------+
| PlayerId | PlayerScore |
+----------+-------------+
| 1 | 78 |
| 2 | 82 |
| 1 | 45 |
| 3 | 97 |
| 2 | 79 |
+----------+-------------+
5 rows in set (0.00 sec)
위 데이터를 보면 1번과 2번 플레이어는 각각 두 개의 점수를 가지고 있고, 3번 플레이어는 하나의 점수만 가지고 있습니다.
4. 평균 점수가 특정 값 이상인 ID 조회하기
GROUP BY로 플레이어 ID를 묶은 뒤, HAVING 절에서 AVG() 함수의 결과에 조건을 적용하면 평균 점수가 80을 초과하는 ID만 조회할 수 있습니다.
mysql> select PlayerId from DemoTable
-> group by PlayerId
-> having avg(PlayerScore) > 80;
실행 결과는 다음과 같습니다.
+----------+
| PlayerId |
+----------+
| 2 |
| 3 |
+----------+
2 rows in set (0.00 sec)
2번 플레이어의 평균은 (82 + 79) / 2 = 80.5점, 3번 플레이어의 평균은 97점이므로 두 ID 모두 조건을 만족합니다.
5. 최대 평균값을 가진 ID만 조회하기
조건 필터링이 아니라 가장 높은 평균점을 기록한 단 하나의 ID만 얻고 싶다면, ORDER BY와 LIMIT을 함께 사용하는 것이 가장 간단합니다.
mysql> select PlayerId, avg(PlayerScore) as AvgScore from DemoTable
-> group by PlayerId
-> order by AvgScore desc
-> limit 1;
실행 결과:
+----------+----------+
| PlayerId | AvgScore |
+----------+----------+
| 3 | 97.0000 |
+----------+----------+
1 row in set (0.00 sec)
이처럼 AVG()로 평균을 구하고, GROUP BY로 중복 ID를 그룹화한 다음, HAVING 또는 ORDER BY + LIMIT을 활용하면 최대 평균과 관련된 다양한 형태의 결과를 손쉽게 조회할 수 있습니다.