[60일차] 변환 함수 / NULL 관련 함수 / 기타 함수 / GROUP BY 절과 HAVING 절 / ROLLUP 절과 CUBE 절 / 집합 연산자

20221019(수)

• 목차 
  - 변환 함수
  - NULL 관련 함수
  - 기타 함수
  - GROUP BY 절과 HAVING 절
  - ROLLUP 절과 CUBE 절
  - 집합 연산자

---

< 변환 함수 >

• 서로 다른 유형의 데이터 타입으로 변환해 결과를 반환하는 함수
• 명시적 형변환 : 변환 함수를 통해 형변환을 직접 처리하는 것

TO_CHAR (숫자 혹은 날짜, format)	숫자나 날짜 ➡ 문자로 변환해 주는 함수
TO_NUMBER(expr, format)	문자나 다른 유형의 숫자 ➡ NUMBER 형
TO_DATE(char, format)	문자 ➡ 날짜
TO_TIMESTAMP(char, format)	TO_DATE ➡ DATE  /  TO_TIMESTAMP ➡ TIMESTAMP

---

< NULL 관련 함수 >

• NULL을 비교할 때는 IS NULL이나 IS NOT NULL 구문을 사용하였는데, 
• 오라클에서는 NULL을 연산 대상으로 처리하는 SQL 함수를 제공

NVL(expr1, expr2)	expr1이 NULL일 때 expr2를 반환
NVL2(expr1, expr2, expr3)	NVL을 확장한 함수 expr1이 NULL이 아니면 expr2를, NULL이면 expr3를 반환하는 함수
COALESCE (expr1, expr2, …)	매개변수로 들어오는 표현식에서  NULL이 아닌 첫 번째 표현식을 반환하는 함수
LNNVL(조건식)	매개변수로 들어오는 조건식의 결과가  FALSE나 UNKNOWN ➡ TRUE를, TRUE ➡ FALSE를 반환
NULLIF (expr1, expr2)	expr1과 expr2을 비교해 같으면 NULL을, 같지 않으면 expr1을 반환

 

---

< 기타 함수 >

GREATEST(expr1, expr2, …)	가장 큰 값 반환
LEAST(expr1, expr2, …)	가장 작은 값 반환
DECODE  (expr, search1, result1,  search2, result2, …, default)	expr과 search1을 비교해 두 값이 같으면 result1을 같지 않으면 다시 search2와 비교해 값이 같으면 result2를 반환하고 이런 식으로 계속 비교한 뒤 최종적으로 같은 값이 없으면 default 값을 반환

---

< 기본 집계 함수 >

• 대상 데이터를 특정 그룹으로 묶은 다음 이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수

COUNT (expr)	전체 로우 수를 반환 테이블 전체 로우는 물론 WHERE 조건으로 걸러진 로우 수를 반환 *expr이 NULL이 아닌 건에 대해서만 로우의 수를 반환
SELECT COUNT (DISTINCT department_id)	DISTINCT를 붙이면 뒤따라 나오는 컬럼에 있는 유일한 값만 조회된다.
SUM(expr)	전체 합계를 반환
AVG(expr)	평균값을 반환
MIN(expr), MAX(expr)	MIN과 MAX는 각각 최솟값과 최댓값을 반환
VARIANCE(expr) & STDDEV(expr)	VARIANCE는 분산    - 주어진 범위의 개별 값과 평균값과의 차이인 편차를 구해 이를 제곱해서 평균한 값    - 제곱한 평균 STDDEV는 표준편차    - 분산 값의 제곱근 *통계에서는 평균을 중심으로 값들의 분포도를 나타내는 수치인 표준편차를 지표로 사용한다.

 

---

< GROUP BY 절과 HAVING 절 >

(1) GROUP BY 절

• 전체가 아닌 특정 그룹으로 묶어 데이터를 집계
• 그룹으로 묶을 컬럼명이나 표현식을 GROUP BY 절에 명시해서 사용
• GROUP BY 구문은 WHERE와 ORDER BY절 사이에 위치한다.

    SELECT department_id, SUM(salary)
      FROM employees
     GROUP BY department_id
     ORDER BY department_id;

 

(2) HAVING 절

• HAVING 절은 GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 수행
• 즉 HAVING 필터 조건 형태로 사용
  
  
• 예를 들어, 위 쿼리 결과에서 대출잔액이 100조 이상인 건만 추출한다면 다음과 같이 쿼리를 작성하면 된다.

    SELECT period, region, SUM(loan_jan_amt) totl_jan
      FROM kor_loan_status
     WHERE period = '201311'
     GROUP BY period, region
    HAVING SUM(loan_jan_amt) > 100000
    ORDER BY region;

 

---

< ROLLUP 절과 CUBE 절 >

(1) ROLLUP(expr1, expr2, …)

• GROUP BY절에서 사용되어 그룹별 소계를 추가로 보여 주는 역할
• expr로 명시한 표현식을 기준으로 집계한 결과, 즉 추가적인 집계 정보를 보여 준다. 
• 명시한 표현식 수와 순서(오른쪽에서 왼쪽 순으로)에 따라 레벨별로 집계한 결과가 반환된다. 
• 표현식 개수가 n개이면 n+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계

    SELECT period, gubun, SUM(loan_jan_amt) totl_jan
      FROM kor_loan_status
     WHERE period LIKE '2013%'
     GROUP BY period, gubun
     ORDER BY period;

 

(2) CUBE(expr1, expr2, …)

• 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과를 반환
• CUBE는 2의(expr 수)제곱 만큼 종류별로 집계 된다. 
  (예를 들어, expr 수가 3이면, 집계 결과의 유형은 총 2^3^ = 8개가 된다.)

    SELECT period, gubun, SUM(loan_jan_amt) totl_jan
      FROM kor_loan_status
     WHERE period LIKE '2013%'
     GROUP BY CUBE(period, gubun) ;

 

---

< 집합(Set) 연산자 >

• 데이터 집합을 대상으로 연산을 수행하는 연산자

UNION	합집합 / 각 집합 원소(SELECT 결과)를 모두 포함한 결과가 반환
UNION ALL	UNION과 비슷한데 한 가지 다른 것은 중복된 항목도 모두 조회된다는 점
INTERSECT	합집합이 아닌 교집합 / 즉 데이터 집합에서 공통된 항목만 추출해 낸다.
MINUS	차집합 / 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출