DataBase 수업 기록(17) 함수 - 기타

▶ 기타 함수 

1. 순위

    1) RANK() OVER(ORDER BY 순위구할칼럼 ASC)  : 오름차순 순위, 낮은 값이 1등, ASC는 생략 가능
    2) RANK() OVER(ORDER BY 순위구할칼럼 DESC) : 내림차순 순위, 높은 값이 1등

    - 같은 값이면 같은 등수(동점)로 처리

 

예시) EMPLOYEES 테이블의 사원 정보를 연봉이 높은 순으로 조회하기(연봉 순위를 함께 조회하기)

SELECT
       RANK() OVER(ORDER BY SALARY DESC) AS 연봉순위
     , EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
  FROM
       EMPLOYEES;

 

예시) EMPLOYEES 테이블의 사원 정보를 입사순으로 조회하기(먼저 입사한 사원이 1등)

SELECT
       RANK() OVER(ORDER BY HIRE_DATE ASC) AS 입사순위
     , EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE
  FROM
       EMPLOYEES;

---

2. 그룹화

    OVER(PARTITION BY 그룹화 칼럼)

    그룹화작업을 수행하므로 집계함수(그룹함수)와 함께 사용이 가능함

 

예시)

SELECT 
       DISTINCT DEPARTMENT_ID
     , SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별연봉합계
     , FLOOR(AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)) AS 부서별연봉평균
     , MAX(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별최대연봉
     , MIN(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별최저연봉
     , COUNT(*)    OVER(PARTITION BY DEPARTMENT_ID) AS 부서별사원수
  FROM
       EMPLOYEES
 WHERE
       DEPARTMENT_ID IS NOT NULL;

 

* RANK() 함수와 PARTITION BY를 함께 사용하면 그룹 내 순위 구하기 가능함

 

예시)

SELECT
       RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS 부서내연봉순위
     , EMPLOYEE_ID
     , FIRST_NAME
     , LAST_NAME
     , SALARY
     , DEPARTMENT_ID
  FROM
       EMPLOYEES
 ORDER BY
       DEPARTMENT_ID ASC;

---

3. 분기처리

DECODE(표현식
        , 값1, 결과1
        , 값2, 결과2
        , 값3, 결과3
        , ...)

- 표현식의 결과가 값1이면 결과1 반환, 값2이면 결과2 반환, ...
- 표현식의 결과와 값의 비교는 동등비교(=)만 가능함

 

예시) JOIN 없이 EMPLOYEES 테이블만 이용하여 EMPLOYEE_ID, DEPARTMENT_NAME 조회하기

SELECT
       EMPLOYEE_ID
     , DECODE(DEPARTMENT_ID
        , 10, 'Administration'
        , 20, 'Marketing'
        , 30, 'Purchasing'
        , 40, 'Human Resources'
        , 50, 'Shipping'
        , 60, 'IT') AS 부서명
  FROM
       EMPLOYEES;

예시2)

SELECT
       EMPLOYEE_ID
     , PHONE_NUMBER
     , DECODE(SUBSTR(PHONE_NUMBER, 1, 3) -- 1, 3 = 첫번째 글자부터 3글자만 가지고 와서,
       , '011', 'MOBILE'            -- 011 이면 (SUBSTR이기 떄문에 '' 로 묶어줌)
       , '515', 'EAST'
       , '590', 'WEST'
       , '603', 'SOUTH'
       , '650', 'NORTH') AS REGION
  FROM 
       EMPLOYEES;

---

4. 분기 표현식(IF 느낌)

 CASE
      WHEN 조건식1 THEN 결과값1
      WHEN 조건식2 THEN 결과값2
      ...
      ELSE 결과값N
 END

예시)

 SALARY < 10000 : C
 SALARY < 20000 : B
 SALARY >= 20000 : A

SELECT
       EMPLOYEE_ID
     , SALARY
     , CASE
            WHEN SALARY < 10000 THEN 'C'
            WHEN SALARY < 20000 THEN 'B'
            ELSE 'A'
        END AS 구분
  FROM
       EMPLOYEES;

예시2)

 EMPLOTEE_ID, HRIR_DATE(YYYY-MM-DD), 근무개월개수, 퇴직금정산대상유무 조회하기
 퇴직금정산대상 : 근무개월수가 240개월 이상이면 '정산대상', 아니면 빈 문자열

SELECT
       EMPLOYEE_ID AS 사원번호
     , TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') AS 입사일
     , FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS 근무개월수
     , CASE 
            WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE)  >= 240 THEN '정산대상'
            ELSE ''
       END AS 퇴직금정산대상유무
  FROM
       EMPLOYEES;

 

 

 

 

 

 

 

 

 

 

 

서브쿼리란?1. 개요 - 하나의 SQL문의 결과를 다른 SQL 문에게 전달하기 위해 두 개 이상의 SQL문을 하나의 SQL 문으로 연결하여 처리하는 방법이다.2. 서브쿼리가 필요한 경우* 문제 << 직원테이블에서 '엘리스'직원과 같은 지역에서 근무하는 직원 목록을 출력하라.>>* 처리방식1. 직원테이블에서 '엘리스'직원이 근무하는 지역을 검색하는 SQL문을 작성한다.SELECT 근무지역 FROM 직원 WHERE 성명 = '엘리스';2.'엘리스'와 동일한 지역에서 근무하는 직원 목록을 출력하는 SQL문을 작성한다.SELECT * FROM 직원 WHERE 근무지역 = '앨리스의 근무지역';3. 1과 2를 처리하는 SQL문을 통합하여 하나의 SQL문을 작성한다.

 

* 서브쿼리 개념- 서브쿼리가 먼저 처리되고, 메인쿼리가 나중에 처리된다.

 

예시(POSITION = '과장'POSITION IN ('과장')같은 거임.

 

 

프로시저
: 한번에 수행할 쿼리문이 여러 개인 경우

함수
 : 사용자가 필요한 기능을 함수로 정의

트리거
: 행(ROW) 삽입/수정/삭제 시 자동으로 처리되는 기능 정의
==PLSQL 배운 이유가 이걸 배우려고 배운거다.