인공지능 부트캠프 24기 _ 3일차 (SQL 함수, 단일항 함수)

※ SQL 함수 ※

 

SQL 에서 함수는 타언어와 동일하게 입력값에 의해 출력값을 반환하는 역할을한다.

역시나, 많은 함수를 언어가 포함하고 있기 때문에 모든 함수를 외우기 보단 reference를 참조하여

함수를 활용하는 스킬을 늘리는게 중요

 

<reference>

 

https://www.oracle.com/technical-resources/

Get the latest product technical information from Oracle

- Documentation – all documentation

– sql 검색 -learn more 계속 해서  SQL Language Reference 21 찾기

좌측에 Fucntion에서 SQL 함수 정보를 확인할 수 있다

SQL함수는 입력값의 발생시 연산 단위에 따라 2가지로 분류 할수 있다.

 

1) 단일행 함수

입력값에  따라 행단위로 연산이 모두 이루어 지며 행 숫자 만큼의 결과값을 출력하는 함수를 말한다.

단일행 함수 요약도

2) 그룹 함수

입력값에 따라 칼럼단위 (자동으로 묶여짐) 혹은 명시적으로 단위를 입력하여 연산 단위만큼의 결과를 뽑는 것을 말한다. (4일차 학습예정)

MAX,MIN,AVG,SUM,COUNT,GROUP BY 등이 이개념에 해당한다.

 

※ 단일행 함수 ※

단일행 함수는 입력값의 종류 혹은 함수의 기능에 따라 아래와 같이 5가지로 구분할 수 있다.

 

1) 문자함수

문자함수는 입력값이 문자를 사용하는 함수를 지칭한다.

반환하는 값으로 치환된 문자열, 특정 문자와 같은 문자열과 또는 문자의 길이나 특정 문자의 위치를 나타내는 숫자의 결과값을 반환하게 된다.

 

문자 관련 함수

함수는 종류가 엄청 많고 입력값의 종류와 갯수가 상이하므로 암기하는 것은 비효율적.

아래와 같이 크게 정리만 하고 필요할 경우 reference를 참조하는것이 도 효율적이다.

 

A. 문자열 반환

출력값으로 문자를 반환하는 문자 함수이며 종류는 다음과 같이 있다. (종류는 자세히 정리하지는 않음)

 

INTICAP, UPPER, LOWER, CONCAT(||와 동일),SUBSTR,REPLACE,LPAD,RPAD,TRIM

 

B. 숫자 반환

 

출력값으로 "숫자"를 반환하는 문자 함수이며 종류는 다음과 같이 있다. (종류가 많지 않으므로 외워준다.)

 

LENGTH, INSTR

 

2) 숫자함수

마찬가지로 숫자가 입력값으로 사용되며 올림, 절삭, 나머지값 등등의 역할을 한다.

숫자 함수

ROUND : 지정한 자리 수 '이하'에서 반올림을 합니다.

TRUNC : 지정한 자리 수 '이하'에서 절삭을 합니다. (간단하게 내림) (자리수를 지정하지 않으면 소수 첫째자림이하 절삭)

MOD : 나머지 연산 (다른 언어의 %와 동일)

CEIL : 주어진 숫자값보다 크거나 같은 최소 정수값(소수점 올림)

FLOOR : CEIL의 반대 (소수점 내림)
SIGN : ABS 기능이구요. 양수일경우 1, 0일경우 0, 음수일경우ㅡ -1을 반환합니다.

 

3) 날짜함수

역시 말그대로 날짜를 입력값으로 변환합니다.

날짜함수는 sql에서 사용할 수 있는 "powerful"한 함수중 하나이다.

연산이 복잡한 날짜데이터를 손쉽게 다룰 수 있도록 하니 잘 숙지해두는게 좋습니다.

 

날짜 데이터의 특징

날짜 데이터는 특정한 포맷을 가지고 있으며 기본적으로 (RR/MM/DD)형식을 가지고 있다.

 

RR과 YY의 차이 : YY는 현재 년도의 세기를 기준으로 년도를 판단하며 RR의 경우 현재 년도가 세기의 전반인 경우 (전세기 xx50~ 현세기 ~49)년으로 반환 후반인 경우 (현세기 50~  후세기 49년)으로 반환한다. 

 

날짜데이터도 간단한 +-연산이 가능한다 이는 Day를 기준으로 연산한다.

날짜 함수 종류

날짜 함수

sysdate : 현재 날짜를 반환한다. (mysql의 경우 now()를 사용) python에서도 now사용한듯?

month_between : 두날짜 사의의 월수를 계산하여 반환. 그러므로 반환값은 날짜데이터가 아닌 "개월수(숫자)" 이다.

