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

쿼리 문의드립니다.

by 삼만돌이 [SQL Query] 쿼리 [2015.08.31 16:15:29]

이렇게 저렇게 해봐도 값이 이상하게 나옵니다..
그리고 무식하게 하는것 같기도 하고요..

테이블 구조는 아래와 같습니다.

TABLE A

ID	YY	B_YM	A_YM
9999999999	2014	201401	201502
9999999999	2015	201503

TABLE B

ID(TABLE A FK)	YY	MM	STATE
9999999999	2014	1	정상
9999999999	2014	2	정상
9999999999	2014	5	위험
9999999999	2014	9	정상
9999999999	2014	11	정상
9999999999	2015	12	보통
9999999999	2015	1	보통
9999999999	2015	2	약함
9999999999	2015	3	약함
9999999999	2015	4	보통

원하는 결과값은 아래와 같습니다. 즉 2년정도의 개월수가 가로로 나열되고,
TABLE A 의 B_YM, A_YM 간격만큼만 년도별로 나타나는겁니다.. A_YM이 없는경우는 현재 진행중인거고요..

AM1

AM2

AM3

AM4

AM5

AM6

...

BM1

BM2

BM3

BM4

...

999999999

2014

정상

위험

보통

약함

999999999

2015

약함

보통

그래서 아래와 같이 쿼리를 해보고,,

