'DB'에 해당되는 글 18건

REGEXP_REPLACE : 11g 부터 사용 가능, 특정 문자 제거나 마스킹 시 유용

SELECT UPPER(REGEXP_REPLACE(MODEL_MAC, '[.]+', '')), MODEL_MAC FROM TB_SCUSTMDW
    
SELECT REGEXP_REPLACE('0102345678','^(\d{3})-?(\d{1,2})\d{2}-?\d(\d{3})$', '\1-\2**-*\3 ') AS PN 
FROM DUAL;

SELECT SUBSTR('AB1234560987',1,4) || REGEXP_REPLACE(SUBSTR(SUBSTR(  'AB1234560987', 1, LENGTH('AB1234560987')-4),5),'[0-9]','#') || SUBSTR(   'AB1234560987',-4,4)  AS EX
FROM DUAL;

피벗 : 행을 열로 변환
언피벗 : 열을 행으로 변환

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

 ORA-29275 발생, 아래의 쿼리로 DB 언어 확인

SELECT parameter, VALUE  FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

으로 확인. PC, 서버 DB언어가 맞지 않아 오류가 발생.

KO16MSWIN949 : 8822 한글지원

KO16KSC5601 : 2350 한글 지원

NULLIF(필드1, 필드2) : 필드1값과 필드2값이 동일하면 NULL을 그렇지 않으면 필드1을 반환

NVL(필드1, 필드2) : 필드1값이 NULL이면 필드2 반환

NVL2(필드, 필드1, 필드2) : 필드의 값이 NULL이 아닐 경우에는 필드1의 값을 반환하고, NULL일 경우에는 필드2의 값을 반환 한다.

[Oracle]

※ CREATE [UNIQUE] INDEX [인덱스명] ON [테이블명]([컬럼명], [컬럼명], ...);

여기서 [UNIQUE]는 생략할 수 있고, PK일 경우 [UNIQUE]를 사용

ex) CREATE INDEX INDEX_TEST ON INDEX_TABLE(DATE_FLD, DATE_FLD1) ;

컬럼을 가공하여 INDEX를 생성할수 있음

ex) CREATE INDEX INDEX_TEST ON INDEX_TABLE(TO_DATE(DATE_FLD, 'yyyymmdd'), DATE_FLD1); 

※ DROP INDEX 인덱스명

ex) DROP INDEX INDEX_TEST;

※ INDEX 확인

ex)
    SELECT *
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE='INDEX'
    AND OBJECT_NAME='INDEX_TEST';

[주의사항]

※ INDEX CREATE시나 REBUILD시 테이블에 LOCK이 발생 할 수 있으며, 서비스일 경우 장애가 날 수 있다

※ INSERT, UPDATE, DELETE 시 속도가 저하된다

※ 조회결과가 전체 데이터수의 3~5% 미만일 경우 INDEX SCAN이 효율적이고, 3~5% 이상일 경우는 FULL SCAN이 효율적이다

※ INDEX 컬럼을 다수 설정하였다면 조회조건으로 모두 사용해야 INDEX를 탈 확률이 높아진다

※ INDEX 컬럼을 가공하여 조회조건으로 실행하면 INDEX를 타지 않는다(미리가공)

※ 조회조건에 부정문(<>, NOT), Function이 있을 경우 INDEX를 타지 않는다

※ NVL() 함수를  IFNULL() 함수로 변경한다.

※ SYSDATE 를 NOW() 함수로 변경한다.

※ TRUNC(SYSDATE) 함수를 CURDATE() 함수로 변경한다.

※ Outerjoin 변경 

[Oracle]

left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);

right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;  

[MySQL]

left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;

right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

% From절에서 테이블명 중간에 , 가 없다는것과 ON 구문이 From절 바로 뒤에 오게 해야하는것에 주의 

※ TO_CHAR()를 DATE_FORMAT()으로 변환한다.

TO_CHAR(sysdate,'YYYYMMDD') =>DATE_FORMAT(now(),'%Y%m%d') 

※ TO_DATE()를 DATE_FORMAT()으로 변환한다.

TO_DATE('20061020','YYYYMMDD') => DATE_FORMAT('20061020','%Y%m%d')로 변환

※ Where rownum <= 10 을 limit 10으로 변환한다.

rownum >= 5 와 rownum <= 10 이면 limit 4, 5  

ex) 리스트 페이징에서 11번째부터 20번째까지 limit 10, 20  

   처음부터 10개             ........ LIMIT 10

   5번째부터 10개            ........LIMIT 4, 10

   5번째부터 마지막까지  .... LIMIT 4, -1 

  이때 주의 할 점은 첫번째 레코드의 시작은 “0” 입니다.

  즉 “LIMIT 1, 3 - 2번째부터 3개” 의 결과를 출력하게 됩니다.

※ 'MY'||'S'||'QL'를 CONCAT('MY','S','QL')으로 변환한다.

※ RANK() 는 존재하지 않는다.

SET @RANK := 0, @PREV := '';
SELECT no, @PREV := user_name AS user_name, jumsu
FROM (
      SELECT *
      FROM member
      ORDER BY user_name, jumsu DESC
      ) AS TB
WHERE IF(user_name = @PREV, @RANK := @RANK + 1, @RANK := 1) <= 2;

MySQL 변수 셋팅으로 처리한다.

1. http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html url로 이동 각자 운영체계에 맞는 Client를 다운로드 한다.

2. 다운로드 후, 압축을 푼다.

3. 환경변수 선택 후 새로만들기(ORACLE_HOME) 

4. NLS_LANG 설정

5. TNS_ADMIN 설정

6. Path 설정

7. Oracle 설치된 경로에에서 TNSNAMES.ORA 파일을 위의 TNS_ADMIN 경로로 복사한다.

8. 커맨드창(CMD)에서 DB연결 확인

* 설치 경로중 한글로 된 이름이 있다면 설치가 실행이 정상적으로 되지 않을 경우가 있기때문에 이점을 주의해야 한다.