[ORACLE] 특정 조건을 가진 회원 조회 (TO_DATE, SUBSTR사용하기)

현재 투입된 프로젝트 DB로 쿼리 짜는 연습을 하고 있는데 새롭게 알게된 내용을 정리하는 것뿐만 아니라 지저분한 쿼리를 조금씩 개선해 나가는 과정을 기록하고 싶어서 블로그에 남겨보기로 했다.

 

미션

• 이름 중 가운데 '용'인 사람 또는 마지막이 '호'인 사람
• 대상 회원의 만나이
• 태어난날의 주차
• 태어난날의 요일
• 정규식 이메일 값이 있는 사람
• 생일과 대상회원의 등록일자와 차이를 시간으로 표시 
• 전화번호 가운데 4자리와 뒷 4자리 숫자값의 차이
• 이메일 중 id만 추출

 

어려움

 

1. 태어난 날의 주차를 구하기 위해서는 YYYYMMDD 형식의 출생년도가 필요했는데 테이블에는 YYMMDD형식으로 되어있다.

2. 회원 테이블에 있는 정보가 온전하지 않은 경우가 많았음. (주민번호 뒷자리가 아예 0000000로 되어있는 회원이라거나..)

 

해결

• 이름 중 가운데 '용' 인 사람 또는 마지막이 '호'인 사람

WHERE NAME LIKE '_용_' || '%호'

 

• 주민번호로 대상회원 생년월일 구하기 

주민번호 뒷자리 맨 앞 숫자가 1 또는 2 인 경우 19를, 3 또는 4인 경우 20을 붙였다.

데이터가 이상한것들이 많았는데 그 중 뒷자리 모두 000000로 되어있는 회원은 2020년도 이전인지만 확인해서 19를 붙였다.

 TO_DATE(CASE WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) IN ('1','2') THEN '19'
	 	WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) in ('3', '4') THEN '20'
       		WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 6) = '000000' 
            		AND SUBSTR(CUBO.DEC(JUMIN), 1, 1) <> 0 THEN '19' END
         || SUBSTR(CUBO.DEC(JUMIN), 0, 6) , 'YYYYMMDD') AS YEAR

 

위에서 구한 생년월일을 여러번 재가공 할 예정으로 아예 한 번 더 싸서 from절에 넣어주기로 했다.

SELECT NAME
    , CUBO.DEC(JUMIN)
    , TO_DATE(CASE WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) IN ('1', '2') THEN '19'
    		   WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) IN ('3', '4') THEN '20'
                   WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 6) = '0000000' 
                   		AND SUBSTR(CUBO.DEC(JUMIN), 1, 1) <> 0 THEN '19' END
              || SUBSTR(CUBO.DEC(JUMIN), 0, 6), 'YYYYMMDD') AS YEAR
    , TO_NUMBER(SUBSTR(CUBO.DEC(CPNO), 4, 4)) AS FRONT_NUMBER
    , TO_NUMBER(SUBSTR(CUBO.DEC(CPNO), 8, 4)) AS BACK_NUMBER
    , CUBO.DEC(EMAIL) 
FROM USER
WHERE( SUBSTR(NAME, 2, 1) LIKE '용' OR SUBSTR(NAME, 3, 1) LIKE '호')
AND LENGTH(CUBO.DEC(JUMIN)) = 13
AND REGEXP_LIKE(CUBO.DEC(EMAIL),'@')

 

• 대상 회원의 만나이

MONTH_BETWEEN으로 위에서 구한 생년월일과 오늘날짜(SYSDATE) 을 비교한 다음 12개월로 나눈 다음 나머지를 버려준다(FLOOR)

 

• 태어난 날의 주차 

TO_CHAR(date,'w')를 하면 date에 해당하는 날짜가 몇 주차인지를 확인 할 수 있다.

TO_CHAR(TO_DATE(CASE WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) IN ('1','2') THEN '19'
			WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) in ('3', '4') THEN '20'
			WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 6) = '000000' AND SUBSTR(CUBO.DEC(JUMIN), 1, 1) <> 0 THEN '19' END
            || SUBSTR(CUBO.DEC(JUMIN), 0, 6) , 'YYYYMMDD'),'w')

 

• 태어난 날의 요일 

TO_CHAR(date,'dy')로 date의 요일을 구한다.

TO_CHAR(TO_DATE(CASE WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) IN ('1','2') THEN '19'
			WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 1) in ('3', '4') THEN '20'
			WHEN SUBSTR(CUBO.DEC(JUMIN), 7, 6) = '000000' AND SUBSTR(CUBO.DEC(JUMIN), 1, 1) <> 0 THEN '19' END
            || SUBSTR(CUBO.DEC(JUMIN), 0, 6) , 'YYYYMMDD'),'dy')

 

• 정규식 이메일이 있는 회원

REGEXP_LIKE는 정규표현식을 활용하여 더 효율적으로 검색할 수 있다.

WHERE REGEXT_LIKE(CUBO.DEC(EMAIL), '@');

 

• 전화번호 가운데 4자리와 뒷 4자리 숫자값의 차이

abs() 함수는 정수 인수 n의 절대값을 리턴.

abs(front_number - back_number) as diff

 

• 이메일 중 id만 추출

substr(email, 0, instr(email, '@') -1) as id