데이터베이스 질문과 답변
합계 / 평균 / 최근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]
답변 감사합니다. 많이 사용을 한 문법이 아니어서 솔직히 바로 이해가 되지는 않지만 외워서라도 익혀야 겠네요
감사합니다. 열심히 사용하고 익힐게요
) 버튼을 클릭하여 작성 하시면 됩니다.