[Basic] SQL 기초 함수, 데이터 타입

1 SQL 함수 개념

• 칼럼의 값, 데이터 타입, 출력 형식 변경 + 하나 이상의 행에 대한 집계 등의 기능
• 단일 행 함수: 개별 행 대상으로 적용 => 하나의 결과 반환
• 복수 행 함수: 여러 행 그룹화해서 적용 => 그룹별로 결과 하나씩 반환

2 문자 함수

• INITCAP('student'): 첫 번째 영문자를 대문자로 => Student
• LOWER('STUDENT'): 소문자로 => student
• UPPER('student'): 대문자로 => STUDENT
• LENGTH('홍길동'): 문자열 길이 반환 => 3
• LENGTHB('홍길동'): 문자열 바이트 수 반환 => 6
• CONCAT('sql', 'plus'): 문자열 결합 => sqlplus
  SELECT CONCAT(CONCAT(name, '의 직책은'), position) FROM professor; => 김도훈의 직책은 부교수
• SUBSTR('SQL*Plus', 5, 4): 문자열 일부 추출. 5번째 글자부터, 4개의 글자(8까지).
  여기서 5가 음수면 뒤부터 시작한다(-3, 2)면 lu, (-3, 4)면 lus,
  4가 생략되면 마지막까지 추출
• INSTR('CORPORATE FLOOR', 'OR', 3, 2) : 3부터 시작해서, 2번째로 만나는, 지정 글자의 위치를 반색. 시작이 1. 3이 음수면 뒤부터. => 14
• LPAD('sql', 5, '-'): 오른쪽 정렬 후 좌측에 해당 글자 채워넣기 => --sql
• RPAD('sql', 5, '-'): 왼쪽 정렬 후 우측에 해당 글자 채워넣기=> sql--
• LTRIM('xyxxsqlxx', 'xy'): 지정된 문자를 개별로 보고, 거기 포함된 것들을 왼쪽에서 모두 삭제 => sqlxx
  LTRIM('xyxxsqlxx', 'xylsq')가 되면 NULL
• RTRIM('xyxxsqlxx', 'xy'): 지정된 문자를 개별로 보고, 거기 포함된 것들을 오른쪽에서 모두 삭제 => xyxxsql
  
  

3 숫자 함수

• ROUND(123.17, 1): 지정된 소수점 자리수까지 반올림. 1대신 -1이면 십의 자리로 자름. 1 123.0이 됨(정수 아니고 실수) => 123.2
• TRUNC(123.17, 1): 지정된 소수점 자리로 값을 버림 => 123.1 TRUNCATE() athena의 경우
• MOD(12, 10): 12를 10으로 나눈 나머지 => 2
• CEIL(123.17): 지정 값보다 큰 수 중 가장 작은 정수 => 124
• FLOOR(123.17): 지정 값보다 작은 수 중 가장 큰 정수 => 123

4 날짜 함수

• 날짜 계산은 더하기, 빼기 연산 가능.
  • 날짜에 숫자를 더하거나 빼면, 일수를 더하거나 빼서 => 날짜 반환
  • 날짜에 날짜를 빼면 => 일수 반환
  • 날짜에 숫자/24를 더하면 => 시간 더해서 => 날짜 반환
• SYSDATE: 시스템의 현재 날짜 => 날짜 반환
• MONTHS_BETWEEN(날짜, 날짜): 날짜와 날짜 사이의 개월을 계산 => 숫자 반환
• ADD_MONTHS(날짜, 숫자): 날짜에 개월수를 더함 => 날짜 반환
• NEXT_DAY(날짜, '표기'): 날짜 이후 첫 번째 '표기'요일을 반환. '일', 1 은 일요일이다. => 날짜 반환
• LAST_DAY(날짜): 그 달의 마지막 날짜를 반환 => 날짜 반환
• ROUND(날짜): 반올림(정오 넘으면 다음날 출력)
• TRUNC(날짜): 절삭(시간 정보 무시하고 당일 출력)
• EXTRACT: date 타입에서 연, 월, 일, 시간 등을 추출하는 함수
  비슷한 역할로는
  DATE YEAR MONTH DAY HOUR MINUTE SECOND 등이 있고 모두 괄호 안에 date 타입 데이터가 있으면 거기서 특정 요소를 추출한다. DATE는 날짜 시간 값에서 날짜(2021-01-01)까지만 추출 => 안되는 환경들 있다. 지금 하는 아테나에서는 안 써짐.
