티스토리 뷰
안녕하세요. 요즘 IBM 커뮤니티에서 열심히 댓글놀이를 하고 있는데요. 며칠전에 Informix에서 문자열에 대한 최빈값(MODE)을 구하는 방법에 대한 질문글이 올라왔습니다. 최빈값은 통계학에서 쓰는 용어로 액셀이나 분석 솔루션에서 MODE라는 함수로 사용되기도 합니다. 아래는 위키백과의 최빈값에 대한 설명입니다.
|
최빈값(最頻-), 모드(mode)는 통계학 용어로, 가장 많이 관측되는 수, 즉 주어진 값 중에서 가장 자주 나오는 값이다. 예를 들어, {1, 3, 6, 6, 6, 7, 7, 12, 12, 17}의 최빈값은 6이다. 최빈값은 산술 평균과 달리 유일한 값이 아닐 수도 있다. 또한 주어진 자료나 관측치의 값이 모두 다른 경우에는 존재하지 않는다. |
그런데 최빈값이라 함은 숫자가 대상이지 문자열은 아닙니다. 그리고 SQL에서는 MODE라는 함수가 따로 제공되는 것도 아니어서 문자열을 대상으로 하려면 별도의 로직을 구현해야합니다.
질문에서 제시하는 샘플데이터와 원하는 결과세트는 아래와 같습니다.
<샘플데이터>
| Name | dob | score |
| JESSE | 1992/10/27 | 10 |
| JESSE | 1992/10/27 | 20 |
| JESSE | 1992/11/06 | 30 |
| JESSE | 1992/11/11 | 40 |
| JESSICA | 1992/03/11 | 50 |
| JESSICA | 1992/11/03 | 60 |
| JESSICA | 1992/10/29 | 70 |
| JESSICA | 1992/11/10 | 80 |
| JESSICA | 1992/11/30 | 90 |
| JESSICA | 1992/11/12 | 10 |
| JESSICA | 1992/12/07 | 20 |
| JESSICA | 1992/12/09 | 30 |
<결과세트>
| Name | dob | avg(score) |
| JESSE | 1992/12/07 | |
| JESSICA | 1992/11/?? |
결과세트에서 요구하는 사항은 다음과 같습니다.
1) 이름별로 가장 연도와 월 기준으로 가장 많은 값을 찾는다.
2) 일자는 해당 월의 아무 일자나 허용한다.
3) 1에서 찾은 값의 개수가 같은 것이 있으면 연도, 월, 일자를 같은 값을 우선한다.
그래서 이리저리 검색을 해서 아래와 같은 쿼리를 만들었습니다.
WITH t1 AS
(
SELECT 'JESSE' Name, '1992/10/27' dob, 10 score
UNION ALL SELECT 'JESSE', '1992/10/27', 20
UNION ALL SELECT 'JESSE', '1992/11/06', 30
UNION ALL SELECT 'JESSE', '1992/11/11', 40
UNION ALL SELECT 'JESSICA', '1992/03/11', 50
UNION ALL SELECT 'JESSICA', '1992/11/03', 60
UNION ALL SELECT 'JESSICA', '1992/10/29', 70
UNION ALL SELECT 'JESSICA', '1992/11/10', 80
UNION ALL SELECT 'JESSICA', '1992/11/30', 90
UNION ALL SELECT 'JESSICA', '1992/11/12', 10
UNION ALL SELECT 'JESSICA', '1992/12/07', 20
UNION ALL SELECT 'JESSICA', '1992/12/09', 30
)
SELECT a.Name,
a.dob,
a.avg
FROM (SELECT t1.Name,
t1.dob,
ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn,
AVG(t1.score) OVER (PARTITION BY t1.Name) avg
FROM t1,
(SELECT t1.Name,
t1.dob[1,7],
RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
FROM t1
GROUP BY t1.Name, t1.dob[1,7]
) t2
WHERE t2.rk = 1
AND t1.Name = t2.Name
AND t1.dob like t2.dob||'%'
GROUP BY t1.Name, t1.dob, t1.score
) a
WHERE rn =1쿼리의 흐름은 다음과 같습니다.
1) 이름을 기준으로 연도,월별 개수가 가장 많은 것을 구합니다. 가장 많은 개수가 같을 경우를 고려해 RANK 함수를 사용합니다. 그러면 같은 랭킹인 경우는 모두 첫번째가 됩니다.
2) 1에서 구한 데이터를 기준으로 일자를 포함하여 개수가 가장 많은 것을 구합니다. 여기선 월별로 일자 하나만 선택해야하므로 ROW_NUMBER를 사용합니다.
아래는 실행한 결과입니다.
[informix@db2 skjeong]$ dbaccess stores_demo mode.sql
Database selected.
name dob avg
JESSE 1992/10/27 25.0000000000000
JESSICA 1992/11/30 60.0000000000000
2 row(s) retrieved.
인포믹스에서의 예제이긴 하지만 대부분의 RDBMS에서 지원하는 함수이기 때문에 필요한 경우 적용하기는 어렵지 않으실겁니다. 혹시 이 글을 읽는 분들께서 더 나은 방법을 알고 계시다면 조언 부탁드리겠습니다.
제가 참고한 사이트는 아래와 같습니다.
https://www.mssqltips.com/sqlservertip/3543/calculate-the-statistical-mode-in-sql-server-using-tsql/
데이터베이스 사랑넷에 자문을 구해 쿼리를 수정해보고, 새로운 쿼리도 알게 됐습니다.
위의 요구사항보다 조건을 좀 더 구체적으로 설정했습니다.
1) 평균은 월평균이 되어야한다. 이전 것은 이름만 기준으로한 평균
2) 가장 많은 갯수가 동일할 경우의 우선순위는? (빠른 날짜, 느린 날짜, 낮은 평균, 높은 평균 등)
여기서는 빠른 날짜를 기준으로 합니다.
아래는 수정된 쿼리입니다. 제가 수정한 쿼리는 마농님의 조언에 따라 약간 수정한 것입니다.
테스트를 위해 WITH절에 샘플 레코드를 추가했습니다.
--- 제가 수정한 쿼리
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20
UNION ALL SELECT 'JESSE', '1992/11/06', 30
UNION ALL SELECT 'JESSE', '1992/11/11', 40
UNION ALL SELECT 'JESSICA', '1992/03/11', 50
UNION ALL SELECT 'JESSICA', '1992/11/03', 60
UNION ALL SELECT 'JESSICA', '1992/10/29', 70
UNION ALL SELECT 'JESSICA', '1992/11/10', 80
UNION ALL SELECT 'JESSICA', '1992/11/30', 90
UNION ALL SELECT 'JESSICA', '1992/11/12', 10
UNION ALL SELECT 'JESSICA', '1992/12/07', 20
UNION ALL SELECT 'JESSICA', '1992/12/09', 30
)
SELECT a.Name,
a.dob,
a.avg
FROM (SELECT t1.Name,
t1.dob,
ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC, t1.dob ASC) rn,
AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
FROM t1,
(SELECT t1.Name,
t1.dob[1,7],
RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
FROM t1
GROUP BY t1.Name, t1.dob[1,7]
) t2
WHERE t2.rk = 1
AND t1.Name = t2.Name
AND t1.dob like t2.dob||'%'
GROUP BY t1.Name, t1.dob, t1.score
) a
WHERE rn =1
--- 마농님의 쿼리 (WITH문 생략)
SELECT *
FROM (SELECT name
, dob
, avg_m
, ROW_NUMBER() OVER(
PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
FROM (SELECT name
, dob
, COUNT(*) OVER(PARTITION BY name, dob[1,7]) cnt_m
, COUNT(*) OVER(PARTITION BY name, dob ) cnt_d
, AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
FROM t1
) a
) a
WHERE rn = 1
;제가 올린 질문글은 아래 링크에서 참고해주세요.
- Total
- Today
- Yesterday