add_month : 날짜 연산 함수이며 달의 갯수를 더하여 반환한다. (기본적으로 날짜 연산은 Day 기준이므로 Month기준의 연산이 필요하면 이 함 수를 사용하면 편함)

Next_day : 미래의 "특정 요일"에 대한 "가장 가까운 날짜"를 반환한다.

Last_day : 해당 달의 마지막 날짜를 반환한다. (Next_ day의 반대의미처럼 보이지만 전혀 다른 함수이므로 숙지가 필요)

Round : 숫자와 같이 ROUND는 반올림 역할을 하며 "년도", "월도"의 반올림을 수행하며 날짜를 반환하다. (년도 반올림시 1월 1일, 월 반올림시 1일 기준으로 반환하며 숙지 필요)

TRUNC : 숫자와 같이 년도 월도 기준으로 절삭을 진행. (역시 년도는 1월 1일 월은 1일 기준이다.)

 

4) 변환함수

위 3가지는 특정한 데이터 타입을 다루는 함수라면 변환 함수는 데이터 타입을 서로 변경시켜주는 함수이다.

변환 함수는 데이터가 변환되는 방식에 따라 크게 2가지로 나누어진다

 

1 - 묵시적방법

기본적으로 테이블에 표시된 모든 데이터는 문자데이터이다.

 

그러나 sql내부적으로 함수의 숫자 혹은 날짜데이터를 작성하여도 문자데이터로 자동 변환되어 숫자, 날짜로 작성하여도 error가 발생하지 않음. 

 

즉 변환에 필요한 함수가 필요하지 않은 경우

2 - 명시적 방법

묵시적 방법 외에도 함수를 사용하여 변환이 필요한 경우가 있음. (화폐단위의 쉼표의 경우)

 

그럴떄 아래와 같은 함수를 사용하여 데이터 타입 변환을 수행한다

 

To_char

To_char 함수 문법

To_char의 경우 크게 날짜에서 문자, 숫자에서 문자로 변환하는 2가지 경우가 존재한다.

 

날짜에서 문자로 변환하는 경우 날짜의 특정 부분을 뽑아내는 경우에 사용한다.

To_char 날짜에서 문자 변환 예시

날짜 포맷 활용 예시

시간 포맷 활용 예시

숫자에서 문자로 변환하는 경우 화폐 단위와 같은 것을 추가하여 표시하는 경우에 사용한다.\

To_char 숫자에서 문자 변환 예시

To_char 숫자 to 문자 변환 예시

 

이경우 입력값이 포맷보다 크면 ###### 값이 반환 되므로 포맷이 입력값보다 항상 크게 지정하여야 한다.

 

 

To_number

문자 형식으로 (숫자사이의 ,와 같은)  표현된 데이터를 숫자형식으로 변환하여 "연산" 을 수행하기 위해 사용한다

To_number  기본 문법

To_number 사용 예시

 

To_date

나열된 문자데이터를 원하는 날짜 포맷 형식의 데이터로 변환시켜주기 위해 사용된다.

To_date 함수 기본 문법

To_date 사용 예시

하지만, 위의 예시 결과를 살펴본 경우 원하는 날짜 데이터 포맷의 형식으로 변경하지 않았다.

이는 기본 데이터 포맷의 설정이 표현할 수 없는 데이터이기에 위와 같이 표현되었으므로 원하는 형식의 데이터를 반환 하기위해서는 아래와 같은 문장으로 날짜 포맷을 변경해주어야 한다.

 

날짜 포맷 변경 문장

 

5) 조건함수

조건 함수는조건에 따라 squl문을 다르게 처리하기 위해 사용되어진다.

기본적으로 oracle에서만 사용이 가능한 decode 함수와 ANSI기반으로 작동하는 case문이 존재하며

Decode 함수의 경우 ''='연산자만 사용하며 Case 함수의 경우 동등과 부등연산자를 모두 사용이 가능하다.

 

일반적으로 Case함수의 성능과 효율이 좋으나 오래된 기업의 경우 아직 Decode 함수를 사용한 경우가 많으므로 숙지가 필요하다.

Devod VS Case

 

Decode 함수 활용 예시 (월급이 24000이면 30% 17000이면 20% 그 외는 100%의 보너스를 반환하는 문장)

Decode 함수 활용 예시 (입사년도별 사원 인원수 구하기)

Case  동등 연산자 활용 예시 (위의 Decod의 salary 예제와 똑같은 문저)

Case 부등 연산자 활용 예시

Case 문은 위와 같은 부등연산자도 활용이 가능하지만 IN 혹은 between A and B와 같은 논리 연산자도 모두 활용이 가능하다.