CASE WHEN , DECODE, 뷰 , 서브쿼리, RANK

유동현·2022년 10월 10일
sql오라클
0

오라클

목록 보기
8/18
post-thumbnail

DECODE

  • 조건문의 일종으로
  • 첫번째 파라미터가 연산할 수식
  • 2.두번째 파라미터가 연산된 결과
  • 3.세번째 파라미터가 2.와 같을경우 내보낼 결과
  • 이후 여러개의 2,3 이 가능하다.
SELECT DECODE(5-2,1,'5-2=1',2,'5-2=2',3,'5-2=3', '5-2는 몰라요') "결과확인"
FROM DUAL;

CASE WHEN

  • 조건문으로써
  • CASE ->WHEN->THEN -> END
                       -> ELSE -> END
  • 와 같은 실행순서를 가진다.
  • 즉 CASE에서 연산한 구문이 WHEN 절과 같을경우 THEN을 아니면 ELSE구문을 수행한다.

일반적으로는

SELECT CASE 1+1 WHEN 2 THEN '1+1=2'
                WHEN 3 THEN '1+1=3'
                WHEN 2 THEN '1+1=4'
                ELSE '몰라'
                END
FROM DUAL;
--==>>1+1=2

이렇게 사용하나 CASE WHEN을 붙여

--자바의 if-else와 같이 하나만 보고 찾으면 끝난다.

SELECT CASE WHEN 5+2 = 4 THEN '5+2=4'
            WHEN 6-3 = 2 THEN '6-3=2'
            WHEN 2*1 = 2 THEN '2*1=2'
            WHEN 6/3 = 3 THEN '6/6=3'
            ELSE 'ㅁ?ㄹ'
    END
    --CASE WHEN을 합쳐서 어떤게 참인지 바로 걸러낼수 있다.
FROM DUAL;

이와같이 사용할 수도있다.

활용예

--CASE WHEN THEN ELSE END (조건문,분기문) 활용

SELECT CASE WHEN 5<2 THEN '5<2'
            WHEN 5>2 THEN '5>2'
            ELSE '5와 2는 비교불가'
        END "결과확인"
FROM DUAL;
--==>>5>2

SELECT CASE WHEN 5<2 OR 3>1 AND 2=2 THEN '소연만세'
            WHEN 5>2  OR 2=3 THEN '시연'
            ELSE '5와 2는 비교불가'
        END "결과확인"
FROM DUAL;


SELECT CASE WHEN 3<1 AND 5<2 OR 3>1 AND 2=2 THEN '보경만세'
            WHEN 5<2 AND 2=3 THEN '은영만세'
            ELSE '현하만세'
        END "결과확인"
FROM DUAL;
                 --F        --T       --F
SELECT CASE WHEN 3<1 AND (5<2 OR 3>1) AND 2=2 THEN '보경만세'
            WHEN 5<2 AND 2=3 THEN '은영만세'
            ELSE '현하만세'
        END "결과확인"
FROM DUAL;

예제

  • 다음과 같은 항목을 조회할 수 있도록 쿼리문을 구성한다.
  • 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일
  • 정년퇴직일, 근무일수, 남은일수, 급여, 보너스
  • 단 현재나이는 기본 한국나이 계산법에 따라 연산을 수행한다.
  • 또한, 정년 퇴직일은 해당 직원의 나이가 한국 나이로 60세가 되는 해의
  • 그 직원의 입사월, 입사일로 연산을 수행한다.
  • 그리고, 보너스는 1000일 이상 2000일 미만 근무한 사원은
  • 그 사원의 원래 급여 기준 30% 지급, 2000일 이상 근무한 사원은
  • 그 사원의 원래 급여 기준 50% 지급을 할 수 있도록 처리
--1.
SELECT TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),1,2))-10
FROM DUAL;
--10 = 20년대

--2.
SELECT TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),1,2))
,TO_NUMBER(EXTRACT(YEAR FROM SYSDATE))
FROM DUAL;
--20	2022

--3.
SELECT  CASE WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('1','2') THEN 19||TO_NUMBER(SUBSTR(JUBUN,1,2))
            ELSE '10'
            END
FROM TBL_SAWON;


--4.
SELECT 60+(19||TO_NUMBER(SUBSTR(JUBUN,1,2)))||SUBSTR(TO_CHAR(HIREDATE),3)
FROM TBL_SAWON;
--5.
SELECT (TO_DATE(60+TO_NUMBER(CASE WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('1','2') THEN 19||(SUBSTR(TO_CHAR(JUBUN),1,2))
             WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('3','4') THEN 20||(SUBSTR(TO_CHAR(JUBUN),1,2))
          ELSE '0'
          END)|| SUBSTR(HIREDATE,5),'YYYY-MM-DD'))
FROM TBL_SAWON;

--결과

