[SQL 기초] 최빈값 찾아내기

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

---

이번 포스팅에서는 특정 칼럼에서 그룹별로 가장 빈번하게 등장한 값(최빈값)을 찾아내는 함수를 알아보겠습니다. 최빈값은 우리가 흔히 계산하는 값이기도 하고 직관적인 개념인데.. SQL로 이를 구현하려면 생각보다는 까다로운 편인 것 같습니다. 일단 가장 간단한 집계함수로 찾아내는 것을 알아보겠습니다. 안타깝게도 이 집계함수는 Oracle에서만 사용이 가능합니다. 

 

1. STATS_MODE 함수 활용하기

아래와 같은 데이터가 주어졌다고 해보겠습니다. 

 

ID	CATEGORY
1	A
1	B
1	A
2	C
2	C
2	D

 

Oracle에서는 Max, Min, Count와 같은 집계함수들 말고도 STATS_MODE라는 집계함수가 있고 최빈값을 반환하는 함수입니다. (다른 함수들과 동일하게 NULL값은 무시하고 집계합니다) 쿼리를 아래와 같이 간단하게 작성하면 바로 최빈값을 찾을 수 있습니다. 

 

SELECT ID, STATS_MODE(category) AS most_frequent_category
FROM your_table
GROUP BY ID;

 

ID	Most Frequent Category
1	A
2	C

 

 

2. Oracle외 기타 DBMS에서 최빈값 찾기

어쩔 수 없이 서브쿼리를 한번 사용해서 찾아내야 합니다. 

SELECT ID, category AS most_frequent_category
FROM (
    SELECT 
        ID,
        category,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COUNT(*) DESC) AS rn
    FROM your_table
    GROUP BY ID, category
) t
WHERE rn = 1;

 

위 코드의 실행 순서를 정리하면 이렇습니다. 

 

• GROUP BY ID, category:
  • your_table에서 각 ID와 category 조합의 빈도를 계산합니다.
  • 결과적으로 ID와 category별로 그룹화된 행과 각 그룹의 개수(빈도)가 생성됩니다.
• ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COUNT(*) DESC):
  • GROUP BY의 결과 집합에 대해 윈도우 함수 ROW_NUMBER()가 적용됩니다.
  • PARTITION BY ID는 각 ID별로 순위를 매기도록 지정합니다.
  • ORDER BY COUNT(*) DESC는 빈도의 내림차순으로 순위를 매기도록 지정합니다.
  • 결과적으로 각 ID와 category 조합에 대한 순위가 rnk 열에 추가됩니다.
• WHERE rnk = 1:
  • rnk 열의 값이 1인 행, 즉 각 ID별로 가장 빈번하게 나타나는 category를 선택합니다.
• SELECT ID, category AS most_frequent_category:
  • 선택된 행의 ID와 category 값을 결과로 반환합니다.

 

 

참고) 2024.11.03 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL 기초] Window함수

---

제가 처음 "최빈값 찾아내기"을 생각했을 때, '이거 Group by 해서 적당히 풀면 복잡하게 안해도 되는거 아닌가?' 라고 생각했었는데요. 아마 그 때 저는 대강 아래와 같은 쿼리를 머릿속에서 그렸던 것 같습니다.  

SELECT category, COUNT(*) 
FROM your_table
GROUP BY category
HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) cnt FROM your_table GROUP BY category));

 

그런데 위와 같은 쿼리에는 문제가 있습니다. 

Having은 GROUP BY로 묶인 현재 그룹의 집계 결과만 참조 가능합니다. 뒤에 달라붙어 있는 sub-query는 참조할 수 없습니다. 따라서 직관적으로는 꽤나 쉬운 개념인 최빈값을 (Oracle의 Stats_mode를 제외하면) 서브쿼리를 도입해서 다소 복잡하게 푸는 방법밖에 없는 것입니다.