• date '2021-01-01'처럼 문자열 앞에 date라고 치면 date형식으로 인식하는 기능 athena엔 있음
• DATE_ADD('day', 숫자, 칼럼): 더하기. 여기서 -를 넣으면 뺄 수 있다.
• DATE_DIFF('day', 첫 칼럼, 둘째 칼럼): 두 날짜 일 수 차이.
• DATE_FORMAT(칼럼, 'yyyy-MM-dd'): 날짜를 원하는 형식으로 포매팅.
• DATE_TRUNC('day', 칼럼): 날짜를 특정 단위로 자르기.
• 그냥 date + interval '10' day 이런 식으로도 가능.

5 데이터 타입 변환

• 숫자나 날짜를 문자와 함께 결합하기 위해 사용.
• 묵시적 변환(명시 안했을 때 오라클 내부에서 자동 변환).
  WHERE A = B 했는데 데이터 타입이 다를 때.
  • NUMBER + VARCHAR2(CHAR)(숫자로만 구성된 문자열) => 글자가 NUMBER로 변환된다.
  • 인덱스가 생성되어 있던 게 묵시적 형변환으로 인덱스 사용 불가능해져서 처리 속도 느려질 수 있음
• 아래의 함수들은 모두 명시적으로 변환하는 것
• TO_CHAR('06/10', 'YYYY-MM'): 숫자 날짜를 문자로 반환 => '2006-10'
• TO_NUMBER(1000): 문자열을 숫자로 => '2006-10'
• TO_DATE('06/10', 'YYYY-MM'): 문자열을 날짜로 반환 => '2006-10'
• Athena에서는
  • timestamp 방식을 date로 바꾸는 건 date(timestamp)로 가능. 비슷한 방식으로 time()도 가능할 것으로 보임.
  • 하지만 varchar을 바꾸는 건 불가능. => DATE_PARSE('2021-01-01', '%Y-%m-%d %H:%i:%s') 이렇게 가능.
  • 마찬가지로 아테나에서 date '2021-01-01'은 문자열 '2021-01-01'이 date라는 걸 나타낸다.
• 아테나 환경에서 가장 편한 건 Cast(컬럼 as 바꾸려는 데이터 타입)
  • CHAR(n): 고정 길이 문자열로, 최대 길이를 n으로 지정합니다.
  • VARCHAR(n): 가변 길이 문자열로, 최대 길이를 n으로 지정합니다.
  • INTEGER 또는 INT(아테나 환경): 정수 데이터 타입입니다. 여기서 사사오입 자동적으로 해준다.
  • SMALLINT: 작은 정수 데이터 타입입니다.
  • BIGINT: 큰 정수 데이터 타입입니다.
  • DECIMAL(p, s): 고정 소수점 숫자로, 전체 자릿수를 p로, 소수점 이하 자릿수를 s로 지정합니다.
  • NUMERIC(p, s): DECIMAL과 동일한 고정 소수점 숫자 데이터 타입입니다.
  • FLOAT 또는 REAL(아테나 환경): 부동 소수점 숫자 데이터 타입입니다.7자리 정빌도로 32비트.
  • DOUBLE: 더블 정밀도 부동 소수점 숫자 데이터 타입입니다.두 배 정밀도로 64비트. 더 큰 숫자나, 더 높은 정밀도.
  • DATE: 날짜 데이터 타입입니다.
  • TIME: 시간 데이터 타입입니다.
  • TIMESTAMP: 날짜 및 시간 데이터 타입인데, 밀리초까지 확장.
  • BOOLEAN: 불리언(참/거짓) 데이터 타입입니다.

5-1 출력 형식 종류

• 날짜 출력 형식 종류

  • SCC(또는 CC): 세기 TO_CHAR(sysdate,'CC') => 21 반환
  • YYYY(또는 SYYYY, YYY, YY, Y, Y,YYY): 년 TO_CHAR(sysdate,'YYYY') => 2006(006, 06, 6, 2,006)
  • YEAR: 년을 글자로. TO_CHAR(sysdate,'YEAR') => TWENTY TWENTY-FOUR
  • BC(B.C.), AD(A.D.): 서기인지 기원전인지 출력 TO_CHAR(sysdate,'BC')=> 서기
  • Q: 분기 TO_CHAR(sysdate,'Q')=> 1
  • MM: 월을 숫자로 TO_CHAR(sysdate,'MM')=> 01
  • MONTH(또는 MON): 월을 글자로TO_CHAR(sysdate,'MONTH')=> 1월
  • RM: 월을 로마자로: => I
  • WW: 연을 주단위로 표현 TO_CHAR(sysdate,'WW') => 04 (1월 4째주)
  • W: 월을 주단위로 표현 TO_CHAR(sysdate,'W') => 4 (1월의 4째주)
  • DDD: 연중 일로 표현 TO_CHAR(sysdate,'DDD') => 025
  • DD: 월중 일로 표현 TO_CHAR(sysdate,'DD') => 25
  • D: 주중 일로 표현 TO_CHAR(sysdate,'D') => 5 (목요일)
  • DY: 요일 약어 TO_CHAR(sysdate,'DY') => 목
  • DAY: 요일 TO_CHAR(sysdate,'DAY') => 목요일

