피벗 : 행을 열로 변환
언피벗 : 열을 행으로 변환
Oracle 11g 부터 가능
select A.*, case when L1 = '무선단절' and L2 = '신호미약' then '0101' when L1 = '무선단절'
and L2 = '간섭' then '0102' when L1 = '무선단절'
and L2 = 'AP 리셋' then '0103' when L1 = '무선단절'
and L2 = '원인미상' then '0104' when L1 = '무선품질'
and L2 = '신호미약' then '0201' when L1 = '무선품질'
and L2 = '재접속' then '0202' when L1 = '연결'
and L2 = '댁내' then '0301' when L1 = '연결'
and L2 = 'NW' then '0302' when L1 = '연결'
and L2 = 'IP망' then '0303' when L1 = '연동'
and L2 = '단말' then '0401' when L1 = '연동'
and L2 = '서버' then '0402' when L1 = '연동'
and L2 = '기타' then '0403' when L1 = '절단'
and L2 = '호절단' then '0501' when L1 = '음질'
and L2 = '음질불량(평균)' then '0601' when L1 = '음질'
and L2 = '음질불량(최소)' then '0602' when L1 = '음질'
and L2 = 'OneWay Call' then '0603' when L1 = '기타'
and L2 = '원인미상' then '0701' end as middlecode
from (
select substr(PTYPE, 0, instr (PTYPE, ' ', 1, 1) - 1) as L1, substr(PTYPE, instr (PTYPE, ' ', 1, 1) + 1,
length(PTYPE) - instr (PTYPE, ' ', 1, 1)) L2, CALL_TRY_FAIL, CUST_CNT, VOC_CNT,
TOTAL_CUST_CNT, round(CALL_TRY_FAIL / TOTAL_CUST_CNT * 100, 8) as RATE
from (
select PTYPE, sum(val) as CALL_TRY_FAIL, count(distinct (case when PTYPE = '무선단절 신호미약'
and VAL > 0 then CUST_NUM when PTYPE = '무선단절 간섭'
and VAL > 0 then CUST_NUM when PTYPE = '무선단절 AP 리셋'
and VAL > 0 then CUST_NUM when PTYPE = '무선단절 원인미상'
and VAL > 0 then CUST_NUM when PTYPE = '무선품질 신호미약'
and VAL > 0 then CUST_NUM when PTYPE = '무선품질 재접속'
and VAL > 0 then CUST_NUM when PTYPE = '연결 댁내'
and VAL > 0 then CUST_NUM when PTYPE = '연결 NW'
and VAL > 0 then CUST_NUM when PTYPE = '연결 IP망'
and VAL > 0 then CUST_NUM when PTYPE = '연동 단말'
and VAL > 0 then CUST_NUM when PTYPE = '연동 서버'
and VAL > 0 then CUST_NUM when PTYPE = '연동 기타'
and VAL > 0 then CUST_NUM when PTYPE = '절단 호절단'
and VAL > 0 then CUST_NUM when PTYPE = '음질 음질불량(평균)'
and VAL > 0 then CUST_NUM when PTYPE = '음질 음질불량(최소)'
and VAL > 0 then CUST_NUM when PTYPE = '음질 OneWay Call'
and VAL > 0 then CUST_NUM when PTYPE = '기타 원인미상'
and VAL > 0 then CUST_NUM else null end)) as CUST_CNT, sum((case when PTYPE = '무선단절 신호미약'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 간섭'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 AP 리셋'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 원인미상'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선품질 신호미약'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선품질 재접속'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 댁내'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 NW'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 서버'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 단말'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 서버'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 기타'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '절단 호절단'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 음질불량(평균)'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 음질불량(최소)'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 OneWay Call'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '기타 원인미상'
and VAL > 0 and QTY_VOC > 0 then QTY_VOC else 0 end)) as VOC_CNT
from (
select DATE_FLD, DATE_FLD1, CUST_NUM, MODEL_TYPE, MODEL_DEV_NM, MODEL_MAC,
MODEL_FW_TYPE, TEL_NO, FAIL_TYPE, QTY_VOC, CALL_TRY_FAIL_CNT, PTYPE, VAL
from (
select *
from TB_SCLLUxxx
where 1 = 1 and DATE_FLD1 >= 1411484400
and DATE_FLD1 < 1411570800 and MODEL_TYPE in (21, 30, 31)) UNPIVOT (VAL FOR
PTYPE in (WD_SIGNAL as '무선단절 신호미약', WD_INTERFER as '무선단절 간섭', WD_APRESET as '무선단절 AP 리셋',
WD_ETC as '무선단절 원인미상', WQ_SIGNAL as '무선품질 신호미약', WQ_RECONN as '무선품질 재접속',
CF_HOMENET as '연결 댁내', CF_NETWORK as '연결 NW', CF_IPNET as '연결 IP망', IF_MODEL as '연동 단말',
IF_SERVER as '연동 서버', IF_ETC as '연동 기타', CALL_DISCONN as '절단 호절단', BAD_CALL_AVG
as '음질 음질불량(평균)', BAD_CALL_MIN as '음질 음질불량(최소)', BAD_CALL_OWC as '음질 OneWay Call',
TOTAL_ETC as '기타 원인미상')))
group by PTYPE), (
select sum(CUST_CNT) as TOTAL_CUST_CNT
from (
select distinct model_dev_nm, model_fw_type
from TB_SCLLUxxx
where 1 = 1 and DATE_FLD1 >= 1411484400
and DATE_FLD1 < 1411570800 and MODEL_TYPE in (21, 30, 31)) MDL left join (
select model_Dev_nm, data_Dev_nm as model_fw_type, sum(cust_cnt) as CUST_CNT
from tb_susrsxxx
where 1 = 1 and DATE_FLD1 = 1411484400
and data_dev = 5 and svc_type = 2 and MODEL_TYPE in (21, 30, 31)
group by model_dev_nm, data_dev_nm) USR on MDL.MODEL_DEV_NM = USR.MODEL_DEV_NM
and MDL.MODEL_FW_TYPE = USR.MODEL_FW_TYPE)) A
order by middlecode
'DB' 카테고리의 다른 글
[Oracle] with 절 (0) | 2014.10.27 |
---|---|
[Oracle]REGEXP_REPLACE (0) | 2014.09.29 |
[Oracle]Oracle 언어 확인 및 설정 (0) | 2014.06.09 |
[Oracle]NVL, NVL2, NULLIF (0) | 2014.01.20 |
[Oracle]Oracle INDEX (0) | 2012.11.14 |