[Database] 함수 - 집합연산자와 분석함수

[Database] 여러 함수

---

집합연산자

조회(SELECT) 쿼리를 대상으로 연산을 수행하는 연산자이다.
▶ UNION, UNION ALL, INTERSECT, MINUS 등이 있다.

조회의 결과를 대상으로 수행하기 때문에, 여러 개의 SELECT문을 하나의 쿼리로 만드는 연산자이다.

UNION

UNION 함수는 합집합이라고 생각하면 된다. 
서로 다른 두 테이블에서 원하는 부분만 선택하여 출력할 수 있다. 
중복을 제거한 결과의 합을 검색

첫 번째 조회할 데이터

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '04%';

두 번째 조회할 데이터

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;

두 테이블의 결과를 UNION을 통해 합쳐서 검색하는 방법

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '04%'
UNION
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;

주의사항!!

컬럼 개 수가 일치해야한다.
▶ 컬럼 개 수가 일치하지 않으면, 검색이 되지않을 뿐더러 에러가 발생한다.

---

UNION ALL

UNION ALL 은 UNION과 거의 동일하다고 보면 되지만, 차이점은 중복된 항목도 모두 조회한다는 것이다.

▶ 가상 테이블을 만들 때 주로 사용

UNION ALL의 경우 UNION과 표기방법이 동일하다.

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '04%'
UNION ALL
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;

UNION과 검색된 결과를 확인하면, 동일 구문 내 검색 결과의 차이가 발생하는 것을 볼 수 있다.
▶ 중복을 제거하지 않고 포함하는 것이 UNION ALL과 UNION의 차이이다.

UNION ALL 경우도 UNION과 마찬가지로 조회할 컬럼의 수를 신경써야 한다.

• 가상테이블과 합칠 경우

SELECT EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES
UNION ALL
SELECT 200, 'HONGKIDONG' FROM DUAL
UNION ALL
SELECT 400, 'DULI' FROM DUAL;

---

INTERSECT

INTERSECT는 교집합을 의미한다.

INTERSECT 교집합

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '04%'
INTERSECT
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;

---

MINUS

MINUS는 차집합을 의미한다. SELECT문을 기준으로, 다른 SELECT문과 공통된 레코드를 제외한 항목을 출력한다.

MINUS 차집합

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '04%'
MINUS
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;

---

분석 함수

총합, 평균 등과 같은 데이터 분석을 위해 복수행 기준의 데이터를 모아 처리하는 다중행 함수이다.
복수행을 그룹별로 묶어 각 각의 그룹당 단일 계산 결과를 반환한다.

▶ 각 WINDOW 별 집합 연산을 수행한 결과를 RETURN
▶ JOIN 이나 프로그램의 부하(Over Head)를 줄인다.
▶ 간결한 SQL 구문으로 복잡한 분석작업을 수행가능하다.
▶ 이해 및 활용에 용이하다.

SELECT column1, column2, column3, ......,
               ANALYTIC_FUNCTION(args)
               OVER (
                            [ PARTITION BY column ]
                            [ ORDER BY column ]
                            [ WINDOWING 절 (ROWS | Range Between ]
                            )
FROM 테이블

• ANALYTIC FUNCTION : 분석할 함수명
• OVER : 분석함수를 나타내는 키워드
• PARTITION BY : 계산 대상 그룹
• ORDER BY : 정렬 수행
• WINDOWING : 분석함수의 계산 대상 범위 지정 ( ORDER BY 절에 종속적 )

---

분석함수의 종류

▶ 가장 상위 3가지를 가장 많이 사용함.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
       RANK()
       OVER(ORDER BY SALARY DESC) AS 등수,
       DENSE_RANK()
       OVER(ORDER BY SALARY DESC) AS 중복없는등수,
       ROW_NUMBER()
       OVER(ORDER BY SALARY DESC) AS 순서
FROM EMPLOYEES

ROW_NUMBER 와 기본 함수 ROWNUM과의 차이

ROW_NUMBER는 조회한 기준으로 순서를 매기지만,
ROWNUM은 기본적으로 순서가 매겨져있는 상태로 조회가 된다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, ROWNUM
FROM EMPLOYEES ORDER BY SALARY DESC;