170530 단일 행 함수, 다중 행 함수

단일 행 함수(Single Row Function) : 결과 레코드를 찾아서 출력할 때마다 (각 행에) 함수가 적용된다.

다중 행 함수(Multiple Row Function) : 조건절에 만족하는 모든 행을 다 찾고나서 모든 레코드를 한번에 연산한다.

 

단일 행 함수 분류

- 문자 함수

- 숫자 함수

- 날짜 함수

- 일반 함수

- 형-변환 함수 : SQL문 처리중에 다른 데이터 유형으로 변경필요시 사용

 

※단일행 함수의 중첩 사용

: 제한없이 중첩사용 할 수 있으며, 안쪽부터 바깥쪽으로 함수가 처리됨.

 

※ Dual table

함수에 테스트에 사용되는 테이블

실제 DB에 함수 적용시 처리량이 너무 많은 경우라던가 불편한 경우가 있기 때문에 DUAL을 사용해서 간단하게 테스트 할 수 있다.

항상 하나의 레코드가 반환 된다

 

문자 함수>>

문법	목적
CONCAT('문자열', '문자열')	두 문자열을 붙입니다.
SUBSTR('문자열', 시작위치, [추출할 문자 수]	원하는 문자열 추출
LENGTH('문자열') LENGTHB('문자열')	문자열의 글자 수 문자열의 바이트 수
REPLACE('문자열','바뀔놈', '대체문자')	문자열에서 바뀔놈을 찾아 대체문자로 바꿈
TRIM('문자' FROM '문자열')	문자열의 양끝의 연속된 문자 삭제
LPAD('문자열', 'Byte 길이','빈칸을 채울 문자')   RPAD('문자열', 'Byte 길이','빈칸을 채울 문자')	Byte길이만큼 문자열 출력하고 LPAD는 왼쪽 RPAD는 오른쪽에 빈칸채울문자 출력
INSTR('문자열', '찾을문자', '몇번째문자부터', '존재횟수')	문자열중에  몇번째 문자부터 몇번째등장하는 문자의 위치인지 검증할때 사용

 

 

숫자 함수 >>

문법	목적
MOD(숫자1, 숫자2)	나누고 남은 나머지 반환
ROUND(숫자, [소수점이하 유효자리 수] )	유효자리 수의 다음 자리에서 반올림
TRUNC(숫자, [소수점이하 유효자리 수] )	유효자리 수의 다음 자리에서 버림

 

 

날짜 함수 >>

문법	목적
SYSDATE	오라클-서버가 운영되는 운영체제의 날짜와 시간    ※ 해당 운영체제의 환경변수인 시간 포맷에 맞게 출력되기 때문에 날짜상수의 포맷을 일치 시켜주어야 한다. ex ) 한국 (yy/mm/dd)
★★★ TO_DATE('날짜', '날짜 포맷'  )	표시형식이 달라도 에러없이 원하는 값을 형변환해서 출력해줌. 날짜 상수를 직접 기술하는 작업이 필요할 때는 이 함수를 사용하는것이 좋다.
MONTHS_BETWEEN('날짜1', '날짜2')	두 날짜 사이의 달 수를 정확히 알려줍니다.
NEXT_DAY('날짜', '요일')	날짜를 기준으로 다음에 오는 요일의 날짜를 반환함.   오늘 날짜를 기준으로 다음주 월요일의 날짜를 반환해 주는것을 확인.
ADD_MONTHS('날짜', '숫자')	숫자를 날짜의 달 수 에 더한 날짜를 반환한다.
LAST_DAY('날짜')	날짜가 포함된 달의 맨 마지막 날짜 반환

 

 

※ 날짜의 형식을 바꿔서 SYSDATE 출력하기

형식	의미	형식	의미
YYYY	4자리 년도 (1990)	HH24	24시간 표기
MM	숫자로 된 월	HH	12시간 표기
DD	숫자로 된 일	MI	숫자로 된 분
DAY	요일(영어 : FRIDAY, 한글 : 금요일)	SS	숫자로 된 초
*  RR 쓰지마셈 버그있음 YYYY 짱짱맨		AM	오전/오후

 

 << 포맷을 변경 한 후의 SYSDATE 결과값

 

본래 SYSDATE를 사용할 경우 RR/MM/DD 의 형식으로 출력이된다.

ALTER SESSION SET NLS_DATE_FORMAT으로 포맷형식을 변경하고 SYSDATE를 실행하면 변경한 형식으로 날짜가 출력되는걸 확인 할 수 있다.

이 설정은 세션이 돌아가는동안 실행되므로 세션이 종료되면 원래의 포맷형식으로 바뀐다.

 

 

 

 

숫자에 대한 포맷 >>