SELECT A.ID,
                   A.B_YM,
                   A.A_YM,
                   (SELECT MAX(CASE WHEN T.MM = 1 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '999999')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM1,
                    (SELECT MAX(CASE WHEN T.MM = 2 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '999999')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM2,
                    (SELECT MAX(CASE WHEN T.MM = 1 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '999999')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM1,
                    (SELECT MAX(CASE WHEN T.MM = 2 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '999999')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM2,
                    (SELECT MAX(CASE WHEN T.MM = 3 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '201604')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM3,
                    (SELECT MAX(CASE WHEN T.MM = 4 AND A.B_YM < T.YY || TO_CHAR(T.MM, 'FM09') AND T.YY || TO_CHAR(T.MM, 'FM09') < NVL(A.A_YM, '201604')
                                    THEN T.STATE END)
                      FROM TABLE_B T
                     WHERE T.ID = A.ID
                    ) AS MM4
              FROM TABLE_A A

또 아래와 같이 해보고

SELECT
          (CASE WHEN B.YY || B.MM = B.YY || '1' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM1,
          (CASE WHEN B.YY || B.MM = B.YY || '2' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM2,
          (CASE WHEN B.YY || B.MM = B.YY || '3' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM3,
          (CASE WHEN B.YY || B.MM = B.YY || '4' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM4,
          (CASE WHEN B.YY || B.MM = B.YY || '5' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM5,
          (CASE WHEN B.YY || B.MM = B.YY || '6' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM6,
          (CASE WHEN B.YY || B.MM = B.YY || '7' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM7,
          (CASE WHEN B.YY || B.MM = B.YY || '8' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM8,
          (CASE WHEN B.YY || B.MM = B.YY || '9' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM9,
          (CASE WHEN B.YY || B.MM = B.YY || '10' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM10,
          (CASE WHEN B.YY || B.MM = B.YY || '11' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM11,
          (CASE WHEN B.YY || B.MM = B.YY || '12' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MM12,
          (CASE WHEN B.YY || B.MM = B.YY || '1' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA1,
          (CASE WHEN B.YY || B.MM = B.YY || '2' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA2,
          (CASE WHEN B.YY || B.MM = B.YY || '3' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA3,
          (CASE WHEN B.YY || B.MM = B.YY || '4' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA4,
          (CASE WHEN B.YY || B.MM = B.YY || '5' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA5,
          (CASE WHEN B.YY || B.MM = B.YY || '6' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA6,
          (CASE WHEN B.YY || B.MM = B.YY || '7' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA7,
          (CASE WHEN B.YY || B.MM = B.YY || '8' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA8,
          (CASE WHEN B.YY || B.MM = B.YY || '9' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA9,
          (CASE WHEN B.YY || B.MM = B.YY || '10' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA10,
          (CASE WHEN B.YY || B.MM = B.YY || '11' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA11,
          (CASE WHEN B.YY || B.MM = B.YY || '12' AND A.B_YM <= B.YY || TO_CHAR(B.MM, 'FM09') AND B.YY || TO_CHAR(B.MM, 'FM09') <= NVL(A.A_YM,'999999') THEN B.STATE END) AS MA12
     FROM TABLE_A A ,
          TABLE_B B
    WHERE A.ID = B.ID

여러가지 방법으로 해봐도 결과는..이상하게 나옵니다... 그룹바이까지 해서 다 해봐도 최종적으로는

AM1

AM2

AM3

AM4

AM5

AM6

...

BM1

BM2

BM3

BM4

...

999999999

2014

정상

위험

정상

999999999

2015

보통

위와 같이 앞에게 뒤에 복사되는 식으로 나와버리네요..

어떻게 해결해야 될까요.. 아무리 해도 방법이 생각나지 않습니다...

by 개발뉴비 [2015.08.31 17:25:55]

B_YM과 A_YM의 기간이 최대 2년을 넘지 않는건가요?

by 삼만돌이 [2015.09.01 09:28:58]

네 최대 2년 입니다.

by 개발뉴비 [2015.08.31 17:46:55]

-- 만약 B_YM과 A_YM의 기간이 2년이 넘지 않는다는 가정하에 짜봤습니다.
WITH TABLE_A (ID, YY, B_YM, A_YM) AS (
    SELECT 999999, '2014', '201401', '201505' FROM DUAL UNION ALL
    SELECT 999999, '2015', '201503', ''       FROM DUAL
)
, TABLE_B (ID, YY, MM, STATE) AS (
    SELECT 999999, '2014', '1',  '정상' FROM DUAL UNION ALL
    SELECT 999999, '2014', '2',  '정상' FROM DUAL UNION ALL
    SELECT 999999, '2014', '5',  '위험' FROM DUAL UNION ALL
    SELECT 999999, '2014', '9',  '정상' FROM DUAL UNION ALL
    SELECT 999999, '2014', '11', '정상' FROM DUAL UNION ALL
    SELECT 999999, '2015', '12', '보통' FROM DUAL UNION ALL
    SELECT 999999, '2015', '1',  '보통' FROM DUAL UNION ALL
    SELECT 999999, '2015', '2',  '약함' FROM DUAL UNION ALL
    SELECT 999999, '2015', '3',  '약함' FROM DUAL UNION ALL
    SELECT 999999, '2015', '4',  '보통' FROM DUAL
)
SELECT ID
     , YY
     , MAX(CASE WHEN MM = 1  AND SYY = 0 THEN STATE END) AM1
     , MAX(CASE WHEN MM = 2  AND SYY = 0 THEN STATE END) AM2
     , MAX(CASE WHEN MM = 3  AND SYY = 0 THEN STATE END) AM3
     , MAX(CASE WHEN MM = 4  AND SYY = 0 THEN STATE END) AM4
     , MAX(CASE WHEN MM = 5  AND SYY = 0 THEN STATE END) AM5
     , MAX(CASE WHEN MM = 6  AND SYY = 0 THEN STATE END) AM6
     , MAX(CASE WHEN MM = 7  AND SYY = 0 THEN STATE END) AM7
     , MAX(CASE WHEN MM = 8  AND SYY = 0 THEN STATE END) AM8
     , MAX(CASE WHEN MM = 9  AND SYY = 0 THEN STATE END) AM9
     , MAX(CASE WHEN MM = 10 AND SYY = 0 THEN STATE END) AM10
     , MAX(CASE WHEN MM = 11 AND SYY = 0 THEN STATE END) AM11
     , MAX(CASE WHEN MM = 12 AND SYY = 0 THEN STATE END) AM12
     , MAX(CASE WHEN MM = 1  AND SYY = 1 THEN STATE END) BM1
     , MAX(CASE WHEN MM = 2  AND SYY = 1 THEN STATE END) BM2
     , MAX(CASE WHEN MM = 3  AND SYY = 1 THEN STATE END) BM3
     , MAX(CASE WHEN MM = 4  AND SYY = 1 THEN STATE END) BM4
     , MAX(CASE WHEN MM = 5  AND SYY = 1 THEN STATE END) BM5
     , MAX(CASE WHEN MM = 6  AND SYY = 1 THEN STATE END) BM6
     , MAX(CASE WHEN MM = 7  AND SYY = 1 THEN STATE END) BM7
     , MAX(CASE WHEN MM = 8  AND SYY = 1 THEN STATE END) BM8
     , MAX(CASE WHEN MM = 9  AND SYY = 1 THEN STATE END) BM9
     , MAX(CASE WHEN MM = 10 AND SYY = 1 THEN STATE END) BM10
     , MAX(CASE WHEN MM = 11 AND SYY = 1 THEN STATE END) BM11
     , MAX(CASE WHEN MM = 12 AND SYY = 1 THEN STATE END) BM12
  FROM (
    SELECT A.ID 
         , A.YY
         , A.B_YM
         , A.A_YM
         , B.MM
         , B.STATE
         , (B.YY - A.YY) SYY 
      FROM TABLE_A A, TABLE_B B
     WHERE A.ID = B.ID(+)
       AND B.YY||LPAD(B.MM, 2, '0') BETWEEN A.B_YM AND NVL(A.A_YM, '999999')  ) GB
 GROUP BY ID, YY
 ORDER BY ID, YY

by 삼만돌이 [2015.09.01 09:36:54]

소중한 답변 감사드립니다..

잘되네요..^^

아 왜 저런생각을 못했을까 싶습니다. 아직도 갈길이 머네요..

by 마농 [2015.09.01 10:17:54]

999999 로 하면 지나치게 많은 조인으로 비효율입니다.
조인의 범위를 제한하는 것이 좋습니다.
- 변경전 : NVL(A.A_YM, '999999')
- 변경후 : NVL(A.A_YM, (A.YY + 1) || '12')

댓글등록

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

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

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

데이터베이스 질문과 답변