[산대특] Oracle10g 표준 내장 함수, Group by, dual

1001

 

  GROUP BY

  GROUP BY는 쿼리할 대상을 그룹으로 묶을 때 사용

그룹화 할 대상을 GROUP BY 절에 명시해서 그룹화한 뒤,

그 그룹에 SQL 집합함수를 사용해서 다양한 결과를 얻을 수 있다. 

GROUP BY는 보통 집합함수 AVG(), SUM(), MIN(), MAX() 등과 함께 사용. 

일단 그룹화 한 경우에는 그룹행 함수와 group by에 지정된 컬럼만 사용가능.

 

  함수 안에 함수를 넣을 수 있다.

 

  group by ~ 도 일종의 where ~ 와 같은 조건절로 볼 수 있다.

  group by ~ 문에 조건을 넣는다면 where ~가 아니라 having ~ 을 다음에 넣어준다.

 

 

  dual 테이블

단순히 수치, 날짜 계산 등을 수행한다면 dual이라는 특수 테이블을 사용

SELECT 계산_함수식 FROM dual;

 

  표준 내장 함수 

단일행 함수 : 

함수가 한 번에 하나의 행에 적용되어 하나의 결과를 반환. 

숫자 함수	mod(m, n)	m을 n으로 나눴을 때 나머지를 반환
	round(m, n)	m을 소수점 n+1 자리에서 반올림하여 반환
	TRUNC(m, n)	m을 소수점 n 자리에서 (반올림X) 절삭 후 반환
	WIDTH_BUCKET(expr, min(/max), max(/min), step)
	min~ max까지 step 구간에서 expr 가 어느 구간에 속하는지 구간 등급으로 반환
