데이터베이스 질문과 답변
by 요호호 [2021.10.20 12:13:10]
서울it직업전문학교의 2021년 월별 학생의 증가수 입니다
with t as ( SELECT 202101 가입월, 104 총가입자수 FROM dual UNION ALL SELECT 202102, 168 FROM dual
UNION ALL SELECT 202103, 220 FROM dual
UNION ALL SELECT 202104, 324 FROM dual UNION ALL SELECT 202105, 424 FROM dual
UNION ALL SELECT 202106, 516 FROM dual UNION ALL SELECT 202107, 602 FROM dual
UNION ALL SELECT 202108, 602 FROM dual UNION ALL SELECT 202109, 602 FROM dual
UNION ALL SELECT 202110, 602 FROM dual UNION ALL SELECT 202111, 602 FROM dual
UNION ALL SELECT 202112, 602 FROM dual )
SELECT st_rn.가입월,st_rn.총가입자수,GROUPING(st_rn.가입월),
CASE
WHEN st_rn.가입월 = 202101 THEN ' '
WHEN st_rn.총가입자수 - va_rn.총가입자수 >0 THEN st_rn.총가입자수 - va_rn.총가입자수||'명증가'|| '('||( st_rn.가입월-1)||'월 대비)'
WHEN st_rn.총가입자수 - va_rn.총가입자수 IS null THEN st_rn.총가입자수 - va_rn.총가입자수|| '('||( st_rn.가입월-1)||'월 대비)'
ELSE 0||'명증가' || '('||( st_rn.가입월-1)||'월 대비)'
END AS 전월대비
FROM (SELECT 가입월,총가입자수,ROWNUM rn FROM t) st_rn,
(SELECT 가입월,총가입자수, ROWNUM+1 rn FROM t)va_rn
WHERE st_rn.rn=va_rn.rn(+)
ORDER BY 가입월;
가입월 총가입자수 전월대비
202101 7
202102 8 1명증가(202101월 대비)
202103 12 4명증가(202102월 대비)
202104 12 0명증가(202103월 대비)
202105 13 1명증가(202104월 대비)
202106 21 8명증가(202105월 대비)
202107 22 1명증가(202106월 대비)
202108 23 1명증가(202107월 대비)
202109 25 2명증가(202108월 대비)
202110 26 1명증가(202109월 대비)
202111 26 0명증가(202110월 대비)
202112 28 2명증가(202111월 대비)
2021년합계 xxx명 xx명 증가 <= 를 마지막 행에 붙이고 싶은데 어떻게 해야 할까요..
꼭 rownum+1 을 이용한 이 형태를 유지해야 하는 건가요?
지금 작성된 쿼리는 여러 문제점을 가지고 있습니다.
분석함수를 사용하면 더 편리합니다.
합계 부분은 롤업을 이용하거나 UNION ALL 로 붙이는 방법도 있습니다.
위 쿼리의 문제점들
1 테이블에 2021년도 자료만 있는가?
- 2021년도만 조회하는 조회조건이 있어야 할 것 같구요.
2. rownum 조인이 맞는가?
- 테이블의 자료는 정렬되어 들어 있지 않습니다.
- 즉 월별 순서와 rownum 순서가 다를 수 있습니다.
- 차라리 rownum 대신 가입월 을 직접 사용해도 될 것 같습니다. (가입월 = 가입월+1)
3. CASE 구문이 비효율입니다.
- 첫번째 When 절은 특정 연월 조건을 주기 보다는. (연도별로 조건이 달라져야 함)
- 범용적으로 va_rn.총가입자수 IS null 조건을 주는게 좋습니다. (연도별로 조건이 동일함)
- 세번째 When 절은 1월만 만족하는 조건인데 1월은 이미 첫번째 When 절을 만족하므로 불필요한 구문입니다.
- 가입자수는 항상 증가만 있나요? 감소에 대한 고려는 없네요.
학원에서 공부하는거라 제가 공부용으로 만들었습니다.
감소가 있다면 제가 만든 쿼리조차 만들지 못했을거라 제가 감소한 경우를 넣지 않았습니다.
rownum+1 형태 유지하지 않아도 됩니다.
답변 감사합니다~
WITH t AS
(
SELECT 202101 가입월, 104 총가입자수 FROM dual
UNION ALL SELECT 202102, 168 FROM dual
UNION ALL SELECT 202103, 220 FROM dual
UNION ALL SELECT 202104, 324 FROM dual
UNION ALL SELECT 202105, 424 FROM dual
UNION ALL SELECT 202106, 516 FROM dual
UNION ALL SELECT 202107, 602 FROM dual
UNION ALL SELECT 202108, 602 FROM dual
UNION ALL SELECT 202109, 602 FROM dual
UNION ALL SELECT 202110, 602 FROM dual
UNION ALL SELECT 202111, 602 FROM dual
UNION ALL SELECT 202112, 602 FROM dual
)
SELECT NVL(가입월||'', 가입년||'년합계') 가입월
, MAX(총가입자수) 총가입자수
, SUM(증감) ||
CASE WHEN SUM(증감) >= 0 THEN '명증가' ||'('|| MIN(전월) ||'월 대비)' END 전월대비
FROM (SELECT FLOOR(a.가입월 / 100) 가입년
, a.가입월
, b.가입월 전월
, a.총가입자수
, a.총가입자수 - b.총가입자수 증감
FROM t a
, t b
WHERE a.가입월 - 1 = b.가입월(+)
)
GROUP BY 가입년, ROLLUP(가입월)
;
) 버튼을 클릭하여 작성 하시면 됩니다.