쿼리에 대한 검색결과 단축에 질문드립니다. 0 0 680

by 장현수 [2007.08.16 10:57:49]


-- 논조
SELECT   atc_tone,
         round(sum(decode(publish_month, '01', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '01'), 1) AS january,
         round(sum(decode(publish_month, '02', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '02'), 1) AS february,
         round(sum(decode(publish_month, '03', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '03'), 1) AS march,
         round(sum(decode(publish_month, '04', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '04'), 1) AS april,
         round(sum(decode(publish_month, '05', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '05'), 1) AS may,
         round(sum(decode(publish_month, '06', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '06'), 1) AS june,
         round(sum(decode(publish_month, '07', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '07'), 1) AS july,
         round(sum(decode(publish_month, '08', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '08'), 1) AS august,
         round(sum(decode(publish_month, '09', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '09'), 1) AS september,
         round(sum(decode(publish_month, '10', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '10'), 1) AS october,
         round(sum(decode(publish_month, '11', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '11'), 1) AS november,
         round(sum(decode(publish_month, '12', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '12'), 1) AS december,
         round(count(*) * 100 / (select count(*) from cz_test_2 where publish_year = '2006'), 1) AS total
    FROM cz_test_2
   WHERE publish_year = '2006'
GROUP BY atc_tone

UNION ALL

-- 소스
SELECT   atc_source,
         round(sum(decode(publish_month, '01', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '01'), 1),
         round(sum(decode(publish_month, '02', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '02'), 1),
         round(sum(decode(publish_month, '03', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '03'), 1),
         round(sum(decode(publish_month, '04', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '04'), 1),
         round(sum(decode(publish_month, '05', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '05'), 1),
         round(sum(decode(publish_month, '06', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '06'), 1),
         round(sum(decode(publish_month, '07', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '07'), 1),
         round(sum(decode(publish_month, '08', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '08'), 1),
         round(sum(decode(publish_month, '09', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '09'), 1),
         round(sum(decode(publish_month, '10', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '10'), 1),
         round(sum(decode(publish_month, '11', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '11'), 1),
         round(sum(decode(publish_month, '12', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '12'), 1),
         round(count(*) * 100 / (select count(*) from cz_test_2 where publish_year = '2006'), 1) AS total
    FROM cz_test_2
   WHERE publish_year = '2006'
GROUP BY atc_source

UNION ALL

-- 유형
SELECT   atc_type,
         round(sum(decode(publish_month, '01', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '01'), 1),
         round(sum(decode(publish_month, '02', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '02'), 1),
         round(sum(decode(publish_month, '03', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '03'), 1),
         round(sum(decode(publish_month, '04', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '04'), 1),
         round(sum(decode(publish_month, '05', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '05'), 1),
         round(sum(decode(publish_month, '06', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '06'), 1),
         round(sum(decode(publish_month, '07', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '07'), 1),
         round(sum(decode(publish_month, '08', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '08'), 1),
         round(sum(decode(publish_month, '09', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '09'), 1),
         round(sum(decode(publish_month, '10', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '10'), 1),
         round(sum(decode(publish_month, '11', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '11'), 1),
         round(sum(decode(publish_month, '12', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '12'), 1),
         round(count(*) * 100 / (select count(*) from cz_test_2 where publish_year = '2006'), 1) AS total
    FROM cz_test_2
   WHERE publish_year = '2006'
GROUP BY atc_type

UNION ALL

-- 주제
SELECT   atc_subject,
         round(sum(decode(publish_month, '01', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '01'), 1),
         round(sum(decode(publish_month, '02', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '02'), 1),
         round(sum(decode(publish_month, '03', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '03'), 1),
         round(sum(decode(publish_month, '04', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '04'), 1),
         round(sum(decode(publish_month, '05', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '05'), 1),
         round(sum(decode(publish_month, '06', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '06'), 1),
         round(sum(decode(publish_month, '07', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '07'), 1),
         round(sum(decode(publish_month, '08', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '08'), 1),
         round(sum(decode(publish_month, '09', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '09'), 1),
         round(sum(decode(publish_month, '10', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '10'), 1),
         round(sum(decode(publish_month, '11', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '11'), 1),
         round(sum(decode(publish_month, '12', 1, 0)) * 100 / (select count(*) from cz_test_2 where publish_year = '2006' and publish_month = '12'), 1),
         round(count(*) * 100 / (select count(*) from cz_test_2 where publish_year = '2006'), 1) AS total
    FROM cz_test_2
   WHERE publish_year = '2006'
GROUP BY atc_subject

 

지금 제가 SQL 쿼리를 작성하는 있는데..

총 7000개 정도의 DB에서 검색하고자 하려고 합니다.

검색결과가 정해진 폼으로 나와야 하기 때문에..

맞추다 보니.. 쿼리가 쫌.. 보기는.. 엉성합니다.

맨처음에 작성할 당시에 9초가 걸리더군요..

시스템이 느려서 그런것 같기도 하지만..

고민 고민해서 지금 작성한 것처럼 해서 5초대로 나와는군요~

어떻게 하면 더 빠르게 검색결과를 단축할 수 있을까요?

제가 작성한 쿼리에 대해서 많이 봐주시고 조금이나마

단축할 수 있는 방법을 고수 분들이 조언해주셨으면 합니다.

오늘과 내일 폭염주의보라는데~ 건강 조심하시길.. *^_^*

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