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

[해결완료] 통계를 짜야하는데 의문점이 생겨서 조언 부탁드립니다ㅜ

by zumsim [Tibero] tibero 통계 sql [2024.10.11 11:13:51]

학생 수 지역	2018년 (1,2차)	2019년 (3,4차)	2020년 (5,6차)	2021년 (7,8차)		2022년 (9,10차)		2023년 (11,12차)		2024년 (13차)		합계
학생 수 지역	중학교	중학교	중학교	초	중	초	중	초	중	초	중	초	중
서 울	2	5	2	1	5	-	1	2	1	1	1	4	17
부 산	-	-	-	-	-	-	-	-	1	-	-	0	1
대 구	-	-	-	-	-	-	-	-	1	-	1	0	2
인 천	-	-	-	-	1	-	-	-	-	-	-	0	1
광 주	-	-	-	-	-	-	-	1	-	-	-	1	0
대 전	-	-	2	1	4	-	2	-	4	-	2	1	14

위와 같은 형태의(위에는 예시) 통계를 짜야하는데 쿼리에 미숙해서 짜다가 의문이 들어서 조언을 구하기 위해 남깁니다

SELECT
   '제주' ADDR_NM,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2018' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) MS1,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2019' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) MS2,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2020' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) MS3,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2021' AND AER_TYPE = 'P' THEN CNT END, 0)), 0) AER_ES1,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2021' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) AER_MS1,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2022' AND AER_TYPE = 'P' THEN CNT END, 0)), 0) AER_ES2,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2022' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) AER_MS2,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2023' AND AER_TYPE = 'P' THEN CNT END, 0)), 0) AER_ES3,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2023' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) AER_MS3,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2024' AND AER_TYPE = 'P' THEN CNT END, 0)), 0) AER_ES4,
   NVL(SUM(NVL(CASE WHEN CREATE_YEAR = '2024' AND AER_TYPE = 'M' THEN CNT END, 0)), 0) AER_MS4,
   NVL(SUM(NVL(CASE WHEN AER_TYPE = 'P' THEN CNT END, 0)), 0) "ES_TOT",
   NVL(SUM(NVL(CASE WHEN AER_TYPE = 'M' THEN CNT END, 0)), 0) "MS_TOT"
FROM (
   SELECT
       TO_CHAR(TO_DATE(CREATE_DT, 'YYYYMMDDHH24MISS'), 'YYYY') CREATE_YEAR,
       AER_ADDR_CD,
       AER_TYPE,
       COUNT(AER_ID) CNT
   FROM KEDI_ACRDT_EDU_REQ
   WHERE AER_ADDR_CD = '9290000' <--------- 이게 db에 저장되어있는 지역별 코드입니다
   GROUP BY CREATE_DT, AER_ADDR_CD, AER_TYPE
)

이런식으로 지역별 통계를 union해서 하는 식으로 해보려했는데 결과는 원하는대로 나오는데 문제는

저렇게 create_year = 년도 이런식으로 하니까

추후 년도가 넘어가면 그때 저렇게 추가해주지않으면 그 년도가 정상출력이 안될거같아서 고민이됩니다.

혹시 조그만 조언이라도 해주시면 감사하겠습니다ㅜ

해결방법

2018부터 현재년도까지의 통계를 출력해야하고 반복되는 쿼리가 있어서

java에서 years라는 변수에 리스트로 담아서

