Greenplum

[postgresql] rollup 예제 응용(2) - join

YourJean 2021. 12. 29. 09:44

지난 포스팅 예제이므로 참고

 

[postgresql] rollup 예제 응용 (1) 합계 월 별

ROLLUP 함수 : 그룹 함수 중 하나로, 소그룹 간의 소계를 계산해줌 -> 그룹 함수이기 때문에 GROUP BY 절 안에서 사용. 지난 포스팅 예제의 응용문제이니 참고 그룹 함수이기때문에 GROUP BY 절 안에서

jeon-u-jin.tistory.com

예제 응용(2) 

- 학원비 결제 TB

school dept grade name yyyymm cost
한양고 이과 1 홍길동 202101 560000
한양고 이과 1 홍길동 202102 320000
한양고 문과 2 현아 202101 230000
한양고 문과 3 김범수 202102 500000
서울고 문과 1 초롱이 202101 200000
서울고 문과 1 초롱이 202102 200000

 

다음과 같이 학생들이 학원비를 결제한 테이블이 있다. 이 테이블의 소계와 합계를 월별로 나타내면 아래와 같다.

 

school dept grade m202101 m202102
(null) 전체합 (null) 990000 1020000
한양고 합 (null) (null) 790000 820000
한양고 이과 합 (null) 560000 320000
한양고 이과 1 560000 320000
한양고 문과 합 (null) 230000 500000
한양고 문과 2 230000 (null)
한양고 문과 3 (null) 500000
서울고 합 (null) (null) 200000 200000
서울고 문과 합 (null) 200000 200000
서울고 문과 1 200000 200000

 

JOIN을 이용하여  월별 합계를 나타내는 3가지 방법

 1. 서브쿼리에서 rollup 후 join  

 2. 서브 쿼리에서 그룹화 후 rollup

 3. 서브 쿼리를 join 후 rollup

 

즉, 그룹화한걸 합치는 방법(1번,2번)과 두 테이블의 데이터를 합쳐서 그룹화하는 방법이다. 

 

 그러나 여기서 3번의 경우, 한 테이블 당 1천만개 이상의 데이터가 있다면, 몇 억개의 데이터가 조인이되어

실행 시간이 매우 길어진다. (10분동안 실행해 봤는데도 안됨)

 

그래서 조금 특이한 방법으로 조인을 해주어야 한다.

자세한건 아래에서 설명한다.

※ JOIN시 주의

  • JOIN할 때 데이터의 null값은 누락되어 JOIN된다. 따라서 coalesce를 이용하여 null값을 치환해주어야한다

 

1. rollup을 먼저한 후 join

with m202101 as (
        select
            school,
            dept,
            grade,
            round(sum(cost)) as m202101
        from
            fc_cost
        where
            yyyymm = '202101'
        group by rollup(school,dept,grade)), /*rollup 처리*/
        m202102 as (
        select
            school,
            dept,
            grade,
            round(sum(cost))as m202102
        from
            fc_cost
        where
            yyyymm = '202102'
        group by rollup(school,area,grade) ) /*rollup 처리*/

 

 with절 안에 두 서브쿼리를 m202101,m202102 로 명칭을 지어주었다.

 두 서브쿼리 모두 group by rollup을 해준 것을 알 수 있다. 

 

이처럼 그룹화와 소계를 생성한 테이블 두 개를 join을 해주면 된다. 

 

 

 <전체 코드>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
with m202101 as (
        select
            school,
            dept,
            grade,
            round(sum(cost)) as cost1,
            grouping(school,dept,grade) gid
        from
            fc_cost
        where
            yyyymm = '202101'
        group by rollup(school,dept,grade)),
        m202102 as (
        select
            school,
            dept,
            grade,
            round(sum(cost))as cost2,
            grouping(school,dept,grade) gid
        from
            fc_cost
        where
            yyyymm = '202102'
        group by rollup(school,area,grade) )
    select * from(    
        select
            case  when a.gid = 3  or b.gid =3 then concat(coalesce(a.school,b.school),' 합') else coalesce(a.school,b.school) end school,             
            case  when a.gid = 7  or b.gid = 7 then concat(coalesce(a.dept,b.dept),' 전체합') 
                   when a.gid = 1  or b.gid = 1 then concat(coalesce(a.dept,b.dept),' 합') else a.dept end dept,
            coalesce(a.grade,b.grade) grade,            
            sum(a.cost1) m202101,
            sum(b.cost2) m202102
        from
            m202101 a
        full outer join m202102 b on
            (coalesce(a.school,'0') = coalesce(b.school,'0')
            and coalesce(a.dept,'0') = coalesce(b.dept,'0')
            and coalesce(a.grade,'0') = coalesce(b.grade,'0'))
        group by a.mline,
            a.dept,
            a.grade)c
    order by
         school is null desc,
         school desc,
         dept desc
 cs

 

