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

합계 / 평균 구하기

by 구사일생 [SQL Query] 합계 평균 ROLLUP [2021.03.04 21:50:19]

합계 / 평균 / 최근3개월(12,11,10월) / 최근6개월(12,11,10,9,8,7월) 평균값을 한번에 구하고 싶습니다.

UNION ALL로 계속 이어 붙이면 너무 길어지고 속도도 문제여서 한번에 할수 있는 방법이 있을 까요

ROLLUP을 하면 합계만 나옵니다. SQL 반출이 되질 않아서 집에서 대략적으로 작성 한 겁니다.

도와 주세요

해당월	건수	금액	영업일
1월	110	200000	24
2월	120	200100	22
3월	130	200200	24
4월	140	200300	23
5월	150	200400	24
6월	160	200500	23
7월	170	200600	24
8월	180	200700	24
9월	190	200800	23
10월	200	200900	24
11월	210	201000	23
12월	220	201100	24
합계	1980	2406600	282
1년영업일평균	1980/282	2406600/282
최근3개월평균	630/71	603000/71
최근6개월평균	1170/142	1205100/142

by 마농 [2021.03.05 08:10:52]

작성하신 쿼리도 간략하게 적어주시면 좋은데요.
DBMS 종류 및 버전도 명시해 주시구요.

-- Oracle --
WITH t AS
(
SELECT '01' mm, 110 cnt, 200000 amt, 24 dd FROM dual
UNION ALL SELECT '02', 120, 200100, 22 FROM dual
UNION ALL SELECT '03', 130, 200200, 24 FROM dual
UNION ALL SELECT '04', 140, 200300, 23 FROM dual
UNION ALL SELECT '05', 150, 200400, 24 FROM dual
UNION ALL SELECT '06', 160, 200500, 23 FROM dual
UNION ALL SELECT '07', 170, 200600, 24 FROM dual
UNION ALL SELECT '08', 180, 200700, 24 FROM dual
UNION ALL SELECT '09', 190, 200800, 23 FROM dual
UNION ALL SELECT '10', 200, 200900, 24 FROM dual
UNION ALL SELECT '11', 210, 201000, 23 FROM dual
UNION ALL SELECT '12', 220, 201100, 24 FROM dual
)
-- Model --
SELECT *
  FROM (SELECT NVL(mm, '합계') mm
             , SUM(cnt) cnt
             , SUM(amt) amt
             , SUM(dd) dd
          FROM t
         GROUP BY ROLLUP(mm)
        )
 MODEL
 DIMENSION BY (CAST(mm AS VARCHAR2(20)) mm)
 MEASURES (cnt, amt, dd)
 RULES
 ( cnt['1년영업일평균'] = ROUND(cnt['합계'] / dd['합계'], 2)
 , amt['1년영업일평균'] = ROUND(amt['합계'] / dd['합계'], 2)
 , cnt['최근3개월평균'] = ROUND(SUM(cnt)[mm BETWEEN '10' AND '12'] / SUM(dd)[mm BETWEEN '10' AND '12'], 2)
 , amt['최근3개월평균'] = ROUND(SUM(amt)[mm BETWEEN '10' AND '12'] / SUM(dd)[mm BETWEEN '10' AND '12'], 2)
 , cnt['최근6개월평균'] = ROUND(SUM(cnt)[mm BETWEEN '07' AND '12'] / SUM(dd)[mm BETWEEN '07' AND '12'], 2)
 , amt['최근6개월평균'] = ROUND(SUM(amt)[mm BETWEEN '07' AND '12'] / SUM(dd)[mm BETWEEN '07' AND '12'], 2)
 )
 ORDER BY INSTR('합계,1년영업일평균,최근3개월평균,최근6개월평균', mm), mm
;

by 마농 [2021.03.05 12:50:29]

-- Oracle --
WITH t AS
(
SELECT '01' mm, 110 cnt, 200000 amt, 24 dd FROM dual
UNION ALL SELECT '02', 120, 200100, 22 FROM dual
UNION ALL SELECT '03', 130, 200200, 24 FROM dual
UNION ALL SELECT '04', 140, 200300, 23 FROM dual
UNION ALL SELECT '05', 150, 200400, 24 FROM dual
UNION ALL SELECT '06', 160, 200500, 23 FROM dual
UNION ALL SELECT '07', 170, 200600, 24 FROM dual
UNION ALL SELECT '08', 180, 200700, 24 FROM dual
UNION ALL SELECT '09', 190, 200800, 23 FROM dual
UNION ALL SELECT '10', 200, 200900, 24 FROM dual
UNION ALL SELECT '11', 210, 201000, 23 FROM dual
UNION ALL SELECT '12', 220, 201100, 24 FROM dual
)
-- Rollup --
SELECT DECODE(gb, 0, mm
                , 1, '합계'
                , 2, '1년영업일평균'
                , 3, '최근3개월평균'
                , 4, '최근6개월평균'
                ) mm
     , ROUND(cnt / CASE WHEN gb > 1 THEN dd ELSE 1 END, 2) cnt
     , ROUND(amt / CASE WHEN gb > 1 THEN dd ELSE 1 END, 2) amt
     , CASE WHEN gb <= 1 THEN dd END dd
  FROM (SELECT h, q, mm
             , SUM(cnt) cnt
             , SUM(amt) amt
             , SUM(dd) dd
             , CASE WHEN GROUPING(mm) = 0 THEN 0
                    WHEN GROUPING(1)  = 1 THEN 1
                    WHEN GROUPING(h)  = 1 THEN 2
                    WHEN GROUPING(mm) = 1 AND q = 4 THEN 3
                    WHEN GROUPING(q)  = 1 AND h = 2 THEN 4
                END gb
          FROM (SELECT CEIL(mm / 6) h
                     , CEIL(mm / 3) q
                     , mm, cnt, amt, dd
                  FROM t
                ) a
         GROUP BY ROLLUP(1, h, q, mm)
        )
 WHERE gb >= 0
 ORDER BY gb, mm
;

by 구사일생 [2021.03.06 17:28:23]

몇일 바쁜일이 있어 이제야 봤네요 역시 ~~

위의 예제는 이해가 전혀 음 ~~ 아래의 예제를 제가 활용 할수 있을 듯 합니다, 감사합니다.

그런데, 혹시 GROUPING(1), GROUPING(0), GROUPING(2) 이게 차이가 뭔가요

by 마농 [2021.03.08 08:12:16]

GROUPING(항목) 은 해당 항목으로 롤업이 되었는지 아닌지를 판단합니다.
GROUP BY 에 사용된 항목만 GROUPING 에 사용할 수 있습니다.
이 쿼리에서는 GROUPING(1) 은 가능하지만 GROUPING(0) 이나 GROUPING(2) 는 불가합니다.
이 쿼리에서는 합계 행을 두번 표시하기 위해 숫자 1을 롤업에 사용하였습니다.

by 구사일생 [2021.03.09 21:12:01]

답변 감사합니다. 많이 사용을 한 문법이 아니어서 솔직히 바로 이해가 되지는 않지만 외워서라도 익혀야 겠네요

감사합니다. 열심히 사용하고 익힐게요

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

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

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

데이터베이스 질문과 답변