연도별 인원수 총 합계구하기 입니다 0 3 180

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명 증가    <= 를 마지막 행에 붙이고 싶은데 어떻게 해야 할까요..

by 마농 [2021.10.20 13:45:39]

꼭 rownum+1 을 이용한 이 형태를 유지해야 하는 건가요?
지금 작성된 쿼리는 여러 문제점을 가지고 있습니다.
분석함수를 사용하면 더 편리합니다.
합계 부분은 롤업을 이용하거나 UNION ALL 로 붙이는 방법도 있습니다.

위 쿼리의 문제점들
1 테이블에 2021년도 자료만 있는가?
- 2021년도만 조회하는 조회조건이 있어야 할 것 같구요.
2. rownum 조인이 맞는가?
- 테이블의 자료는 정렬되어 들어 있지 않습니다.
- 즉 월별 순서와 rownum 순서가 다를 수 있습니다.
- 차라리 rownum 대신 가입월 을 직접 사용해도 될 것 같습니다. (가입월 = 가입월+1)
3. CASE 구문이 비효율입니다.
- 첫번째 When 절은 특정 연월 조건을 주기 보다는. (연도별로 조건이 달라져야 함)
- 범용적으로 va_rn.총가입자수 IS null 조건을 주는게 좋습니다. (연도별로 조건이 동일함)
- 세번째 When 절은 1월만 만족하는 조건인데 1월은 이미 첫번째 When 절을 만족하므로 불필요한 구문입니다.
- 가입자수는 항상 증가만 있나요? 감소에 대한 고려는 없네요.


by 요호호 [2021.10.20 14:09:31]

 

학원에서 공부하는거라 제가 공부용으로 만들었습니다.

감소가 있다면 제가 만든 쿼리조차 만들지 못했을거라 제가 감소한 경우를 넣지 않았습니다.

rownum+1 형태 유지하지 않아도 됩니다.

답변 감사합니다~


by 마농 [2021.10.20 14:33:01]
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(가입월)
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입