형식	의미	사용 예	결과
$	숫자 앞에 $ 표시	$9,999,999	$1,250
L	해당 지역의 통화단위	L9,999,999	\1,250 (원화), $1,250(달러)
0	사용하지않은 자리수 까지 출력	09,999,999	0,001,250

 

 

형-변환 함수 >>

문법	목적
TO_DATE	위에있음
TO_CHAR('날짜 또는 숫자 표현식' ,'날짜 또는 숫자형식 모델' )	사용자가 원하는 형식 모델을 모두 사용할 수 있다. AM과 HH24 형식모델을 동시의 사용하는등 사용자가 원하는대로 사용가능

 

★★★★일반 함수 >>

문법	목적
NVL(컬럼명 , 대체값)	Null 값이 있을 경우 대체값으로 변경시켜줌
NVL2 (컬럼명, 대체값1, 대체값2)	값이 있으면 = 대체값 1  null 일경우 = 대체값 2
DECODE(컬럼, 값1, 표현식1, 값2 ,표현식2   ...   값n, 표현식n       해당없을때 표현값. )	컬럼에서 받아오는 값에 따라서 표현식을 다르게 한다.   표현식끼리는 같은 데이터 타입이 와야한다.
(CASE 컬럼명 WHEN 값1 THEN 표현식1       ...                        WHEN 값n THEN 표현식n  ELSE salary  END)	case 시작  when 값이 왓을때  then 표현식을 적용  else 해당 사항없을때 end 끝

    

 

---

 

다중 행 함수 (= 그룹함수) >>

 

그룹함수의 종류

- SUM() 

- AVG() 

- MAX()

- MIN()

- COUNT()

 

그룹함수 이름	기능	처리 데이터
SUM()	합계	숫자 데이터
AVG()	평균
MAX()	최대값	숫자 , 문자, 날짜 데이터
MIN()	최소값
COUNT()	개수

 

 

고려사항

 

1. ALL / DISTINCT : ALL은 요소 모든 값을 연산하고 DISTINCT는 동일한 값을 한번만 연산함. 디폴트 값은 ALL 방식.

[1,2,3,4,4] 가있을경우 ALL 을 적용한 결과값은 '14' 이며 DISTINCT을 적용한 결과값은 '10'이다.

 

2. NVL : 평균등을 연산하는 경우 null의 값을 포함 시키느냐 마느냐의 차이가 결과값에 영향을 줄 수 있다. 원래대로라면 null값을 가지고 있는 요소는 평균계산에 포함시키지 않는다. AVG와 COUNT에  NVL을 사용하면 평균연산에 null을 가지고있는 원소를 포함해 계산되는걸 볼수 있으며 ,

COUNT(*) = COUNT(NVL(table, 0)) 과 같다.

그룹함수와 함께 사용하는 SELECT문의 키워드가 있는데. GROUP BY와 HAVING이다.

이 둘은 WHERE와 ORDER BY 사이에 위치한다.

 

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

 

 

GROUP BY >>

: 데이터들을 원하는 그룹으로 나눌 수 있다.

 

1. 나누고자 하는 그룹의 컬럼명을 SELECT와 GROUP BY에 추가하면된다.

2. 뽑아내는 데이터가 같을경우에 그룹화시켜서 결과레코드를 출력해준다.

(Group by 에서 null값의 처리는 null값 끼리 모아서그룹을 만들어준다.)

 

SELECT 절에서 명시된 컬럼들은 모두 GROUP BY에 명시해 주어야 한다.

ex )

Select a,b,c avg(d)

from table1

group by a,b,c

 

Where절에서는 그룹함수를 사용 할 수 없다.

: 그룹함수는 작업처리 순서상 WHERE 보다 나중에 처리되는 작업이기 때문에 where절이 처리될때 함께 쓸 수 없다.

 

 

HAVING >>

 : 그룹함수에 조건을 걸어서 최종 결과를 선별 할 수 있다. select로 만족하는 행을 선택하고, 선택된 행들중 group by에따라 같은 그룹으로 나눈후 , 그룹의 행들을 대상으로 having 절의 그룹함수 조건을 검사해서 원하는 레코드를 구성합니다.

 

결과값중 SUM(salary)가 7000보다 높은 값만 가져왓다. 결과에 조건을 붙이는 역활을 하는것을 확인했다.

 

※그룹함수의 중첩

 : 그룹함수는 2번까지만 중첩을 할 수 있다. 제한된 사항은 아니고 그룹함수를 두번 실행하면 하나의 결과가 출력되기 때문에 더이상의 중첩이 의미가 없기 때문이다. 그룹함수에 단일행 함수를 연산하는것은 상관없이 연산이 가능하다.