• 시간 출력 형식 종류

  • AM(또는 A.M) PM(P.M): 오전, 오후 표시 TO_CHAR(sysdate,'PM') => 오전
  • HH(또는 HH12): 시각(1~12) TO_CHAR(sysdate,'HH') => 11
  • HH24: 시각(0~23) TO_CHAR(sysdate,'HH24') => 11
  • MI: 분 TO_CHAR(sysdate,'MI') => 31
  • SS: 초 TO_CHAR(sysdate,'SS') => 03

• 기타 날짜 표현

  • "글자": 중간에 글자를 그냥 표현

  TO_CHAR(sysdate, 'Mon "the" DDTH "of" YYYY') => 1월 the 25th of 2024

  • TH: 서수로 표시 (DDTH의 경우 25 => 25th)
  • SP: 숫자(기수)를 영문으로 표시(DDSP의 경우 25 => TWENTY-FIVE)
  • SPTH(또는 THSP): 서수를 영문으로 표시 (DDSPTH 25 => TWENTY-FIFTH)

• 숫자 출력 형식 종류

  • 9: 한자리 숫자 표시.
    • TO_CHAR(1234, '999999')
    • 이것보다 긴 숫자 => #####로 표시
    • 이것보보다 짧은 숫자 => 왼쪽에 공백 그만큼 채우고 표시
  • 0: 앞부분을 0으로 표시 TO_CHAR(1234, '099999') => 001234
  • $: 달러 기호 앞에 표시 TO_CHAR(1234, '$999999') => $1234
  • .: 소수점 위치 표시 TO_CHAR(1234, '9999.99') => 1234.00
  • ,: , 위치 표시TO_CHAR(1234, '999,999') => 1,234
  • FM: 음수 표시 TO_CHAR(-1234, 'FM999999') => -1234
  • S: 양수, 음수 모두 표시 TO_CHAR(1234, 'FM999999') => +1234
  • EEEE: 과학적 표기로 표시 TO_CHAR(1234, '9.9999EEEE') => 1.2340E+03 이렇게 표시 .위치 따라서 또 달라짐

6 일반 함수

• NVL(칼럼 또는 표현식, 0): NULL을 다른 대체 값으로 변경. 여기서는 0으로 대체. 0이 아니라, 다른 값도 가능.
  • 주의점은 앞과 뒤가 같은 데이터 타입이어야
• NVL2(칼럼 또는 표현식, 0, 1): NULL이면 0으로, NULL이 아니면 1로 대체.
• NULLIF(표현식, 표현식): 두 표현식이 일치하면 NULL 반환, 아니면 첫 번째 표현식 반환
• COALESCHE(표현식, 표현식, ...): 인수 중 NULL이 아닌 첫 번째 인수를 반환
• DECODE: 복잡한 구문을 하나로 표현하는 것으로, '=' 비교 연산만 가능하다. 실예를 보는 게 빠르다.

SELECT name, deptno
	DECODE(deptno, 101, '컴퓨터 공학과', 102, '멀티미디어학과', 201, '전자공학과', '기계공학과) dname)
FROM professor;

첫 번째 인수는 표현식 또는 칼럼이다(deptno)
이후는 search, result가 반복되는 개념. 101이면 '컴퓨터공학과'로 바꾸고, 102면 멀티미디어학과로 바꾸고.
마지막 값은 어디에도 해당 안되는 값, NULL 값을 이걸로 바꾼다는 default값. default값이 없으면 NULL 반환한다.

• CASE: DECODE를 확장해서, '=' 외에도 산술연산, 관계연산, 논리연산 등 다양한 비교 가능. WHEN절로 다양한 표현식 가능.

SELECT name, deptno, sal, 
	CASE WHEN deptno = 101 THEN sal*0.1
    	WHEN deptno = 102 THEN sal*0.2
        WHEN deptno = 201 THEN sal*0.3
        ELSE 0
       END bonus
FROM professor;

여기서 보면 CASE WHEN하고 조건을 언급하고, THEN하고 그 조건에 맞을 경우 표현식
어디에도 해당 안되면 ELSE
마지막에 END
bonus는 앞에 AS가 생략된 것으로 속성 별명

7 편한 함수

• SELECT *
  FROM table_name
  LIMIT 10;

: 가장 위의 10개 행만 보여준다. 가장 마지막에 실행된다. from, where, group, select, order by, limit