데이터베이스 질문과 답변

제대로 쿼리를 짰는지... 느리게 하는 요인이 있는지 알려주시면 감사합니다 ㅠㅠ 0 7 1,809

by 스파이 [SQL Query] [2015.07.09 09:34:56] 

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(d.centarea,'0117','세종'),'0116','제주'),'0115','경남'),'0114','경북'),'0113','전남'),'0112','전북'),'0111','충남'),'0110','충북'),'0109','강원'),'0108','경기'),'0107','울산'),'0106','대전'),'0105','광주'),'0104','인천'),'0103','대구'),'0102','부산'),'0101','서울') 지역
     , d.centcode
     , d.centname
     , c.cnt
     , c.bongcnt
from (select a.centcode
           ,count(a.centcode) cnt
          , count(distinct(a.resno)) bongcnt
       from resactres a inner join centmst b on 
       a.centcode = b.centcode 
      where a.actdate between '2014-07-01' and '2015-06-30'
     group by a.centcode, a.resno having count(a.resno) >= 200) c, 
     centmst d
where c.centcode = d.centcode

 

 

이렇게 쿼리를 짜게 되면

지역  센터코드  센터명    count값  인원

광주 01000169    A         231          1
전남 01000528    B         206          1
광주 01000532    C         229          1
광주 01000532    C         214          1
경북 01000534    D         219          1
경기 01000553    E          246          1
경기 01000553    E          206          1
경기 01000553    E          247          1

 

이러한 데이터가 나오는데...

이 데이터를 지역 옆에 센터 코드로 그룹을 맺으려고 합니다.

예를 들면

지역  센터코드  센터명   인원

광주 01000169    A         1
전남 01000528    B         1
광주 01000532    C         2
경북 01000534    D         1
경기 01000553    E          3

 

일단 count 값을 보여주는 컬럼을 지우고 센터로 그룹을 맺으면 될 것 같은데...

그런데 count(distinct(a.resno)) bongcnt 이 부분 때문인지...

 

select aaa.지역, aaa.centcode, aaa.centname, sum(aaa.bongcnt) from
(select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(d.centarea,'0117','세종'),'0116','제주'),'0115','경남'),'0114','경북'),'0113','전남'),'0112','전북'),'0111','충남'),'0110','충북'),'0109','강원'),'0108','경기'),'0107','울산'),'0106','대전'),'0105','광주'),'0104','인천'),'0103','대구'),'0102','부산'),'0101','서울') 지역
     , d.centcode
     , d.centname
     , c.cnt
     , c.bongcnt
from (select a.centcode
           ,count(a.centcode) cnt
          , count(distinct(a.resno)) bongcnt
       from resactres a inner join centmst b on
       a.centcode = b.centcode
      where a.actdate between '2014-07-01' and '2015-06-30'
     group by a.centcode, a.resno having count(a.resno) >= 200) c,
     centmst d
where c.centcode = d.centcode) aaa
group by aaa.지역, aaa.centcode, aaa.centname, aaa.bongcnt

 

일단은 이렇게 만들긴 했는데...

제가 원하는바 제대로 된 것인지...

쿼리를 느리게 하는 요인이 어떤 부분이 있는지...

알려 주시면 감사하겠습니다 ㅠㅠ

아침부터 머리 아프게 해드려 죄송합니다 ㄷㄷ;

 

 

by 아발란체 [2015.07.09 09:51:06]

= ㅁ =); 잠깐 보면.. 과도한 REPLACE... 요거 코드테이블 또는 가상테이블 만드셔서 서브쿼리(스칼라)로 해도 속도가 개선됩니다. 그리고 REPLACE 구문 보다 저런 경우 DECODE 또는 CASE WHEN 구문이 보다 쓰기 쉬워용(=직관적), DECODE(centarea, '042', '대전', '031', '수원', '02', '서울', ....) 그 외는 실력이 딸려서... 직접 돌려봐야 할 것 같은디.. ㅋㅋ 실행계획 보는 방법 검색하셔서 해보세요. 그럼 어떤 부분에서 비용이 많이 발생하는지 나옵니다.

