210521 oracle - 함수, 그룹함수, GROUP BY, HAVING, ROLLUP, CUBE, GROUPING
※ TO_DATE의 'RR'형식과 'YY'형식
YY 형식은 날짜의 년도부분을 현재의 세기로 해석
RR 형식은 현재 년도와 지정된 년도의 두자리를 비교하여 50년을 기준으로 해석을 달리함
현재연도가 00~49 사이라면 'RR'
01~49까지는 00년대로 해석하고
50~99까지는 1900년대로 해석한다.
※ 문자는 왼쪽정렬, 숫자는 우측정렬
※ 추가 변환함수
TO_TIMESTAMP : CHAR, VARCHAR2 데이터 타입을 TIMESTAMP로 변환
TO_TIMESTAMP_TZ : CHAR, VARCHAR2 데이터 타입을 TIMESTAMP WITH TIME ZONE 타입으로 변환
TO_YMINTERVAL : CHAR, VARCHAR2 데이터 타입을 INTERVAL YEAR TO MONTH 타입으로 변환
TO_DSINTERVAL : CHAR, VARCHAR2 데이터 타입을 INTERVAL DAY TO SECOND 타입으로 변환
※ 기타함수
NVL(column, exp1) : NULL 값을 exp1 값으로 변환
NVL2(column, exp1, exp2) : 해당 컬럼이 NULL일 경우 exp2 값을, NULL이 아니면 exp1 값을 리턴
NULLIF(exp1, exp2) : exp1과 exp2 값을 비교해 같으면 NULL, 다르면 exp1 값을 리턴
COALESCE(exp1, exp2, ..... , expN) : exp1, exp2, ..... , expN 중 null이 아닌 첫 번째 값을 이용
DECODE : if ~ then ~ else 문의 역할 수행 ( 조건부 조회를 쉽게)
[형식]
DECODE(VALUE, IF1, THEN1, IF2, THEN2.....) : VALUE의 값이 IF1일 경우 THEN1값으로 리턴 ....
CASE WHEN 조건식 THEN
EXP1 END : if ~ then ~ else 문의 역할 수행
============================================================================
1. 그룹함수 : GROUP BY 절, GROUP 함수를 선언하고 활용할 수 있다.
2. HAVING절을 사용해서 GROUP 제한을 줄 수 있다.
3. ROLLUP 연산을 사용해서 하위 총계값을 계산할 수 있다.
4. CUBE 연산을 사용해서 모든 열 조합에 대한 그룹함수를 적용할 수 있다.
5. GROUPING 함수를 사용해서 ROLLUP, CUBE를 통해 만들어진 행 값을 식별할 수 있다.
6. GROUPING SETS 절을 사용해서 원하는 조합을 설정할 수 있다.
그룹함수(다중 행 함수 = Multi Row) : 여러개의 ROW데이터를 묶어서 처리한다.
- 여러 행들의 그룹이 모여서 그룹당 하나의 결과를 리턴하는 함수
- SELECT, HAVING, ORDER BY절에 사용될 수 있다.
- SELECT 문장 내의 GROUP BY절은 행들을 그룹화 한다.
- HAVING절은 그룹을 제한하기 위해 사용한다.
- 집합함수, 집계함수, 그룹함수라고도 한다.
종류
COUNT(*) : NULL값을 포함한 행의 수
COUNT(표현식) : 표현식의 값이 NULL값을 제외한 행의 수
SUM ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 합
AVG ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 평균
MIN ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최소값
MAX ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최대값
- 문자, 날짜데이터 사용가능, 나머지는 숫자만
STDDEV ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 표준편차
VARIAN ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 분산
그룹 함수 구문
SELECT 그룹함수
FROM 테이블명
WHERE 조건 ----------- > 그룹의 조건문 사용 불가
GROUP BY 컬럼1, 컬럼2 -------- > 별칭 사용 불가
HAVING 그룹에 대한 조건
ORDER BY 컬럼1, 컬럼2
============================================================================
GROUP BY에 ROLLUP 및 CUBE 연산자 사용
- ROLLUP 및 CUBE 연산자를 질의의 GROUP BY절에 지정할 수 있다.
- ROLLUP 그룹화는 정규 그룹화 행과 하위 총계값을 포함하는 결과 집합을 산출한다.
- CUBE 연산자를 GROUP BY절에 사용하면 지정된 표현식에서 가능한 모든 조합 값에 따라
선택된 행이 그룹화되고 각 그룹에 대한 요약 정보를 나타내는 행이 반환된다.
- CUBE : ROLLUP의 결과 행 및 교차 도표화 행을 포함하는 결과 집합을 산출한다.
ROLLUP 연산자
- 보고서를 작성할 때 집합에서 통계 및 요약정보를 추출하는데 사용할 수 있다.
- GROUP BY 절에 지정된 열 목록을 따라 오른쪽에서 왼쪽 방향으로 하나씩 그룹을 만든다.
그런 다음 그룹함수를 생성한 그룹에 적용한다.
- ROLLUP 연산자 없이 N(GROUP BY 열 수)차원의 하위 총계를 산출하려면
N+1개의 SELECT 문을 UNION ALL로 연결해야 한다. 그러면 모든 SELECET 문이
각각 테이블에 엑세스하므로 질의가 비효율적으로 실행된다.
- 단 한 번 테이블에 액세스하여 해당 결과를 취합한다.
- 하위 총계를 산출하는데 필요한 열이 많은 경우 유용하다.
CUBE : GROUP BY 확장형이다.
- ROLLUP은 하위 총계 조합 중에 일부만 산출하지만 CUBE는 GROUP BY절에 지정된
모든 그룹의 조합에 대해 하위 총계와 최상위 총계를 산출한다.
- 집계함수에 CUBE 연산자를 사용하면 결과 집합에 추가 행이 만들어진다.
- GROUP BY절에 N개의 열이 있을 경우 상위 집계 조합수는 2^N개 이다.
GROUPING
- GROUPING 함수는 ROLLUP, CUBE 연산자와 함께 사용한다.
- 행에서 하위 총계를 형성한 그룹을 찾을 수 있다.
- ROLLUP, CUBE를 통해 만들어진 NULL 값과 저장된 NULL 값을 구별할 수 있다.
- 0 (해당열) 또는 1(집계 연산결과)를 리턴한다.