버글버글
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 배운 이유가 이걸 배우려고 배운거다.
'Database > database 수업 기록' 카테고리의 다른 글
DataBase 수업 기록(18.5) DQL 연습 (0) | 2022.09.14 |
---|---|
DataBase 수업 기록(18) DQL그룹 (0) | 2022.09.13 |
DataBase 수업 기록(16) 함수 - 문자 (0) | 2022.09.11 |
DataBase 수업 기록(15) 함수 - 날짜 (0) | 2022.09.10 |
DataBase 수업 기록(14) 함수 - 수학 (0) | 2022.09.09 |