[STARTERS 4기 TIL] #10일차(23.02.17)

 

📚 오늘 내가 배운 것

📍 자주 쓰이는 단일 행 함수(1) 

• 문자 함수

LOWER (char)	char의 모든 문자를 소문자로 변경하여 리턴
UPPER (char)	char의 모든 문자를 대문자로 변경하여 리턴
CONCAT (char1, char2)	char1의 문자열과 char2의 문자열을 연결하여 리턴

SELECT CONCAT ('ABC', 'def') AS CONCAT_FN
  FROM dual;

 

SUBSTR (char, position [, length])	char 문자열의 position 위치로부터 length개 문자를 잘라내서 리턴
REPLACE (char, search_string [, replace_string])	char 문자열에서 search_string 값을 replace_string 값으로 대체하여 리턴

SELECT SUBSTR ('ABCDEFGH', 4, 2)          AS SUBSTR_FN
     , REPLACE ('ABCDEFGH', 'DEF', 'ZZZ') AS REPLACE_FN1
     , REPLACE ('ABCDEFGH', 'DEF')        AS REPLACE_FN2
  FROM dual;

[결과]

SUBSTR_FN	REPLACE_FN1	REPLACE_FN2
DE	ABCZZZGH	ABCGH

 

LPAD (expr1, n [, expr2])	expr1을 n자리만큼 늘리고, 왼쪽 빈 공간을 expr2로 채워서 리턴
RPAD (expr1, n [, expr2])	expr1을 n자리만큼 늘리고, 오른쪽 빈 공간을 expr2로 채워서 리턴

SELECT LPAD ('ABC', 8, 'Z')          AS LPAD_FN1
     , LPAD ('ABC', 8)               AS LPAD_FN2
     , RPAD ('ABC', 8, '12')          AS RPAD_FN1
  FROM dual;

[결과]

LPAD_FN1	LPAD_FN2	RPAD_FN1
ZZZZZABC	ABC	ABC12121

 

