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

[질문]쿼리에 대한 질문입니다.

by 장현수 [2007.10.09 11:13:41]

-- 기사논조
SELECT (SELECT CODE_NM
          FROM S_CODE_MANAGER
         WHERE CODE_GROUP = 'to'
           AND CODE = A.TONE_CD) AS TONE_NM,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)) AS JANUARY,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)) AS FEBRUARY,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)) AS MARCH,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)) AS APRIL,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)) AS MAY,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)) AS JUNE,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)) AS JULY,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)) AS AUGUST,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)) AS SEPTEMBER,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)) AS OCTOBER,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)) AS NOVEMBER,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)) AS DECEMBER,
       COUNT (*) AS TOTAL
FROM S_ARTICLE A
WHERE A.PUBLISH_DT LIKE '2007%'
GROUP BY TONE_CD

UNION ALL

-- 기사소스
SELECT (SELECT CODE_NM
          FROM S_CODE_MANAGER
         WHERE CODE_GROUP = 'so'
           AND CODE = A.SOURCE_CD) AS SOURCE_NM,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)),
       COUNT (*)
FROM S_ARTICLE A
WHERE A.PUBLISH_DT LIKE '2007%'
GROUP BY SOURCE_CD

UNION ALL

-- 기사유형
SELECT (SELECT CODE_NM
          FROM S_CODE_MANAGER
         WHERE CODE_GROUP = 'ty'
           AND CODE = A.TYPE_CD) AS TYPE_NM,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)),
       COUNT (*)
FROM S_ARTICLE A
WHERE A.PUBLISH_DT LIKE '2007%'
GROUP BY TYPE_CD

UNION ALL

-- 기사주제
SELECT (SELECT CODE_NM
          FROM S_CODE_MANAGER
         WHERE CODE_GROUP = 'su'
           AND CODE = A.SUBJECT_CD) AS SUBJECT_NM,
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)),
       COUNT (*)
FROM S_ARTICLE A
WHERE A.PUBLISH_DT LIKE '2007%'
GROUP BY SUBJECT_CD

UNION ALL

-- 총계
SELECT 'TOTAL',
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)),
       COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)),
       COUNT (*)
FROM S_ARTICLE A
WHERE A.PUBLISH_DT LIKE '2007%'

이와 같이 중복되는 쿼리인데요~

이걸 중복되는 부분을 줄이고 할 수 있는 방법은 있을까요?

아직 제가 초보라서 이런식으로 밖에 안되는군요~

고수님들의 많은 답변 부탁드립니다..

아침저녁으로 쌀쌀한데.. 오라클럽회원님들.. 감기 조심하세요~ ^^*

by 김강환 [2007.10.09 00:00:00]

이렇게 한번 해 보세요
테스트는 못했습니다

SELECT
DECODE(A.CODE_GROUP,'to','TONE_CD','so','SOURCE_CD','ty','SOURCE_CD','su','.SUBJECT_CD') GUBUN
B.CODE_NM,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)) AS JANUARY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)) AS FEBRUARY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)) AS MARCH,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)) AS APRIL,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)) AS MAY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)) AS JUNE,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)) AS JULY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)) AS AUGUST,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)) AS SEPTEMBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)) AS OCTOBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)) AS NOVEMBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)) AS DECEMBER,
COUNT (*) AS TOTAL
FROM S_ARTICLE A,S_CODE_MANAGER B
WHERE A.PUBLISH_DT LIKE '2007%'
AND B.CODE=DECODE(A.CODE_GROUP,'to',A.TONE_CD,'so',A.SOURCE_CD,'ty',A.SOURCE_CD,'su',A.SUBJECT_CD)
GROUP BY TONE_CD

by 장현수 [2007.10.09 00:00:00]

기민용님~~ 항상 고마워요~
지난번에도 비슷한 쿼리로 질문했었는데..
이번에도 민용님이 바로 답변해주시네요~
근데 조금 이해는 하겠네요~
어떻게 이런 쿼리가 나오는지?
한번에 이렇게 머리에 떠오르시나요?
저는 언제쯤.. 이런 경지에 오르려나~
민용님 이 글 보시면 주석도 달아 주셨으면
제가 이해하기 좋을 것 같습니다~~
제가 워낙 머리가 모자라서.. 죄송합니다.
너무 너무 고맙고 감사합니다. ^^*
강환님도.. 답변 감사드려요~
근데 돌아가지는 않네요~
CODE_GROUP은 S_ARTICLE에 없어서 에러나 나네요~
^^* 요긴 대전인데 날씨가 엄청 좋네요~
다들 행복한 하루 되시길. 바랍니다.. ^^*

by 김강환 [2007.10.09 00:00:00]

점심 먹으러 가기 직전 만든 쿼리라 지금 보니 엉망이네요 정말 죄송합니다. 기민용님이 좋은 답변 주셨네요. 혹 시간이 나시면 제 수정쿼리도 제대로 나오는지 테스트 해 주세요. 실테이블 없이 쿼리 만드려닌 무척 헷갈리네요

SELECT
DECODE(B.CODE_GROUP,'to',A.TONE_CD,'so',A.SOURCE_CD,'ty',A.SOURCE_CD,'su',A.SUBJECT_CD) GUBUN
B.CODE_NM,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '01', 1)) AS JANUARY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '02', 1)) AS FEBRUARY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '03', 1)) AS MARCH,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '04', 1)) AS APRIL,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '05', 1)) AS MAY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '06', 1)) AS JUNE,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '07', 1)) AS JULY,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '08', 1)) AS AUGUST,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '09', 1)) AS SEPTEMBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '10', 1)) AS OCTOBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '11', 1)) AS NOVEMBER,
COUNT (DECODE (SUBSTR (A.PUBLISH_DT, 5, 2), '12', 1)) AS DECEMBER,
COUNT (*) AS TOTAL
FROM S_ARTICLE A,S_CODE_MANAGER B
WHERE A.PUBLISH_DT LIKE '2007%'
AND B.CODE=DECODE(B.CODE_GROUP,'to',A.TONE_CD,'so',A.SOURCE_CD,'ty',A.TYPE_CD,'su',A.SUBJECT_CD)
GROUP BY ROLLUP(DECODE(B.CODE_GROUP,'to',A.TONE_CD,'so',A.SOURCE_CD,'ty',A.SOURCE_CD,'su',A.SUBJECT_CD),CODE_NM)
HAVING GROUPING(DECODE(B.CODE_GROUP,'to',A.TONE_CD,'so',A.SOURCE_CD,'ty',A.SOURCE_CD,'su',A.SUBJECT_CD)+GROUPING(CODE_NM) IN(0,2)

댓글등록

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

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

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

데이터베이스 질문과 답변