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

테이블에 없는 ROW 출력 도와주세요...

by 살별 [SQL Query] [2014.09.25 10:55:36]

WITH T AS(
    SELECT '001' KEY1, '2013' YYYY, '-999' FROM_VALUE, '-10' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2012' YYYY, '-10' FROM_VALUE, '20' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2011' YYYY, '20' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2012' YYYY, '-999' FROM_VALUE, '-2' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2011' YYYY, '-2' FROM_VALUE, '5' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2013'  YYYY, '5' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2012'  YYYY, '-999' FROM_VALUE, '10' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2011'  YYYY, '10' FROM_VALUE, '30' TO_VALUE FROM DUAL
)
SELECT *
  FROM T
 WHERE T.YYYY IN (
                 SELECT '2013' - LEVEL + 1 AS YYYY
                   FROM DUAL
                 CONNECT BY LEVEL <= 3
                 )
 ORDER BY KEY1, YYYY DESC

위와 같은 테이블과 쿼리가 있습니다.

쿼리 결과로 다음과 같은 값을 얻고 싶은데 잘 안되네요 ㅠㅠ

KEY1 YYYY FROM_VALUE TO_VALUE

001   2013   -999   -10
001   2012   -10   20
001   2011   20   999
002   2013   0   0
002   2012   -999   -2
002   2011   -2   5
003   2013   5   999
003   2012   -999   10
003   2011   10   30

위 '002'번과 같이 테이블에 없는 '2013'이 함께 나올 수 있는 쿼리를 만들어야 합니다.

다시말해서 모든 KEY1 데이터에 YYYY 컬럼인 2013, 2012, 2011 가 기준이 되어 나와야 합니다.

도와주세요...ㅠㅠ

by 비주류 [2014.09.25 11:41:52]

WITH T AS(
    SELECT '001' KEY1, '2013' YYYY, '-999' FROM_VALUE, '-10' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2012' YYYY, '-10' FROM_VALUE, '20' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2011' YYYY, '20' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2012' YYYY, '-999' FROM_VALUE, '-2' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2011' YYYY, '-2' FROM_VALUE, '5' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2013'  YYYY, '5' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2012'  YYYY, '-999' FROM_VALUE, '10' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2011'  YYYY, '10' FROM_VALUE, '30' TO_VALUE FROM DUAL
)
SELECT S.KEY1, S.YYYY, NVL(T.FROM_VALUE, 0), NVL(T.TO_VALUE, 0)
  FROM T,
       (
         SELECT *
         FROM   (SELECT LPAD(LEVEL, 3, '0') KEY1
                   FROM DUAL
                 CONNECT BY LEVEL <= 3),
                 (SELECT '2013' - LEVEL + 1 AS YYYY
                   FROM DUAL
                 CONNECT BY LEVEL <= 3)
       ) S
 WHERE S.YYYY = T.YYYY(+)
 AND   S.KEY1 = T.KEY1(+)
 ORDER BY S.KEY1, S.YYYY DESC

by 아발란체 [2014.09.25 11:43:17]

WITH T AS(
    SELECT '001' KEY1, '2013' YYYY, '-999' FROM_VALUE, '-10' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2012' YYYY, '-10' FROM_VALUE, '20' TO_VALUE FROM DUAL
    UNION ALL SELECT '001' KEY1, '2011' YYYY, '20' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2012' YYYY, '-999' FROM_VALUE, '-2' TO_VALUE FROM DUAL
    UNION ALL SELECT '002' KEY1, '2011' YYYY, '-2' FROM_VALUE, '5' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2013'  YYYY, '5' FROM_VALUE, '999' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2012'  YYYY, '-999' FROM_VALUE, '10' TO_VALUE FROM DUAL
    UNION ALL SELECT '003' KEY1, '2011'  YYYY, '10' FROM_VALUE, '30' TO_VALUE FROM DUAL
)
SELECT
  LPAD(gno, 3, '0') AS key1, T2.yyyy,
  TO_NUMBER(NVL(from_value, 0)) from_value,
  TO_NUMBER(NVL(to_value, 0)) to_value
FROM
  T
FULL OUTER JOIN
  (
    SELECT
      ROUND((LEVEL + 1) / 3) AS gno,  --값(년도) 개수로 나누기
      2011 + MOD(LEVEL - 1, 3) AS yyyy  --값 개수로 나머지 구하기
    FROM
      DUAL
    CONNECT BY
      LEVEL <= 9 --키 종류수 * 년도 종류수 = 3 * 3 = 9
  ) T2
ON (
  T.key1 = gno
  AND T.yyyy = T2.yyyy
)
ORDER BY
  gno, T2.yyyy DESC

by 마농 [2014.09.25 11:43:51]

SELECT b.key1
     , a.yyyy
     , NVL(b.from_value, 0) from_value
     , NVL(b.to_value  , 0) to_value
  FROM (SELECT TO_CHAR('2013' - LEVEL + 1) AS yyyy
          FROM dual
         CONNECT BY LEVEL <= 3
        ) a
  LEFT OUTER JOIN t b
  PARTITION BY (b.key1)
    ON a.yyyy = b.yyyy
 ORDER BY b.key1, a.yyyy
;

by 아발란체 [2014.09.25 13:18:49]

PARTITION BY를 몰라서 고민 했는데, 감사합니다 ~ !

by 비주류 [2014.10.01 16:29:22]

outer join partition by 잘 참고하겠습니다 감사합니다

by 살별 [2014.09.25 12:53:15]

우와!! 정말 놀랍습니다.

파티션 아우터 조인이라는 것도 처음보고 이렇게 간단하게 되다니요...이틀을 고민헀던건데.ㅠㅠ

앞에 먼저 답글 달아주신분들도 너무 감사합니다.

도움 감사합니다.

by 백면서생 [2014.09.25 13:36:20]

-- 뒤늦은 model

SELECT * 
FROM T 
MODEL 
PARTITION BY (KEY1) 
DIMENSION BY (YYYY) 
MEASURES (CAST(FROM_VALUE AS VARCHAR2(100)) FROM_VALUE, CAST(TO_VALUE AS VARCHAR2(100)) TO_VALUE) 
RULES (
  FROM_VALUE[FOR YYYY IN ('2011', '2012','2013')] = NVL(FROM_VALUE[CV()], 0)
 ,TO_VALUE[FOR YYYY IN ('2011', '2012','2013')] = NVL(TO_VALUE[CV()], 0) 
) 
ORDER BY KEY1, YYYY

댓글등록

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

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

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

데이터베이스 질문과 답변