LTRIM (char [, set])	char 문자열의 왼쪽부터 set 문자를 제거하여 리턴 (set 기본 값은 공백)
RTRIM (char [, set])	char 문자열의 오른쪽부터 set 문자를 제거하여 리턴 (set 기본 값은 공백)
TRIM ([LEADING	TRAILING

 

LENGTH (char)	char 문자열의 길이를 리턴
INSTR (char, search_string [, position [, occurrence]])	char 문자열의 position 위치로부터 occurrence 번째로 찾은 search_string의 시작 위치를 리턴

SELECT INSTR ('ABCABCAB', 'AB', 3, 2)     AS LENGTH_FN1
	   , INSTR ('ABCABCAB', 'AB', 3)        AS LENGTH_FN2
		 , INSTR ('ABCABCAB', 'AB')           AS LENGTH_FN3
		 , INSTR ('ABCABCAB', 'AB', 3, 3)     AS LENGTH_FN4
  FROM dual;

[결과]

LENGTH_FN1	LENGTH_FN2	LENGTH_FN3	LENGTH_FN4
7	4	1	0

• 숫자 함수

ABS (n)	숫자 n의 절대 값을 리턴
SIGN (n)	숫자 n의 부호를 리턴 (n이 양수면 1, 음수면 -1, 0이면 0 을 리턴)

 

ROUND (n1 [, n2])	숫자 n1을 소수점 n2 째 자리로 ‘반올림’하여 리턴 (n2가 0이거나 생략되면 정수)
TRUNC (n1 [, n2])	숫자 n1을 소수점 n2 째 자리로 ‘버림’하여 리턴 (n2가 0이거나 생략되면 정수)

 

ROUND (n1 [, n2])	숫자 n보다 크거나 같은 최소의 정수 값을 리턴
FLOOR (n)	숫자 n보다 작거나 같은 최대의 정수 값을 리턴

 

MOD (n1, n2)	숫자 n1을 n2로 나눈 나머지 값을 리턴
POWER (n1, n2)	숫자 n1의 n2 제곱 값을 리턴
SQRT (n)	숫자 n의 제곱근 값을 리턴

• 날짜 함수

SYSDATE	데이터베이스 서버의 현재 날짜 값을 리턴
ROUND (date [, fmt])	날짜 date를 fat 포맷 요소 기준으로 ‘반올림’하여 리턴 (fmt: YY, MM, DD, HH, MI 등)
TRUNC (date [, fmt])	날짜 date를 fmt 포맷 요소 기준으로 ‘버림’하여 리턴

 

NEXT_DAY (date, n)	날짜 date 이후 가장 가까운 n 요일 날짜 값을 리턴 1: 일, 2: 월, 3: 화, 4: 수, 5: 목, 6: 금, 7: 토
ROUND (date [, fmt])	날짜 date가 속한 월의 마지막 날짜 값을 리턴

 

ADD_MONTHS (date, n)	날짜 date에 n 개월 수를 더한 날짜 값을 리턴
MONTHS_BETWEEN (date1, date2)	두 날짜 (date1, date2) 간의 개월 수를 리턴

 

 

📍 GROUP BY 절 

SELECT 칼럼, 칼럼, ...
  FROM 테이블 a, 테이블 b, ...
 WHERE 조건 
GROUP BY 칼럼, 칼럼;

SELECT a.job, SUM(a.sal), MAX(a.sal), MIN(a.sal)
  FROM emp a
GROUP BY a.job;

• SELECT 문에 GROUP BY 절이 포함되어 있을 때,
• **기준 칼럼 외 다른 칼럼**을 SELECT 절에 포함시키면 오류 발생 !

SELECT a.job, a.sal
  FROM emp a
GROUP BY a.job;

• 집계 함수

      - 집계 함수는 다중 행(행 그룹)의 값을 입력 받아 단일 행의 값을 리턴하는 함수

      - COUNT, SUM, AVG, MAX, MIN

• 집계함수를 사용하면, 행 그룹 별로 NULL을 제외한 값이 집계됨
• 집계함수의 인자에 연산이나 가공이 존재하면, 이는 집계함수 실행 전에 행 그룹 내에 있는 개별 행 단위로 수행됨

SELECT a.deptno
	   , COUNT(NVL(a.comm, 0) AS CNT
		 , SUM(NVL(a.comm, 0) AS SUM
		 , AVG(NVL(a.comm, 0) AS AVG
  FROM emp a
GROUP BY a.deptno;

 

• GROUP BY 절 없이 SELECT 절에 집계 함수를 사용하면, 전체 행이 하나의 그룹 행이 된다.

 

 

📍 집계함수 

COUNT(*)	행 그룹에 속한 전체 행의 개수를 리턴
COUNT(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 개수를 리턴

 

SUM(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 합계 값 리턴
AVG(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 평균 값 리턴

SELECT a.deptno
		 , SUM(a.comm)
		 , AVG(a.comm)
		 , NVL(AVG(a.comm),0)     //평균 값을 내고 NULL 값을 0으로 치환
		 , AVG(NVL(a.comm, 0))    // NULL 값을 0으로 치환한 뒤 평균 값 내기
FROM emp a
GROUP BY a.deptno;

 

MAX(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 최대 값 리턴
MIN(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 최소 값 리턴

 

VARIANCE(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 분산 값 리턴
STDDEV(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 표준편차 값 리턴

 

STATS_MODE(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 최빈 값 리턴
MEDIAN(expr)	행 그룹에 속한 NULL이 아닌 expr(표현식)의 중앙 값 리턴

 

LISTAGG(exp, ‘delimiter’) WITHIN GROUP (order_by_daue)

행 그룹에 속한 expr(표현식)을 delimiter 문자로 구분하여 연결한 값을 리턴

SELECT a.deptno
		 , LISTAGG(a.ename, ',') WITHIN GROUP (ORDER BY a.sal DESC)
		 , LISTAGG(a.job, '/') WITHIN GROUP (ORDER BY a.job)
  FROM emp a
GROUP BY a.deptno;

• CASE 표현식과 집계 함수를 사용하면 조건에 만족하는 건만 집계(부분 집계)할 수 있다.

SELECT SUM(CASE WHEN a.deptno = 10
								THEN a.sal
					  END) AS SUMSAL_DEPT10
  FROM emp a;

 

 

📍 HAVING 절

• 조회되는 행 그룹을 제한할 수 있다.

SELECT 칼럼, 칼럼, ...
  FROM 테이블
 WHERE 조건
GROUP BY 칼럼, 칼럼, ...
HAVING **조건** ;

SELECT a.deptno, a.job, COUNT(*)
  FROM emp a
GROUP BY a.deptno, a.job
	HAVING COUNT(*) >= 2;

• HAVING 절에 집계 함수 없이 조건을 기술하면 에러가 발생 !
  • Grouping 칼럼에 대한 조건은 집계 함수가 없더라도 에러가 발생하진 않음
  • 하지만, 성능 상 WHERE 절에 기술하는 것이 바람직함
  • 집계 값에 대한 조건은 HAVING 절에만 기술 가능 (WHERE 절 불가 !)
• WHERE 절은 행 그룹이 생성되기 전에 조건에 따라 행을 제한하고,
• HAVING 절은 행 그룹이 생성된 후에 조건에 따라 행 그룹을 제한함.

 

• HAVING 절을 사용하여 중복 값 존재 여부를 확인(데이터 검증)할 수 있다.

SELECT a.sal
		 , COUNT(*)
  FROM emp a
GROUP BY a.sal
	HAVING COUNT(*) > 1;

---

💭 오늘 기억해야 할 내용

① 다양한 단일행 함수

 단일행 함수를 모두 외울 필요는 없지만, 기본적인 것들은 구글링 없이도 사용할 수 있도록 숙지하고 있어야겠다 !

 

② groupby절과 having절

예제를 풀다보면 having절 대신 where절을 사용해 틀리는 경우가 있다.

그러니 having절의 사용상황에 대해 유념해야 한다 !!

 

 

 

✍🏻 회고

오늘은 SQL 마지막 시간이었다!

SQL은 강의를 듣을 때 어려움이 유독 많았다.

배웠던 함수들은 예제를 풀면서 계속 복습해보고,

다음 오프라인 SQL 강의에서 부족한 부분을 보강해야겠다 !!

 

내일은 R 강의가 시작된다 !

내일도 화이팅 !!!🤩