SELECT SANO "사원번호" ,SANAME "사원명", JUBUN "주민번호"
       , CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN  '남' ELSE '여' END "성별"
       , 1+TO_NUMBER(EXTRACT(YEAR FROM SYSDATE)) 
       - TO_NUMBER(CASE WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('1','2') THEN '19'||(SUBSTR(JUBUN,1,2))
          WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('3','4') THEN '20'||(SUBSTR(JUBUN,1,2))
          ELSE '-1'
          END) "현재나이"
      , HIREDATE "입사일"
      , TO_CHAR(ADD_MONTHS(SYSDATE,((60-((TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))+1)
       - CASE WHEN SUBSTR(JUBUN,0,2)<22 THEN SUBSTR(JUBUN,0,2)+2000
          ELSE SUBSTR(JUBUN,0,2)+1900
          END))*12)), 'YYYY') ||'-'||TO_CHAR(HIREDATE,'MM-DD')"정년퇴직일"
      , ROUND(SYSDATE - HIREDATE) "근무일수"
      ,ROUND(TO_DATE(59+TO_NUMBER(CASE WHEN SUBSTR(TO_CHAR(JUBUN),7,1) IN ('1','2') THEN '19'||(SUBSTR(TO_CHAR(JUBUN),1,2))
          ELSE 20||(SUBSTR(TO_CHAR(JUBUN),1,2))
          END)||SUBSTR(HIREDATE,5),'YYYY-MM-DD')-SYSDATE) "남은일수"
      ,SAL "급여"
      ,CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE-HIREDATE))>=2000 THEN SAL*0.5 
            WHEN TO_NUMBER(TO_CHAR(SYSDATE-HIREDATE))>=1000 THEN SAL*0.3
             
            ELSE 0
            END "보너스"
            
FROM TBL_SAWON;

주의할점

  • 만약 CASE WHEN THEN 결과값이 숫자인데 ELSE 에 문자를 넣으면
  • 제대로 작성했어도 정체성이 없어 에러가 난다.
  • 고로 ELSE에 숫자를 넣어준다.



뷰(VIEW)

  • 만약 테이블의 데이터가 아닌 데이터를 연산한 과정을 통해 나온값을 사용해야 한다면
  • 조회할때마다 연산한 수식들을 써야할것
  • VIEW를 만들면 연산한 수식을 통해나온 값들을 다시 쿼리문을 작성하지 않아도 간편히 볼수 있다.
--○ SYS계정으로 SCOTT계정에 CREATE VIEW 권한 부여 후 다시 실행
CREATE VIEW VIEW_EMP
AS
SELECT EMPNO, ENAME, SAL, COMM, SAL*12+NVL(COMM,0)"연봉"
FROM EMP;
--==>>View VIEW_EMP이(가) 생성되었습니다.


SELECT *
FROM VIEW_EMP;
--내부적으로는 SELECT EMPNO, ENAME, SAL, COMM, SAL*12+NVL(COMM,0)"연봉"
--FROM EMP;
--쿼리문이 실행되는 것임 , 데이터를 가지고 있는 것이 아님

만약 아래와 같은 쿼리문을 일일히 실행해야 한다면

--서브쿼리(인라인 뷰)
SELECT T.사원명 , T.사원번호, T.주민번호, T.성별, T.현재나이, T.입사일, T.급여
    --정년퇴직일
    --정년퇴직년도    → 해당직원의 나이가 한국나이로 60세가 되는 해
    --현재나이가 ...57세 ...3년 후   2022→ 2025
    --현재나이가 ...28세 ...32년 후  2022→ 2054
    --ADD_MONTHS(SYSDATE, 남은년수*12)
    --                    -------- 
    --                     60 - 현재나이
    --ADD_MONTHS(SYSDATE, (60-현재나이)*12) → 특정날짜
    --TO_CHAR('특정날짜','YYYY')            → 정년퇴직 년도만 추출
    --TO_CHAR('입사일','MM-DD')             → 입사 월일만 추출
    --TO_CHAR('특정날짜','YYYY')|| '-' || TO_CHAR('입사일','MM-DD') -> 정년퇴직일
    --TO_CHAR(ADD_MONTHS(SYSDATE,(60-현재나이)*12),'YYYY') || '-' || TO_CHAR('입사일','MM-DD')
      , TO_CHAR(ADD_MONTHS(SYSDATE,(60-T.현재나이)*12),'YYYY')
      || '-' || TO_CHAR(T.입사일,'MM-DD') "정년 퇴직일"
      
      --근무일수
      --근무일수 = 현재일 - 입사일
      ,TRUNC(SYSDATE - T.입사일) "근무일수"
      
      --남은일수
      --남은일수 = 정년퇴직일 - 현재일
      ,TRUNC(TO_DATE( TO_CHAR(ADD_MONTHS(SYSDATE,(60-T.현재나이)*12),'YYYY')
      || '-' || TO_CHAR(T.입사일,'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"
      
      --급여
      ,T.급여
      
      --보너스
      --근무일수가 1000일 이상 2000일 미만 -> 급여의 30% 지급
      --근무일수가 2000일 이상 -> 급여의 50%지급
      ---------------------------------------------------
      --근무일수가 2000일 이상            -> 급여 * 0.5
      --근무일수가 1000일 이상            -> 급여 * 0.3
      ,CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5
            WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3
            ELSE 0
        END "보너스"
FROM
(   
    SELECT SANO"사원번호", SANAME"사원명", JUBUN"주민번호"
        ,  CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
                WHEN SUBSTR(JUBUN, 7, 1) IN ('2','4') THEN '여성'
                ELSE '성별확인불가'
                END "성별"
          -- 현재나이 = 현재년도 - 태어난년도 + 1(1900년대 /2000년대)
          , CASE WHEN SUBSTR(JUBUN, 7, 1) IN('1', '2') 
                THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) +1899)
                WHEN SUBSTR(JUBUN,7,1) IN('3','4')
                THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) +1999)
                ELSE -1
            END "현재나이"
          -- 입사일
         , HIREDATE "입사일"
          -- 급여
         , SAL "급여"
    FROM TBL_SAWON
)T;
  • 위에서 처리한 내용을 기반으로
  • 특정 근무일수의 사원을 확인해야 한다거나...
  • 특정 보너스 금액을 받는 사원을 확이해야 할 경우가 발생할 수 있다.
  • (즉, 추가적인 조회 조건이 발생하거나, 업무가 파생되는 경우)
  • 이와같은경우..해당 쿼리문을 다시 구성해야 하는 번거로움을 줄일 수 있도록
  • 아래와 같이 뷰(VIEW)를 만들어저장해둘수있다.
