Greenplum

[postgresql] rollup 활용하여 합계, 소계 명시적으로 나타내기

YourJean 2021. 12. 8. 16:14

ROLLUP 함수

:  그룹 함수 중 하나로, 소그룹 간의 소계를 계산해줌 -> 그룹 함수이기때문에 GROUP BY 절 안에서 사용.

 

Preview

1. 전체 합계만 구하기 : group by rollup(( ))  /*괄호 두 번*/

2. 전체 합계 및 소계 구하기 : group by rollup( )  /*괄호 한 번*/

 

 

 


 

활용 방법

ex) 학원비 관리 table

school dept grade name cost
한양고 이과 2 홍길동 120000
한양고 이과 2 이순신 560000
서울고 문과 1 이황 300000
서울고 문과 3 현아 330000
대전고 이과  1 태민 200000

위와 같이 정규화가 안된 학원비 관리 테이블이 있다고 치자...

학교, 과, 학년별 학원비의 전체합 혹은 소계를 구하고 싶다고 가정한다.

 

 

 

1. 전체 합계 명시적으로 나타내기 

 

school dept grade cost
전체합 1510000
한양고 이과 2 680000
서울고 문과 1 300000
서울고 문과 3 330000
대전고 이과 1 200000

 

위와 같이 결과가 나오기 위해서 아래와 같이 sql 문을 작성해준다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
    *
    from(
    select
         shcool,
         decode(grouping(school,dept,grade),7,'전체합',dept) as dept,
        grade,
       sum(cost) as cost
    from
        academy
    group by
        rollup ((scholl,dept,grade)) 
        )a
order by
   school is null desc ,
    dept,
    grade desc
cs

 

1) 전체합 구하기 :  rollup(()) 괄호를 두 번 묶어, 소그룹이(school,dept,grade) 하나의 그룹이 되어 합계를 구한다.

따라서, 소계를 구할 그룹이 '하나'이므로 전체합을 구할 수 있다. 

 

2) 명시적으로 나타내기 :  

원래 rollup 함수를 이용하면 구해진 합계가 맨 마지막 줄에 나타난다.

우리가 원하는 것은 '전체합'이라는 단어가 들어가야하고 맨 윗줄에 정렬되어야 한다.

 

   따라서, decode 문과 grouping 함수를 이용한다. 

  •  grouping( )함수는 그룹 결합 유무에 따라 비트를 반환한다. 즉 , 모두 결합이 되면 1,1,1 -> 4+2+1 = 7 이 반환.
  •  decode( )함수는 java의 if 문, sql 에서의 case문과 같은 역할을 한다. 

 

  Decode(grouping(shool, dept,grade),7,'전체합',dept) as dept

  → GROUPING함수는 여러 컬럼을 매개변수로 사용

     매개변수의 컬럼 순서에 맞게 해당 컬럼이 NULL인 경우 1을 반한하고 아닌 경우 0을 반환한다. 

    ex)( null, null, null ) → 1 1 1 (2진수) → 4+ 2 + 1 = 7 (10진수) 

        ( school, null, null ) → 0 1 1 (2진수) → 0+ 2 + 1 = 3 (10진수) 

        ( school, dept, null ) → 0 0 1 (2진수) → 0+ 0 + 1 = 1 (10진수) 

 

  ∴ GROUPING 함수에서 반환된 비트가 7이면 dept 컬럼의 데이터를 '전체합' 으로 나타내고 그게 아니라면

      dept의 데이터로 채워넣는다. 그리고 이걸 dept라 칭한다.

 

  마지막으로 select로 한 번 더 묶고 order by를 이용하여 정렬을 해준다


 

2. 전체 합계 및 소계를 구해서 명시적으로 나타내기 

school dept grade cost
전체합 1510000
한양고 합 (null) (null) 680000
한양고 이과 합  (null) 680000
한양고 이과 2 680000
서울고 합  (null) (null) 630000
서울고  문과 합 (null) 630000
서울고 문과 1 300000
서울고 문과 3 330000
대전고 합 (null) (null) 200000
대전고  이과 합 (null) 200000
대전고 이과 1 200000

 

위와 같이 소계가 포함된 결과가 나오기 위해서, 아래와 같이 sql 문을 작성해준다.

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
    *
    from(
    select
       case  when school notnull and dept is null and grade is null then concat(school,' 합') else school end school,
        case  when school is null and grade is null and grade is null then concat(dept,' 전체합'
              when dept notnull and grade is null then concat(grade,' 합'
        else dept end dept,
       grade,
       sum(cost) cost
    from
      academy
    group by rollup (school,dept,grade)
        )a
order by
   school is null desc ,
   school desc,
   dept desc
cs

 

1) 전체합 및 소계 구하기 :  rollup( ) 괄호를 한 번 묶어, 소그룹의(school,dept,grade) 합산을 구한다.

이때, rollup 괄호 안에 들어가는 컬럼들의 순서가 굉장히 중요하다.

 

   rollup( ) 은 계층구조이므로 인수의 순서가 바뀌면 안된다.  ex) rollup(a,b,c) 라면 (a,b,c) , (a,b) , (a), ( ) 로 결합.

  따라서, group by rollup(school,dept,grade)

  -> (school,dept,grade) , (school, dept) , (school) , ( /*전체합*/ ) 라는 결과가 나온다.

 

2) 명시적으로 나타내기 :  

원래 rollup 함수를 이용하면 구해진 합계가 맨 마지막 줄에 나타난다.

우리가 원하는 것은 합산된 결과에 'xx 합' 이라는 단어가 들어가야하고 맨 윗줄에 정렬되어야 한다.

 

   따라서, case문과 concat 함수를 이용한다. 

  •  concat( )함수는 concat(a,b) 문자열 a에 b라는 문자열을 합쳐, 두 개의 문자열을 하나로 만들어주는 함수이다.

   즉, null값을 이용하여 해당하는 인수에 '합'이라는 단어를 붙여준다. 

 

 

  마지막으로 select로 한 번 더 묶고 order by를 이용하여 정렬을 해준다