by 스파이 [2015.07.09 10:07:12]

ㅎㅎ 답변 감사합니다.

저도 이번 유지보수에 처음 들어왔는데

이전 사람이 지역코드를 뽑는걸 저렇게 표현 했더라구요... ㅠㅠ

지역코드는 시간 나는대로 한번 만들어 봐야 할 것 같습니다.

by 마농 [2015.07.09 09:54:56]

1. GROUP BY 에 resno 가 있어서 여러행으로 나오는 것입니다.
  - 그룹바이 항목에서 제외하세요
2. centmst 가 쓸데없이 2번 조인되네요.
  - 집계후 한번만 조인하세요
3. Replace 를 무지막지하게 사용하셨네요.
  - DECODE 로 해결 가능하겠네요.
  - 지역코드테이블이 따로 있다면 조인하여 사용하는게 좋겠네요.

SELECT DECODE(b.centarea, '0117', '세종', '0116', '제주', '0115', '경남', '0114', '경북'
                        , '0113', '전남', '0112', '전북', '0111', '충남', '0110', '충북'
                        , '0109', '강원', '0108', '경기', '0107', '울산', '0106', '대전'
                        , '0105', '광주', '0104', '인천', '0103', '대구', '0102', '부산'
                        , '0101', '서울') AS 지역
     , b.centcode
     , b.centname
     , a.cnt
     , a.bongcnt
  FROM (SELECT centcode
             , COUNT(*) cnt
             , COUNT(DISTINCT resno) bongcnt
          FROM resactres
         WHERE actdate BETWEEN '2014-07-01' AND '2015-06-30'
         GROUP BY centcode
        ) a
 INNER JOIN centmst b
    ON a.centcode = b.centcode 
;

 

by 스파이 [2015.07.09 10:10:03]

크!!! 감사합니다!! ㅠㅠ

decode를 아예 생각도 못하고 있었네요 ㅠㅠ

일단 지역코드 관련 테이블은 없는 것 같아

유지보수 시간 남을때 추가 해서 넣어야 할 것 같습니다.

제가 미쳐 보지 못한 부분 까지 설명해 주셔서 감사합니다 ^^

즐거운 하루 되세요~

by 마농 [2015.07.09 10:18:16]


음...
제가 Having 절을 빼먹었네요.
Having 절을 보니 Group By 에 resno 가 있는게 의미상 맞을지도 모르겠네요.

SELECT DECODE(b.centarea, '0117', '세종', '0116', '제주', '0115', '경남', '0114', '경북'
                        , '0113', '전남', '0112', '전북', '0111', '충남', '0110', '충북'
                        , '0109', '강원', '0108', '경기', '0107', '울산', '0106', '대전'
                        , '0105', '광주', '0104', '인천', '0103', '대구', '0102', '부산'
                        , '0101', '서울') AS 지역
     , b.centcode
     , b.centname
--     , a.cnt
     , a.bongcnt
  FROM (SELECT centcode
--             , SUM(cnt) cnt
             , COUNT(*) bongcnt
          FROM (SELECT centcode
--                     , COUNT(*) cnt
                  FROM resactres
                 WHERE actdate BETWEEN '2014-07-01' AND '2015-06-30'
                 GROUP BY centcode, resno
                HAVING COUNT(*) >= 200
                )
         GROUP BY centcode
        ) a
 INNER JOIN centmst b
    ON a.centcode = b.centcode 
;

 

by 스파이 [2015.07.09 10:29:53]

저도 돌려보니 데이터가 너무 많이 나오더라구요 ㅎㅎ;;;;

그래서 저도 약간 손 봤습니다 ^^; ㅋ

by 스파이 [2015.07.09 10:30:16]

꼼꼼하게 봐 주셔서 감사합니다 ^^ ㅎ

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