CREATE OR REPLACE VIEW VIEW_SAWON --이미 같은 이름의 뷰가 존재하면 REPLACE하고 없으면 생성한다. 
AS
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
      , TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12),'YYYY')
        || '-' || TO_CHAR(T.입사일, 'MM-DD') "정년퇴직일"
    , TRUNC(SYSDATE - T.입사일) "근무일수"
    , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12),'YYYY')
        || '-' || TO_CHAR(T.입사일, 'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"
    , T.급여                          
    , CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5
           WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3
           ELSE 0
           END "보너스"
FROM
(
    SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
    , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1' , '3') THEN '남성' 
           WHEN SUBSTR(JUBUN,7,1) IN ('2' , '4') THEN '여성'
           ELSE '성별확인불가'
           END "성별"
           , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1' , '2') 
                  THEN EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)
                  WHEN SUBSTR(JUBUN,7,1) IN ('3' , '4') 
                  THEN EXTRACT(YEAR FROM SYSDATE) -(TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)
                  ELSE -1
             END "현재나이"
        , HIREDATE "입사일"
        , SAL "급여"
            
    FROM TBL_SAWON
) T;

이때의 장점은

SELECT *
FROM VIEW_SAWON
WHERE 근무일수 >= 5000;

이와 같이 사용할 수 있다는점


순위를 매기는 함수

RANK

  • RANK() : 등수(순위)를 반환하는 함수
  • 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환한다
  • OVER 다음에 PARTITION BY 컬럼1 ORDER BY 컬럼2 하게되면
    컬럼 1의 데이터대로 데이터들을 분할한후 컬럼2에 따라 등수를 매긴다.

SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO"부서번호", SAL "급여" 
       ,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여순위"
       ,RANK() OVER(ORDER BY SAL DESC) "전체급여순위"
FROM EMP
ORDER BY 3, 4 DESC;
/*
7839	KING	10	5000	    1	1
7788	SCOTT	20	3000	    1	2
7566	JONES	20	2975	    3	4
7698	BLAKE	30	2850	    1	5
7499	ALLEN	30	1600	    2	7
7844	TURNER	30	1500	    3	8
7934	MILLER	10	1300	    3	9
7654	MARTIN	30	1250	    4	10
7876	ADAMS	20	1100	    4	12
*/
  • OVER(ORDER BY 컬럼1 DESC) 에서 중복값을 없애고 싶다면
SELECT RANK() OVER(ORDER BY 컬럼1 DESC, 컬럼2 DESC)
FROM DUAL;

와 같이 더 자세한 분석기준을 부여하면 된다.


DENSE RANK

  • DENSE_RANK() -> 서열을 반환하는 함수 위의 RANK는
    동일 등수가 있으면 그만큼 뒤로 밀려서 나오지만 이건 아님

SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여"
     , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여서열"     
     , DENSE_RANK() OVER(ORDER BY SAL DESC) "전체급여서열"     
FROM EMP
ORDER BY 3,4 DESC;
--==>>
/*
7839   KING   10   5000   1    1
7782   CLARK   10   2450   2    5
7934   MILLER   10   1300   3    8
7902   FORD   20   3000   1     2
7788   SCOTT   20   3000   1     2
7566   JONES   20   2975   2    3
7876   ADAMS   20   1100   3   10

응용법

SELECT T.*
FROM(
SELECT ENAME "사원명" ,DEPTNO "부서번호", SAL*12+NVL(COMM,0) "연봉"
       , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉등수"
       , RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉등수"
FROM EMP
) T
WHERE T.부서내연봉등수<=2;

위와 같이 컬럼뿐만 아니라 연산식도 들어갈 수있고
인라인 뷰를 사용한다면 WHERE절을 사용하여 원하는 등수만 뽑아낼 수도 있다.

profile
유동현
이전 포스트

오라클의 날짜(DATE)

다음 포스트

오라클의 그룹 함수들, GROUP BY ,CUBE

0개의 댓글