SUM(CASE WHEN SUBSTR(AER_DATE, 1,4) LIKE CONCAT(REPLACE(#years[]#, '''', ''),'%') AND SCHOOL_LV = '01' THEN 1 ELSE 0 END) AS AER_ES$years[]$,
SUM(CASE WHEN SUBSTR(AER_DATE, 1,4) LIKE CONCAT(REPLACE(#years[]#, '''', ''),'%') AND SCHOOL_LV = '02' THEN 1 ELSE 0 END) AS AER_MS$years[]$,

이런식으로 iterate를 돌려서 해결하였습니다.

다만 이런식으로 하면 화면단도 동적으로 구성하여야 하니 혹여나 비슷한 문제로 고민하시는분들은 유의하여 참고가 되시길 바랍니다!

ps. AER_ES$years[]$, alias 부분은 저 방법 외엔 어떤 걸 써도 해결이 되지 않아 $$로 사용을 했는데 실질적으로 취약점 점검에는 걸리는 부분인것으로 알아서

주의하셔야합니다.

by 마농 [2024.10.11 14:00:52]

1. 복잡한 연도 구하기 개선
- 현재 : TO_CHAR(TO_DATE(create_dt, 'yyyymmddhh24miss'), 'yyyy') create_year
- 개선 : SUBSTR(create_dt, 1, 4) create_year
2. 지역별로 따로 쿼리해서 유니온 하나요?
- 지역 조건 없애고 그룹바이 지역 하면 유니온 안해도 됩니다.
- 지역명은 코드테이블 조인해서 가져오도록 하는게 좋습니다.
3. 연도 자동화
- 연도를 직접 조건으로 주지 말고
- 현재연도와의 차이를 구해 조건으로 주면 됩니다.
4. aer_id 는 널 가능한가요?
- 널이 없는 항목이라면? 굳이 COUNT(aer_id) 할 필요가 없습니다.
- 그냥 COUNT(*) 하시면 됩니다.
5. 합계 와 연도별 합계 불일치 현상 발생
- 합계에는 연도 조건이 없어 2018년 이전 값도 합산됨
- 2018~2020년은 중등만 뽑고 있는데 합계에서는 초등도 뽑고 있음
- 죄측 연도별 합계의 합산결과와 우측 합계의 값이 일치하지 않을 수 있음
- 이게 맞는지?

by zumsim [2024.10.11 15:24:05]

1. 아 저런식으로 해도 되는군요 감사합니다 ㅜㅎ

2. 네 현잰 지역별로 해서 유니온해서 하고 있습니다. 그룹바이도 생각은 당연히해봤는데 저 표에 제 생각이 얽매여서 그런가 그 형태가 안나오는거같아서 고민이었습니다.

3번이 핵심같은데 감이 오질 않네요 조금만 고민해보겠습니다!!

4. aer_id는 필수값입니다

5. 예시로 작성된 표라 결과가 조금 다를 순 있습니다.

전체적으로 좋은 조언 감사합니다!

by zumsim [2024.10.11 16:17:50]

선배님.. 아무리 줄여도

SELECT
AER_ADDR_NM,
SUM(CASE WHEN CREATE_DT LIKE '2018%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS MS1,
SUM(CASE WHEN CREATE_DT LIKE '2019%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS MS2,
SUM(CASE WHEN CREATE_DT LIKE '2020%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS MS2,
SUM(CASE WHEN CREATE_DT LIKE '2021%' AND AER_TYPE = 'P' THEN 1 ELSE 0 END) AS AER_ES1,
SUM(CASE WHEN CREATE_DT LIKE '2021%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS AER_MS1,
SUM(CASE WHEN CREATE_DT LIKE '2022%' AND AER_TYPE = 'P' THEN 1 ELSE 0 END) AS AER_ES2,
SUM(CASE WHEN CREATE_DT LIKE '2022%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS AER_MS2,
SUM(CASE WHEN CREATE_DT LIKE '2023%' AND AER_TYPE = 'P' THEN 1 ELSE 0 END) AS AER_ES3,
SUM(CASE WHEN CREATE_DT LIKE '2023%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS AER_MS3,
SUM(CASE WHEN CREATE_DT LIKE '2024%' AND AER_TYPE = 'P' THEN 1 ELSE 0 END) AS AER_ES4,
SUM(CASE WHEN CREATE_DT LIKE '2024%' AND AER_TYPE = 'M' THEN 1 ELSE 0 END) AS AER_MS4,
(SELECT ORG_ORD FROM TCMN_ORG WHERE ORG_CD = AER_ADDR_CD) ORG_ORD
FROM 테이블명
GROUP BY AER_ADDR_NM, AER_ADDR_CD
ORDER BY ORG_ORD ASC;

이정도밖에 안떠오르는데 혹시 여기서 좀 더 감안해야할게 있을까요ㅜ

by 마농 [2024.10.12 14:51:19]

SELECT aer_addr_nm
     , COUNT(CASE y WHEN 6 THEN m END) ms1
     , COUNT(CASE y WHEN 5 THEN m END) ms2
     , COUNT(CASE y WHEN 4 THEN m END) ms3
     , COUNT(CASE y WHEN 3 THEN p END) aer_es1
     , COUNT(CASE y WHEN 3 THEN m END) aer_ms1
     , COUNT(CASE y WHEN 2 THEN p END) aer_es2
     , COUNT(CASE y WHEN 2 THEN m END) aer_ms2
     , COUNT(CASE y WHEN 1 THEN p END) aer_es3
     , COUNT(CASE y WHEN 1 THEN m END) aer_ms3
     , COUNT(CASE y WHEN 0 THEN p END) aer_es4
     , COUNT(CASE y WHEN 0 THEN m END) aer_ms4
     , COUNT(p) es_tot
     , COUNT(m) ms_tot
     , COUNT(*) tot
  FROM (SELECT a.aer_addr_cd
             , a.aer_addr_nm
             , b.org_ord
             , TO_CHAT(sysdate, 'yyyy') - SUBSTR(create_dt, 1, 4) y
             , CASE aer_type WHEN 'P' THEN 1 END p
             , CASE aer_type WHEN 'M' THEN 1 END m
          FROM kedi_acrdt_edu_req a
             , tcmn_org b
         WHERE a.aer_addr_cd = b.org_cd
        )
 GROUP BY org_ord, aer_addr_cd, aer_addr_nm
 ORDER BY org_ord
;

1. 지역명을 가져오는 방법?
- 원본 테이블에 있나요? 조인이 필요한가요?
- 혹시 tcmn_org 테이블에 지역명이 있는 것은 아닌지?
2. 합계 표시는?
- 합계에 어디까지 포함이 될지 고민이 필요합니다.
- 전체 다 포함할지? 최근 7년치만 표함할지?
- 2028~2020 사이의 m 만 포함할지? p 도 포함할지?

by zumsim [2024.10.14 15:41:53]

1. 맞습니다. tcmn_org에 있어요! 엄밀히 따지면 지역별 교육청명인데 그걸 지역명으로 대체해서 씁니다.

2. 합계는 모두입니다.

2018이 시작이고 그 뒤로 년수가 늘어날때마다 계속 그 년도에 맞게 잡혀야합니다.

특정 구간만의 합계가 아니라.ㅜㅜ

by 마농 [2024.10.14 15:49:46]

컬럼을 가변적으로 늘리는 것은 쿼리만으로는 안됩니다.
- 1안) 동적쿼리를 사용하는 방안
- 2안) 매년 초에 쿼리 수정하는 방안

SELECT aer_addr_nm
     , COUNT(CASE y WHEN 1 THEN m END) ms1
     , COUNT(CASE y WHEN 2 THEN m END) ms2
     , COUNT(CASE y WHEN 3 THEN m END) ms3
     , COUNT(CASE y WHEN 4 THEN p END) es4
     , COUNT(CASE y WHEN 4 THEN m END) ms4
     , COUNT(CASE y WHEN 5 THEN p END) es5
     , COUNT(CASE y WHEN 5 THEN m END) ms5
     , COUNT(CASE y WHEN 6 THEN p END) es6
     , COUNT(CASE y WHEN 6 THEN m END) ms6
     , COUNT(CASE y WHEN 7 THEN p END) es7
     , COUNT(CASE y WHEN 7 THEN m END) ms7
--   , COUNT(CASE y WHEN 8 THEN p END) es8  -- 2025년 추가
--   , COUNT(CASE y WHEN 8 THEN m END) ms8  -- 2025년 추가
--   , COUNT(CASE y WHEN 9 THEN p END) es9  -- 2026년 추가
--   , COUNT(CASE y WHEN 9 THEN m END) ms9  -- 2026년 추가
     , COUNT(p) es_tot
     , COUNT(m) ms_tot
     , COUNT(*) tot
  FROM (SELECT a.aer_addr_cd
             , b.org_nm aer_addr_nm
             , b.org_ord
             , SUBSTR(create_dt, 1, 4) - 2018 + 1 y
             , CASE aer_type WHEN 'P' THEN 1 END p
             , CASE aer_type WHEN 'M' THEN 1 END m
          FROM kedi_acrdt_edu_req a
             , tcmn_org b
         WHERE a.aer_addr_cd = b.org_cd
        )
 GROUP BY org_ord, aer_addr_cd, aer_addr_nm
 ORDER BY org_ord
;

by zumsim [2024.10.15 11:53:23]

아무래도.. 2안이 베스트인거 같습니다.

쿼리를 동적으로 한다해도 화면도 동적으로 해줘야하는데

현재 사용중인 grid로 그렇게 하기엔 그게 상당히 빡세서..

답변 감사합니다ㅜㅜ

그리고 저거랑은 별개의 질문인데 alias에는 concat이나 ||로 문자열 연결해서 사용할수 없나요?

by 마농 [2024.10.15 13:02:52]

알리아스를 바꾸는 것도 동적쿼리 영역입니다.

by zumsim [2024.10.21 15:17:27]

뒤늦게나마 답변 다시 답니다!

결국은 java단에서 2018부터 현재까지의 년도를 리스트에 담아 넘겨서

iterate 돌려서 출력하는 것으로 진행했습니다.

화면도 이거에 따라 동적으로 짜야하는 문제가 있었는데 그것도 해결했구요..ㅎ

답변 자세히 해주셔서 감사했습니다!

by 마농 [2024.10.21 15:56:20]

해결 내용에 대한 피드백 좋습니다.
어떤 식으로 해결했는지? 소스 코드 등을 추가로 상세하게 공유해 주시면.
비슷한 어려움을 겪고 있는 많은 분들에게 도움이 될 것입니다.

댓글등록

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

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

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

데이터베이스 질문과 답변