코드 설명

  → full join을 해주어야 한다.

     join(inner, left, right)을 하게되면 데이터 개수가 안맞을 경우, 누락되는 데이터가 발생할 수 있다.

     ex) 2월에는 있는 데이터가 1월에 없거나 1월에 있는 데이터가 2월에 없음.

 

  → 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 함수에서 반환된 비트에 따라 컬럼의 데이터를 수정해서 합계를 명시적으로 나타낸다.

 

2. 서브 쿼리에서 그룹화 후 rollup

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
with m202101 as (
        select
            school,
            dept,
            grade,
            round(sum(cost)) as cost1
        from
            fc_cost
        where
            yyyymm = '202101'
        group school,dept,grade),
        m202102 as (
        select
            school,
            dept,
            grade,
            round(sum(cost))as cost2
        from
            fc_cost
        where
            yyyymm = '202102'
        group by school,area,grade )
    select * from(    
        select
            case  when coalesce(a.school,b.school) notnull and coalesce(a.dept,b.dept) is null and coalesce(a.grade,b.grade) is null then concat(coalesce(a.school,b.school),' 합') else coalesce(a.school,b.school) end school,        
            case  when coalesce(a.school,b.school) is null and coalesce(a.grade,b.grade) is null and coalesce(a.dept,b.dept) is null then concat(coalesce(a.dept,b.dept),' 전체합') 
                   when coalesce(a.dept,b.dept) notnull and coalesce(a.grade,b.grade) is null then concat(coalesce(a.dept,b.dept),' 합') 
               else coalesce(a.dept,b.dept) end dept,
            coalesce(a.grade,b.grade),            
            sum(a.cost1) m202101,
            sum(b.cost2) m202102
        from
            m202101 a
        full outer join m202102 b on
            (coalesce(a.school,'0') = coalesce(b.school,'0')
            and coalesce(a.dept,'0') = coalesce(b.dept,'0')
            and coalesce(a.grade,'0') = coalesce(b.grade,'0'))
        group by rollup(
            coalesce(a.school,b.school),
            coalesce(a.dept,b.dept),
            coalesce(a.grade,b.grade))c
    order by
         school is null desc,
         school desc,
         dept desc
 cs

 

1번이랑 크게 차이는 없다. with 절에서 rollup 대신 group by 로 바꾸고 조인할 때 rollup을 처리해준다.

 

3.  서브 쿼리를 join 후 rollup

with m202101 as (
        select
            school,
            dept,
            grade
        from
            fc_cost
        where
            yyyymm = '202101'
        m202102 as (
        select
            school,
            dept,
            grade
        from
            fc_cost
        where
            yyyymm = '202102'
    select * from(    
        select
            case  when coalesce(a.school,b.school) notnull and coalesce(a.dept,b.dept) is null and coalesce(a.grade,b.grade) is null then concat(coalesce(a.school,b.school),' 합') else coalesce(a.school,b.school) end school,        
            case  when coalesce(a.school,b.school) is null and coalesce(a.grade,b.grade) is null and coalesce(a.dept,b.dept) is null then concat(coalesce(a.dept,b.dept),' 전체합') 
                   when coalesce(a.dept,b.dept) notnull and coalesce(a.grade,b.grade) is null then concat(coalesce(a.dept,b.dept),' 합') 
               else coalesce(a.dept,b.dept) end dept,
            coalesce(a.grade,b.grade),            
            sum(a.cost1) m202101,
            sum(b.cost2) m202102
        from
            m202101 a
        full outer join m202102 b on a.area = b.mline 
        group by rollup(
            coalesce(a.school,b.school),
            coalesce(a.dept,b.dept),
            coalesce(a.grade,b.grade))c
    order by
         school is null desc,
         school desc,
         dept desc

with 절을 살펴보면 서브쿼리에서 단순히 select만 하고있는 것을 알 수 있다.

만약 select 문의 결과값이 1천만이 넘는다면 두 테이블을 조인하는데 매우 많은 시간이 소요될 것이다.

 

따라서, 테이블의 조인 조건을 일부러 맞지 않게 주어야 한다.