[DEV SQL] 5. [MySQL/MariaDB] Select Gruop by TOP1 (각 그룹별로 TOP 1 가져오기)

09 Mar 2020 in Database on 개발관련 SQL

[MySQL/MariaDB]에서 각 그룹별로 TOP 1 가져오는 포스팅이다.

예제 테이블

SCORES(점수)

컬럼	컬럼명
NAME	학생이름
SUBJECT	과목
SCORE	점수

SCORES Select

NAME	과목	점수
김재현	수학	90
김재현	국어	71
김재현	영어	66
김재현	사회	80
박웅비	수학	98
박웅비	국어	82
박웅비	영어	80
박웅비	사회	61
정대철	수학	77
정대철	국어	67
정대철	영어	78
정대철	사회	98
정연호	수학	77
정연호	국어	86
정연호	영어	67
정연호	사회	85
…	…	…
…	…	…

개발하다 보면 각 그룹 별 TOP 1 이 필요하는 쿼리가 필요하다. 오라클인 경우 PARTITONN BY 로 각 그룹(학생) 별로 ROWNUM를 주고 조건절을 주면 된다.

Oracle에서 Select Group by TOP 1

SELECT
    S1.*
FROM
    (
    SELECT
        NAME
        , SUBJECT
        , SCORE
        , ROW_NUMBER() OVER(PARTITONN BY NAME ORDER BY NAME, SCORE) ROWNUM
    FROM
        SCORES
    )
WHERE
    RNUM = 1 -- ex) RNUM => 3

MySQL에서는 각 그룹별로 ROWNUM를 활용해서 각 그룹별로 TOP 1를 가져올 수 있다.

MySQL에서 ROWNUM

SELECT
	@ROWNUM:=@ROWNUM+1 AS ROWNUM
	, NAME
	, SUBJECT
	, SCORE
FROM 
	SCORES, (SELECT @ROWNUM:=0) R

MySQL에서 각 그룹별로 ROWNUM를 활용한 Select Group by TOP 1

SELECT
	S1.*
FROM
    (
    SELECT 
        NAME
        , SUBJECT
        , SCORE
        , CASE WHEN @GRP = NAME THEN @ROWNUM:=@ROWNUM + 1 ELSE @ROWNUM :=1 END AS ROWNUM
        , (@GRP := NAME) as dummy
    FROM
        SCORES, (select @ROWNUM:=0, @GRP:='') R 
    ORDER BY
        NAME, SCORE ASC
    ) S1
WHERE
    S1.ROWNUM = 1

위에 쿼리를 실행하면 아래와 같이 각 학생 별로 제일 높은 점수의 과목을 가져올 수 있다. 통계나 집계에서 유용하게 활용 할 수 있다.

실행결과

NAME	SUBJECT	SCORE	ROWNUM
김재현	수학	90	1
박웅비	수학	98	1
정대철	사회	98	1
정연호	국어	86	1
주형신	영어	85	1
…	…	…	1
…	…	…	1

그럼 전 이만.