문자 함수	LOWER(char)	입력된 문자열을 모두 소문자로 변환
	UPPER(char)	입력된 문자열을 모두 대문자로 변환
	SUBSTR(str, 시작_자리_수, 추가_자리_수)
	문자열 str에서 ‘시작_자리_수’부터 ~ ‘추가_자리_수”까지의 문자열을 반환. 빈칸도 계산
	REPLACE(str, m, n)	m을 n으로 치환
	LTRIM/RTRIM(expr)	좌/우 측에서 빈칸을 잘라낸다.
	INITCAP(expr)	첫 문자를 대문자로 변환
	length(expr)	전체 문자열의 개수를 반환 (+빈칸도)
	instr(expr, ‘char’)	해당 expr 에서 처음으로 찾은 char 문자 위치를 반환
	lpad(expr, n, ‘#’) (left) rpad(expr, n, ‘$’) (right)	문자열의 길이가 지정된 n보다 적을 때 나머지 자리를 ‘#/$’(지정한 특수문자)로 채운다.
	concat(column, char)	지정한 테이블에서 문자열을 합침 == || 와 유사
날짜 함수	SYSDATE	시스템에 저장된 날짜를 반환 (열_명처럼 사용됨)
	ADD_MONTHS(n)	해당 월에 n 달을 더한 달_수를 반환
	LAST_DAY(date)	해당 달(월)의 마지막 날짜까지의 남은 일 수를 반환
	moths_between(m, n)	m날짜로부터 n 날짜까지의 기간을 월로 반환 (M > n)
변환 함수	TO_CHAR(n, ‘format’)	n의 데이터 타입을 문자열(varchar2) 타입으로 반환
	TO_CHAR(number)	한중일 등의 문자열을 처리해주는 varchar2 타입으로 변환
	TO_NUMBER(char)	문자(열)를 숫자로 바꿔서 반환. 날짜 계산시 유용
기타	CAST(col as 형변환)	형변환에 사용
	convert(char, dst, src)	주어진 문자세트를 다른 문자 세트로 변환 (알지못해도ㄱㅊ)
	TO_DATE()	문자열을 날짜_데이터형으로 변경
	DECODE(expr, 조건, 참_값, 거짓_값)
	‘삼항 연산자’ expr 과 조건을 비교해 참이면 ‘참_값’, 거짓이면 ‘거짓_값’으로 변환
	CASE when ~ then ~ (Big Data 에서 많이 나와)	PL/SQL 프로그래밍 언어로 변환해서 처리. deconde() 함수로는 처리할 수 없는 세밀한 부분도 쉽게 처리하는 장점
	NVL(expr1, value)	None Value Logic 의 준말. expr1=NULL 이면 value 값으로 할당. Big Data 분석에서 데이터 연산 시 NULL 값을 일정한 수로 지정(신중히)하여 처리. for sum, avg

 

집합 함수 : 

하나의 행에 적용해서 하나의 결과를 반환하는 것이 단일행 함수, 

여러 행(하나의 집단)에 적용해서 하나의 결과를 반환해주는 것이 집합함수. 

집합 함수

AVG(열_명)	평균값을 반환
SUM(), MIN(), MAX(), COUNT()	하나 이상의 행으로부터 입력받아 합, 최소값, 최대값, 행의 수를 반환
ROW_NUMBER()	행에서 첫 번째 항목을 반환 (SQL?에서는 limit)
RANK(수식)	순위를 반환. 정렬을 해줘야 순위를 알 수 있기 때문에 over ( ) 에서 ( )안에 주로 order by를 넣어서 over(order by ~), within group(order by ~) 형식으로 사용

 

분석함수 : 

분석함수는 테이블의 행에 대해서 특정 그룹별로 집계 값을 산출할 때 주로 사용된다.

GROUP BY에 의한 최종 쿼리결과는 그룹별로 행이 줄어듦 

집계함수를 사용하면 행의 손실 없이 그룹별로 집계값을 산출.

분석함수는 집계함수에 속한다. 

 

다량의 데이터를 대상으로 하나 이상의 결과를 필요로 하는 누적 합계, 부서별 순위, 구간별 평균 등을 구할 때

분석함수를 사용하면 간결하게 해결. 

 

분석_함수(인자1, 인자2, ....) over(partition by ~ order by ~) window

처리 대상이 되는 행 의 집단(그룹)을 윈도우(window)

 

분석함수는 행의 집단에 대해서 연산이 이뤄진다는 점,

분석함수는 하나의 집단에 대해서 여러 기준을 적용해서 여러 개의 결과. 

 

분석함수는 join, where, group by, having 등이 처리한 결과에 대해서 분석함수를 적용시키고,

마지막에 order by를 적용

분석함수는 select 절이나 order by 절에만 나타나날 수 있다.

 

FIRST_VALUE(수식) OVER(분석_절)는 정렬된 값 중에서 첫 번째 값을 반환한다. 

     ~ OVER(ORDER BY 컬럼_명)을 사용할 경우 메인 쿼리 순서와 상관없이 지정한 컬럼으로 정렬하여 첫 번째 행의 값을 표시한다. 

     ~ OVER(PARTITION BY 컬럼_명1 ORDER BY 컬럼_명2)을 사용할 경우 해당 컬럼을 그룹핑해서 첫 번째 행의 값을 표시한다. 

 

LAST_VALUE() OVER(분석_절) 함수는 정렬된 값 중에서 마지막 값을 반환한다. FIRST_VALUE()와 반대이다. 

 

COUNT(수식) OVER(분석_절)는 조건을 만족하는 행의 수를 반환한다.

 

SUM(수식) OVER(분석_절)을 사용해서 조건을 만족하는 합을 반환한다.  

 

ROW_NUMBER() OVER(분석_절)은 partition by로 분할된 그룹별로 각 행에 대한 순번을 반환하는 함수.

 

RANK() OVER(분석_절), DENSE_RANK() OVER(분석_절)은 partition by로 분할된 그룹별로 순위를 반환한다.

 

 

① REGEXP_LIKE(검색_값, 패턴, .. 매치_옵션) 구문은 

검색_값은 검색할 문자열, 패턴은 검색할 특정 문자, 매치_옵션은 찾고자 하는 문자이다. 

 

② REGEXP_SUBSTR(검색_값, 패턴1, 패턴2, 위치, 특정_문자, 매치_옵션)은 문자열에서 일부를 추출해준다. 위치는 디폴트로 1이므로 처음부터 찾는다. 특정_문자는 찾고자 하는 특정문자이다.

 

③ REGEXP_REPLACE(검색_값, 패턴, 대체_값, 위치, 발생, 매치_옵션) 구문은 특정 패턴과 매치되는 부분을 바꿔준다.