group by rollup 질문이요. 0 6 187

by ekekekek [2019.10.07 15:15:25]


안녕하세요. 

공부를 하다 group by rollup 이라는걸 알게되서 공부를 하고있는데

select 
        a.sch
        , a.code
        , a.stu_year
        , a.class_n
        , count(b.stu_cnt) as stu_cnt
        , count(case b.req WHEN 'y' THEN 1 END) req_y
    from sch as a, stu as b
    where a.code = b.code

위 쿼리를 이용해 밑 테이블과 같은 데이터를 가져왔습니다.

sch stu_year class_n stu_cnt req_y
A중학교 1학년 1반 30 28
A중학교 1학년 2반 29 21
A중학교 2학년 1반 31 30
A중학교 2학년 2반 25 20
A중학교 2학년 3반 28 26
B중학교 1학년 1반 32 30
B중학교 1학년 2반 31 29
B중학교 2학년 1반 29 27
B중학교 2학년 2반 33 25
B중학교 2학년 3반 28 22
B중학교 3학년 1반 32 28
B중학교 3학년 2반 31 18

 

위의 데이터를 

sch stu_year class_n stu_cnt req_y req_y_p
총계     359 304 84.7
A중학교     143 125 87.4
  1학년 1반 30 28 93.3
  1학년 2반 29 21 72.4
  2학년 1반 31 30 96.8
  2학년 2반 25 20 80.0
  2학년 3반 28 26 92.9
B중학교     216 179 82.9
  1학년 1반 32 30 93.8
  1학년 2반 31 29 93.5
  2학년 1반 29 27 93.1
  2학년 2반 33 25 75.8
  2학년 3반 28 22 78.6
  3학년 1반 32 28 87.5
  3학년 2반 31 18

58.1

형태와 같이 정렬시켜보기위해

SELECT 
    DECODE(sch, NULL, '총계', sch) AS sch
    , stu_year
    , class_n
    , stu_cnt
    , req_y
    , TRUNC((req_y / stu_cnt) * 100 , 1) AS req_y_p
FROM (
    select 
        a.sch
        , a.code
        , a.stu_year
        , a.class_n
        , count(b.stu_cnt) as stu_cnt
        , count(case b.req WHEN 'y' THEN 1 END) req_y
    from sch as a, stu as b
    where a.code = b.code
)
GROUP BY ROLLUP (sch, (stu_year, class_n));

이런식으로 쿼리를 작성했는데..group by 표현식에 걸린다고만 출력이 되어 rollup 앞에 rollup에 해당되는 컬럼 말고 다른 컬럼을 추가해서 조회해보니 중복으로 값이 출력이 되는데..rollup의 정확한 사용방법이 어떻게 될까요?

by jkson [2019.10.07 15:37:51]
SELECT DECODE(SCH, NULL, '총계', DECODE(GROUPING(STU_YEAR),1,SCH)) AS SCH
     , STU_YEAR
     , CLASS_N
     , SUM(STU_CNT) STU_CNT
     , SUM(REQ_Y) REQ_Y
     , TRUNC((SUM(REQ_Y) / SUM(STU_CNT)) * 100 , 1) AS REQ_Y_P
  FROM ( SELECT A.SCH
              , A.CODE
              , A.STU_YEAR
              , A.CLASS_N
              , COUNT(B.STU_CNT) AS STU_CNT --> 왜 COUNT죠?
              , COUNT(CASE B.REQ WHEN 'Y' THEN 1 END) AS REQ_Y
           FROM SCH AS A, STU AS B
          WHERE A.CODE = B.CODE
          GROUP BY A.SCH, A.CODE, A.STU_YEAR, A.CLASS_N
       ) T
 GROUP BY ROLLUP (T,SCH, (T.STU_YEAR, T.CLASS_N))
 ORDER BY GROUPING(T.SCH) DESC,T.SCH, GROUPING(T.STU_YEAR) DESC, T.STU_YEAR, T.CLASS_N                     

괄호를 오른쪽부터 제거하면서 group by 한다고 생각하시면 됩니다.

1.SCH, STU_YEAR_ CLASS_N별

2.SCH별

3.전체

STU_CNT, REQ_Y 합계 계산되는 컬럼이므로 SUM함수를 사용하셔야 합니다.

... 그런데 이상하네요. 처음 쿼리가 GROUP BY 없이 결과가 나오나요???

DB 종류가 뭐예요?


by ekekekek [2019.10.07 17:01:11]

아 글 작성할때 쿼리를 돌려보면서 짤수있는 상황이 아니라 그냥 기억으로 썼던 쿼리 적어 놓은거라 group by를 빼고 적었었네요. db종류는 오라클 11g입니다.


by ekekekek [2019.10.07 19:01:14]

알려주신 방법으로 조금씩 수정하다보니 원하는 양식대로 출력이 되네요. 감사합니다.!


by 마농 [2019.10.07 16:40:17]

롤업 사용법을 익히기 이전에 그룹바이 사용법부터 익히셔야 할 듯 합니다.
질문의 그룹바이 쿼리가 이상해요.
DB 종류나 버전도 알려주시구요.


by ekekekek [2019.10.07 17:01:54]

위의 쿼리내용에 그룹바이(GROUP BY A.SCH, A.CODE, A.STU_YEAR, A.CLASS_N)를  빼먹고 작성을 했네요. 죄송합니다.

db종류를 오라클 11g 사용하고있습니다.


by ekekekek [2019.10.07 19:02:18]

위에 jkson 님이 알려주신 방법과 마농님이 ROLLUP 관련 댓글들 달아주신거 참고해서 원하는 양식대로 출력했습니다.

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입