합계 / 평균 구하기 1 5 2,955

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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입