꿈꾸는 개발자, DBA 커뮤니티 구루비

group by rollup 질문이요.

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(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변