[DATABASE/SQL] GROUP BY , GROUP BY GROUPING SETS 함수

1.GROUP BY 함수

  + 조회된 결과(ResultSet)에서 Group을 묶어 중복제거나 group 별 결과보기 등에 활용 됨

2.GROUP BY GROUPING SETS 함수

 + Group by 된 데이터를 GROUPING을 묶어 GROUPING 별로 데이터를 보여주는 함수

 + 데이터 통계(합계),소계 등에 많이 활용됨

 + 유사한 함수는 GROUPING ROLLUP / CUBE 가 있음, 필자는 GROUPING SETS함수가 이해하기 쉬워서 이 함수를 자주 사용함.

 

 

1.GROUP BY 

with school as (
select '1' as grade,'1' as class, '홍길동(1)' as name, 70 as score from dual union all 
    select '1','1','홍길동(4)',10 from dual union all
select '2','1','홍길동(5)',50 from dual union all
    select '2','1','홍길동(7)', 15 from dual union all
    select '2','2','홍길동(8)',90 from dual union all
    select '3','1','홍길동(2)',54 from dual union all
    select '3','1','홍길동(6)',60 from dual union all
select '3','2','홍길동(3)',72 from dual union all
    select '3','2','홍길동(9)',0 from dual 
)   SELECT * FROM SCHOOL;

Q01-01. 학년(GRADE) 별 SCORE(점수)의 합계를 구하시오.

with school as (
select '1' as grade,'1' as class, '홍길동(1)' as name, 70 as score from dual union all 
select '1','1','홍길동(4)',10 from dual union all
select '2','1','홍길동(5)',50 from dual union all
select '2','1','홍길동(7)', 15 from dual union all
select '2','2','홍길동(8)',90 from dual union all
select '3','1','홍길동(2)',54 from dual union all
select '3','1','홍길동(6)',60 from dual union all
select '3','2','홍길동(3)',72 from dual union all
select '3','2','홍길동(9)',0 from dual  )   
SELECT GRADE || '학년' AS 학년
         , SUM(SCORE) AS 점수
  FROM SCHOOL 
 GROUP BY GRADE;

문제에서 학년 별 점수를 구하라고 하였으니, GROUP BY 에 학년(GRADE)를 넣어준다.

그리고 점수는 합계를 내야 하기 때문에 SELECT 절에 합계함수 SUM으로 SCORE을 감싸준다.

GROUP BY는 말 그대로 데이터를 GROUP 지어 주기 때문에, SELECT 절에 나올 데이터(GRADE & SCORE)가 GROUP BY에 명시 되어 있던지 아니면 통계함수(SUM)으로 합계를 지어 주어야 한다. 

그렇지 않고 SUM을 빼고 조회를 하게 된다면

ORA-00979: GROUP BY 표현식이 아닙니다.

라는 에러를 마주하게 될 것이다. GROUP BY를 GRADE만 해 주고 SCORE을 안 해주었기 때문이다. 

다시 말해, 쿼리 입장에서는 GRADE는 총 데이터 9 건 중에서 GROUP을 지어라 해놓고(데이터를 줄여 놓고)

SCORE는 어쩌란거야? 이런 의미이다.

 

Q01-02.학년(GRADE) 의 반(CLASS)별 점수(SCORE)의 합계를 구하시오.

with school as (
select '1' as grade,'1' as class, '홍길동(1)' as name, 70 as score from dual union all
select '1','1','홍길동(4)',10 from dual union all
select '2','1','홍길동(5)',50 from dual union all
select '2','1','홍길동(7)', 15 from dual union all
select '2','2','홍길동(8)',90 from dual union all
select '3','1','홍길동(2)',54 from dual union all
select '3','1','홍길동(6)',60 from dual union all
select '3','2','홍길동(3)',72 from dual union all
select '3','2','홍길동(9)',0 from dual )
SELECT GRADE || '학년' AS 학년
        , CLASS AS 반
        , SUM(SCORE) AS 점수
FROM SCHOOL GROUP BY GRADE, CLASS;

생각보다 너무 간단하다. 첫 문제 Q01-01과 틀린점이 있다면 SELECT 구문에 CLASS를 추가하고, CLASS가 추가되었으니 GROUP BY에 CLASS 를 추가시켜주면 된다.

GRADE 별 > CLASS 별 > 점수 이기 때문에 GROUP BY 순서를 GRADE > CLASS 순으로 꼭 명시 해 주어야 한다.

ORDER BY도 기준에 따라 순서가 틀리듯이 GROUP BY도 순서의 영향을 받는다.

2.GROUP BY GROUPING SETS

앞에서 얘기 하였듯이, 통계 낼 때 주로 쓰는 함수이다. 

예제를 통해 보자

1번의 첫 이미지와 동일하다. 쿼리가 필요하면 위에서 긁어 쓰시오.

Q02-01.학년(GRADE)별 점수(SCORE)의 합계와 전체 데이터를 보여 주시오.

문제를 보면 이전 문제와 틀린 점은, 기존의 데이터도 함께 표시해 주어야 한다는 것이다.

with school as ( select '1' as grade,'1' as class, '홍길동(1)' as name, 70 as score from dual union all
select '1','1','홍길동(4)',10 from dual union all
select '2','1','홍길동(5)',50 from dual union all
select '2','1','홍길동(7)', 15 from dual union all
select '2','2','홍길동(8)',90 from dual union all
select '3','1','홍길동(2)',54 from dual union all
select '3','1','홍길동(6)',60 from dual union all
select '3','2','홍길동(3)',72 from dual union all
select '3','2','홍길동(9)',0 from dual
) SELECT grade,class, name, sum(score) score
FROM SCHOOL
group by grouping sets((grade),(grade,class,name))
order by 1;

우선, Grouping Sets에 있는 것들을 하나씩 뜯어보자.

제일 앞에 (grade)는 데이터 중 grade가 같은 데이터를 group by 하여 준다고 생각하면 된다.

즉 (grade)를 써줌으로써 나타나는 데이터는

이 3개의 데이터이다. grade 별 sum의 총합 !

만약 학년(grade) 별 학급(class)의 총합을 보고 싶다면

(grade,class) 이런 식으로 써 주면 된다.

SELECT grade,class, name, sum(score) score
FROM SCHOOL
group by grouping sets((grade,class),(grade,class,name))
order by 1;

grouping sets에서 명시해 준 grade와 class가 같은 데이터들!의 select 절에 있는 sum의 결과를 보여준다.

sum 대신 max 같은 함수를 사용한다면 grade와 class별 max의 값을 가져 올 수 있는 것이다.

 

그리고 여기서 가장 중요한 원본 데이터를 보여주기 위한 쿼리

(grade,class,name) 이다

이 것을 씀으로써 나타나는 데이터는

이 데이터 들이다. 생각해보면 (grade, class, name)을 묶어서 보여주어야 하는데, 3개가 key 값인 셈이다.

그래서 결국은 모든 데이터를 보여줄 수 밖에 없게 된다.

 

위의 내용을 이용해서 아래 문제를 풀어본다.

Q02-01.학년(GRADE)별 점수(SCORE)의 합계와 학년(GRADE)별 학급(CLASS)별 점수(SCORE)의 소계 그리고 전체 데이터를 보여 주시오.

 

SELECT  decode(class,null,decode(name,null,'총계'),grade) as grade
         , decode(name,null,decode(class,null,'','소계'),class) as class
         , decode(name,null,'------->',name) as name
         , sum(score) score
    FROM SCHOOL 
    group by grouping sets((grade),(grade,class),(grade,class,name));

 

사진과 샘플로 이해하는게 역시 최고네요 ^^