데이터베이스 질문과 답변

row 추가 질문드립니다 0 2 1,584

by 모델장윤주 [2015.12.09 12:02:11]

 

 

 

데이터가

날짜          구분      수량

201501       A         11

201501       B         27

201501       C         21

이런식으로 표시된다고 할 때

구분이 D인 값의 수량을 0으로 추가하고자 합니다

날짜는 동일하구게요

쿼리부분은 select substr(날짜,0,6) as날짜

, 구분 , count(*) as from 테이블 where 날짜 between '20150101' and '20150631'

group by 날짜, 구분 입니다

 

nvl과 decode룰 써보려 했으나 잘 이해가 되지 않아서 질문드립니다

추가적으로 where조건문에 의한 날짜 조건은 검색조건에 의해 변경이 가능합니다

감사합니다

모바일 양식이라 부족한점죄송합니다

by 우리집아찌 [2015.12.09 12:56:03] 
/* ROW 추가를 말씀하시는거라면.. */
WITH T AS (
SELECT '201501' DT , 'A' DV , 11 QTY FROM DUAL UNION ALL
SELECT '201501' , 'B' , 27 FROM DUAL UNION ALL
SELECT '201501' , 'C' , 21 FROM DUAL UNION ALL 
SELECT '201502' , 'A' , 21 FROM DUAL UNION ALL
SELECT '201502' , 'B' , 37 FROM DUAL UNION ALL 
SELECT '201502' , 'C' , 31 FROM DUAL 

)

SELECT DT, DV, QTY FROM T
UNION ALL
SELECT DT , 'D' , 0 FROM T
GROUP BY DT
ORDER BY DT , DV

 

by 마농 [2015.12.09 15:26:10] 
WITH data_t AS
( -- Sample Data --
SELECT '20150101' dt, 'A' gb FROM dual
UNION ALL SELECT '20150102', 'A' FROM dual
UNION ALL SELECT '20150103', 'B' FROM dual
UNION ALL SELECT '20150104', 'C' FROM dual
UNION ALL SELECT '20150105', 'C' FROM dual
UNION ALL SELECT '20150106', 'C' FROM dual
UNION ALL SELECT '20150201', 'A' FROM dual
UNION ALL SELECT '20150202', 'B' FROM dual
UNION ALL SELECT '20150203', 'B' FROM dual
UNION ALL SELECT '20150204', 'B' FROM dual
UNION ALL SELECT '20150205', 'D' FROM dual
)
, code_t AS
( -- Code Data --
SELECT 'A' gb FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)

-- 1. Partition Outer Join
SELECT b.ym
     , a.gb
     , NVL(b.cnt, 0) cnt
  FROM code_t a
  LEFT OUTER JOIN
       (SELECT SUBSTR(dt, 1, 6) ym
             , gb
             , COUNT(*) cnt
          FROM data_t
         WHERE dt BETWEEN '20150101' AND '20150631'
         GROUP BY SUBSTR(dt, 1, 6), gb
        ) b
 PARTITION BY (b.ym)
    ON a.gb = b.gb
 ORDER BY ym, gb
;

-- 2. Model --
SELECT *
  FROM (SELECT SUBSTR(dt, 1, 6) ym
             , gb
             , COUNT(*) cnt
          FROM data_t
         WHERE dt BETWEEN '20150101' AND '20150631'
         GROUP BY SUBSTR(dt, 1, 6), gb
        ) b
 MODEL
 PARTITION BY (ym)
 DIMENSION BY (gb)
 MEASURES (cnt)
 IGNORE NAV
 RULES (cnt[FOR gb IN ('A', 'B', 'C', 'D')] = cnt[CV()] )
 ORDER BY ym, gb
;

-- 3. PIVOT/UNPIVOT --
SELECT *
  FROM (SELECT SUBSTR(dt, 1, 6) ym, gb
          FROM data_t
         WHERE dt BETWEEN '20150101' AND '20150631'
        )
 PIVOT (COUNT(*) FOR gb IN ('A' a, 'B' b, 'C' c, 'D' d))
 UNPIVOT (cnt FOR gb IN (a, b, c, d))
 ORDER BY ym, gb
;

 

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