데이터베이스 질문과 답변

쿼리 문의드립니다. 0 5 2,071

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이 없는경우는 현재 진행중인거고요..

ID YY 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

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

ID YY 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- 만약 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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입