[STUDY] 240311 | DB | 함수(다중행 함수), Group by절을 사용한 데이터 조회

• EMP, DEPT 테이블을 활용한 DB 예제 풀이

EMP TABLE(사원)의 데이터

DEPT TABLE(부서)의 데이터

ㅤ

💻 함수(Function)

1. 내장 함수(Buit-in Function)

• 단일행 함수(Single-Row Function)
  • 문자형 함수, 숫자형 함수, 날짜형 함수, 변환형 함수, NULL 관련 함수
• 다중행 함수(Multi-Row Function)
  • 집계함수, 그룹함수, 윈도우함수

2. 사용자 정의 함수(User Defined Function)

• 사용자 정의 함수(UDF)는 SQL 언어의 기존 내장 함수에 대한 확장 또는 추가 기능

ㅤ

📌 단일행 함수

• 함수의 입력 행수가 1개
• 1개의 결과값을 도출
• 각 행에 대한 조작 결과를 리턴함
• EX) 5개를 입력하면 5개의 데이터가 출력
• SELECT, WHERE. ORDER BY, UPDATE의 SET절에 사용 가능
• lower, upper, substr, length, trim, replace

📌 다중행 함수

• 데이터의 개수와 상관없이 조회 결과가 1행 나오는 함수
• 함수의 입력 행수가 여러개
• 여러 행을 바탕으로 1개의 결과값을 도출
• EX) 5개를 입력하면 1개의 데이터가 출력
• 여러행이 결과로 나오는 열은 사용 불가
• 사번(14개), 사원명(14개), 모든 직원의 급여의 합(1개) 조회 시
  ➡️ 행 개수가 서로 다르면 조회되지 않음
• sum, count, max, min, avg

💡 집계함수

• 집계 함수는 테이블의 각 열에 대한 계산이 필요할 때 사용됨.
• 특정 컬럼을 기준으로 집계 함수를 사용하여 건수(COUNT), 합계(SUM), 평균(AVG) 등 집 계성 데이터 추출

COUNT

• 행의 개수를 반환
• 테이블 전체 행의 개수를 알고 싶을 때 사용하기도 하고, 집합의 개수, 조건절의 개수 등 원하는 행의 개수를 확인할 수 있음.

SUM

• 컬럼 값의 합계를 반환
• EX) 월급의 합계, 매출의 합계 등

AVG

• 행의 개수를 반환
• 테이블 전체 행의 개수를 알고 싶을 때 사용하기도 함
• EX) 월급의 평균, 수량의 평균 등

MAX

• 컬럼의 최대값을 반환
• 페이징 기법에서 사용되기도 함
• 마지막 값에서 +1을 하면 자동으로 값을 증가시킬 수 있음

MIN

• 컬럼의 최소값을 반환

ㅤ

💻 GROUP BY - 그룹별 조회

• GROUP BY 그룹화할 컬럼명
• GROUP BY 절은 특정 컬럼을 기준으로 집계 함수를 사용하여 건수(COUNT), 합계(SUM), 평균(AVG) 등 집 계성 데이터를 추출할 때 사용함.
• GROUP BY 절에서 기준 컬럼을 여러 개 지정할 수 있으며, HAVING 절을 함께 사용하면 집계 함수를 사용하여 WHERE 절의 조건절처럼 조건을 부여할 수 있음.
• GROUP BY 절은 중복 제거를 할 때도 사용 가능함.
• SQL Server에서는 GROUP BY 절을 사용할 경우 그룹 컬럼을 기준으로 자동으로 ORDER BY가 되지만 명시적으로 ORDER BY를 사용하여 쿼리문을 작성하는 것이 좋음.
  ㅤ

📌 계성 데이터 추출

• 특정 컬럼을 기준으로 집계 함수를 사용하여 건수(COUNT), 합계(SUM), 평균(AVG) 등 집 계성 데이터 추출

-- 각 직급별 급여의 합
SELECT JOB 
		, SUM(SAL)
FROM emp
GROUP BY JOB;

-- 부서 번호별 인원수 조회
-- COUNT 조회 시 중복값/NULL값이 없는 PRIMARY KEY를 사용!!
SELECT DEPTNO
		, COUNT(EMPNO)
FROM emp
GROUP BY DEPTNO;

-- 직급별로 그룹지어서 => GROUP BY
-- 직급별 급여의 합과 급여의 평균, 커미션의 평균을 => SUM(), AVG()
-- 조회하는 쿼리문을 작성하세요.
-- 단 조회는 직급 기준 오름차순으로 정렬하여 나타내세요.  
-- *** 만약 커미션의 평균이 NULL이라면 0.0으로 조회
SELECT JOB
	, SUM(SAL)
	, AVG(SAL)
	, IFNULL(AVG(COMM), 0.0)
FROM EMP
GROUP BY JOB
ORDER BY JOB ASC;

-- 입사한 월별 사원들의 급여의 합
SELECT DATE_FORMAT(HIREDATE, '%m') 입사월
		, SUM(SAL)
FROM emp
GROUP BY DATE_FORMAT(HIREDATE, '%m');

📌 HAVING절 사용 - 조건 부여

• 그룹에 조건을 줄 때 사용함.
• HAVING절은 반드시 GROUP BY와 함께 사용되며, GROUP BY에 조건을 줄 때는 WHERE이 아닌 HAVING만 사용 가능함.

-- 1월에 입사한 사원들을 제외하고, 월별 입사자 수를 조회
-- 해석 순서 : 1 (FROM)
-- 해석 순서 : 2 (WHERE) **별칭 사용 불가능(SELECT보다 먼저 조회되기 때문)
-- 해석 순서 : 3 (SELECT)
-- 해석 순서 : 4 (GROUP BY) *별칭 사용 가능
-- 해석 순서 : 5 (ORDER BY) *위치, 해석 모두 마지막 **별칭 사용 가능
SELECT DATE_FORMAT(HIREDATE, '%m') 입사월  
		, COUNT(EMPNO)
FROM emp 
WHERE DATE_FORMAT(HIREDATE, '%m') != '01' 
GROUP BY DATE_FORMAT(HIREDATE, '%m'); 
ORDER BY 입사월; 

-- 월별 입사자 수가 2명 이상인 데이터만 조회
-- *** HAVING 사용 - GROUP BY가 실행된 후 실행됨
SELECT DATE_FORMAT(HIREDATE, '%m') 입사월
		, COUNT(EMPNO) 입사자수
FROM emp
GROUP BY 입사월 	          -- DATE_FORMAT(HIREDATE, '%m') **별칭 사용 가능
	HAVING 입사자수 >= 2;  -- HAVING COUNT(EMPNO) >= 2 
						  -- **별칭 사용 가능

-- 10월에 입사한 사원들을 제외하고, 월별 입사자 수를 조회
-- 월별 입사자 수가 2명 이상인 데이터만 조회
-- 조회시 월별 입사자 수가 높은 순으로 조회
SELECT DATE_FORMAT(HIREDATE, '%m') 입사월
		, COUNT(EMPNO) 입사자수
FROM emp
WHERE DATE_FORMAT(HIREDATE, '%m') != '10'
GROUP BY 입사월
	HAVING 입사자수 >= 2;
ORDER BY 입사인원 DESC;

📌 DISTINCT - 중복 제거

• 지정된 열의 고유 값이 들어 있는 1열 테이블을 반환
• 즉, 중복 값이 제거되고 고유한 값만 반환됨.

-- 중복을 제거한 데이터 조회
SELECT DISTINCT JOB
FROM emp;

ㅤ

---

🔍 참조

• https://codingwone.tistory.com/24 (코딩하는동그리:티스토리)