[TIL] DB-03 - 매핑 룰과 정규화, GROUP BY와 HAVING, JOIN

W L·2026년 8월 26일
TIL

TIL

목록 보기
19/32

1. 오늘 학습한 주제

  • MAPPING RULE
  • 정규화
  • GROUP BY와 그룹함수
  • HAVING
  • 계층적 집계 — WITH ROLLUP
  • window function — OVER(PARTITION BY | ORDER BY)
  • JOIN — ANSI 표준구문, ON / USING
  • OUTER JOIN, SELF JOIN, EXISTS
  • 워크북 실습 — 집계 문항(10, 12 ~ 15), Additional SELECT - Option

2. 배운 내용

MAPPING RULE

ERD를 관계형 데이터베이스 이론에 입각해서 스키마로 변환하는 과정이다.

N:M은 사실상 없다. 1:1 관계와 1:N 관계로 다 풀어낸다.

정규화

목적은 세 가지다.

  • 데이터 중복 방지
  • NULL 값 방지
  • 논리적인 작은 단위의 테이블로 분리

제 1 정규화

전이된 기본키가 자식 테이블의 기본키가 아니면 비식별 관계, 기본키이면 식별 관계다.

제 2 정규화

복합키 일부에만 의존하는 컬럼이 존재한다면 제거해야 한다.

GROUP BY

특정 컬럼에 대해 동일한 값을 가지는 행들을 하나의 행으로 처리한다(통계).

GROUP BY {column | expr | position}

그룹함수는 일반적으로 GROUP BY와 같이 사용되는 경우가 흔하다. GROUP BY 없이 쓰면 테이블 전체가 하나의 그룹이 된다.

SELECT MIN(SALARY)
FROM employee;

부서별 급여 총합.

SELECT DEPT_ID
		,SUM(SALARY)
FROM employee
GROUP BY DEPT_ID;

집계 컬럼과 비집계 컬럼

-- 원래는 이렇게 하면 안됨
SELECT EMP_NAME
		,DEPT_ID
		,SUM(SALARY)
FROM employee
GROUP BY DEPT_ID;

EMP_NAME은 그룹으로 묶이지 않은 컬럼이다. MariaDB의 기본 sql_mode에서는 에러 없이 실행되지만, 이때 나오는 EMP_NAME은 해당 그룹에 속한 행 중 임의의 한 값이다.

표현식으로 그룹 만들기

GROUP BY의 대상은 컬럼만이 아니라 표현식도 된다. CASE로 만든 구간을 그대로 그룹 기준으로 쓴다.

-- Q) 급여등급별 인원수 집계
SELECT CASE WHEN 
					SALARY <= 3000000 THEN '초급'
				WHEN 
					SALARY <= 4000000 THEN '중급'
				ELSE '고급'
			END AS `급여등급`
		,COUNT(*) AS `COUNT`
FROM employee
GROUP BY 급여등급
ORDER BY FIELD(급여등급, '중급', '초급', '고급');

MariaDB에서는 GROUP BY와 ORDER BY에서 SELECT 절의 alias를 참조할 수 있다.

정렬 기준이 사전순도 숫자순도 아닐 때는 FIELD()에 순서를 직접 나열한다.

주민번호 뒷자리 첫 글자로 성별을 만들어 묶는 것도 같은 형태다.

-- Q) 성별에 따른 평균 급여를 검색한다면?
SELECT CASE WHEN 
					SUBSTRING(EMP_NO, 8, 1) IN ('1', '3') THEN '남자'
				WHEN
					SUBSTRING(EMP_NO, 8, 1) IN ('2', '4') THEN '여자'
			
		 END AS `성별`
		 ,ROUND(AVG(SALARY))
FROM employee
WHERE DEPT_ID = '50' OR DEPT_ID IS NULL
GROUP BY 성별
ORDER BY 2 DESC;

ORDER BY 2는 SELECT 절의 두 번째 컬럼을 가리킨다.

HAVING

SELECT DEPT_ID
		,SUM(SALARY)
FROM employee
WHERE SALARY >= 3000000
GROUP BY DEPT_ID
HAVING SUM(SALARY) >= 4000000;

WHERE는 그룹을 만들기 전 행을 거르고, HAVING은 만들어진 그룹을 거른다. 그래서 집계 결과에 대한 조건은 HAVING에만 쓸 수 있다.

-- Q) 부서별 급여 총액이 9000000 이상인 부서만 검색한다면?
SELECT DEPT_ID
		,SUM(SALARY)
FROM employee
GROUP BY DEPT_ID
HAVING SUM(SALARY) >= 9000000;

WITH ROLLUP

계층적 집계 결과를 만든다. GROUP BY에 나열한 컬럼 순서대로 소계 행과 총계 행이 추가된다.

-- Q) 구매한 목록 중 그룹 이름별 구매 비용을 검색한다면?
SELECT groupName
		,userID
		,SUM(price * amount)
FROM buytbl
GROUP BY groupName, userID WITH ROLLUP;

window function

window function(분석함수) : 기존 행을 유지하면서 집계결과를 추가
group function(avg, rank, row_number, dense_rank) ~ over(partition by | order by)

GROUP BY는 행을 묶어서 줄이지만, window function은 행을 그대로 두고 집계 결과를 컬럼으로 붙인다.

SELECT 	EMP_NAME
			,SALARY
			,DEPT_ID
			,AVG(SALARY) OVER(PARTITION BY DEPT_ID) AS `DAVG`
FROM employee;

순위 함수는 세 개를 같은 조건으로 나란히 실행해 비교했다.

SELECT 	EMP_NAME
			,SALARY
			,RANK() OVER(ORDER BY SALARY DESC) AS `RANK`
FROM employee;
SELECT 	EMP_NAME
			,SALARY
			,DENSE_RANK() OVER(ORDER BY SALARY DESC) AS `RANK`
FROM employee;
SELECT 	EMP_NAME
			,SALARY
			,ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS `RANK`
FROM employee;

동점 처리에서 갈린다. RANK()는 같은 순위 뒤의 번호를 건너뛰고, DENSE_RANK()는 건너뛰지 않으며, ROW_NUMBER()는 동점이어도 유일한 번호를 매긴다.

PARTITION BY를 붙이면 순위를 그룹 안에서 다시 매긴다.

-- Q) 부서별 급여 순위 검색한다면? (부서마다 따로 순위를 정하고 싶다면)
SELECT 	EMP_NAME
			,DEPT_ID
			,SALARY
			,RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS `RANK`
FROM employee;

JOIN

join
- n개 이상의 테이블을 서로 묶어서(가상의 테이블) 하나의 결과 집합을 만들어 내는 것
- 관계형 데이터베이스의 가장 큰 특징
- 관계(1:1, 1:N, N:M)

ANSI 표준구문

SELECT
FROM	TBL
[INNER] JOIN 	TBL ON (조건식)
[INNER] JOIN	TBL USING (컬럼명)

NATURAL[INNER] JOIN TBL;
- 부모의 기본키와 자식의 외래키를 알아서 찾아서 JOIN

LEFT | RIGHT [OUTER] JOIN TBL;

JOIN 조건을 주지 않으면 CARTISIAN PRODUCT

FROM에 테이블을 나열하고 WHERE에 조건을 거는 방식과, ANSI 표준구문은 같은 결과를 낸다.

SELECT E.EMP_NAME
		,D.DEPT_NAME
FROM employee E, department D
WHERE E.DEPT_ID = D.DEPT_ID;
SELECT E.EMP_NAME
		,D.DEPT_NAME
FROM employee E
-- JOIN department D ON (E.DEPT_ID = D.DEPT_ID);
JOIN department D USING (DEPT_ID);

양쪽 컬럼명이 같으면 USING (컬럼명)으로 줄여 쓴다.

조건을 주지 않으면 CROSS JOIN이 된다.

-- CROSS JOIN (의미없음)
SELECT E.EMP_NAME
		,D.DEPT_NAME
FROM employee E
JOIN department D;

JOIN은 이어 붙일 수 있다.

SELECT EMP_NAME
		,JOB_TITLE
		,DEPT_NAME
FROM job J
JOIN employee E USING(JOB_ID)
JOIN department D USING(DEPT_ID);

JOIN 조건이 반드시 등호일 필요는 없다. 급여 등급 테이블은 범위로 붙인다.

SELECT EMP_NAME
		,JOB_TITLE
		,DEPT_NAME
		,LOC_DESCRIBE
		,C.COUNTRY_NAME
		,S.SLEVEL
FROM job J
JOIN employee E ON(J.JOB_ID = E.JOB_ID)
JOIN department D ON(E.DEPT_ID = D.DEPT_ID)
JOIN location L ON(L.LOCATION_ID = D.LOC_ID)
JOIN country C ON(C.COUNTRY_ID = L.COUNTRY_ID)
JOIN sal_grade S ON(E.SALARY BETWEEN S.LOWEST AND S.HIGHEST)
WHERE DEPT_NAME LIKE '해외%'
AND LOC_DESCRIBE LIKE '아시아%'
ORDER BY S.SLEVEL DESC;

OUTER JOIN

INNER JOIN은 양쪽에 다 있는 행만 남긴다. 한쪽 기준으로 전부 살리려면 OUTER JOIN이다.

SELECT E.EMP_NAME
		,D.DEPT_NAME
FROM employee E
LEFT JOIN department D USING(DEPT_ID);

짝이 없는 쪽은 NULL로 채워지므로, 그 NULL을 조건으로 걸면 "없는 것"을 찾을 수 있다.

-- Q) 부서배치를 받지 않은 사원의 이름, 부서명을 검색한다면?
SELECT E.EMP_NAME
		,D.DEPT_NAME
FROM employee E
LEFT JOIN department D ON (E.DEPT_ID = D.DEPT_ID)
WHERE DEPT_NAME IS NULL;

구매이력이 없는 회원을 찾는 것도 같은 형태다.

-- Q) 구매이력이 없는 회원만 검색한다면?
SELECT DISTINCT U.userID
		,U.`name`
		,B.prodName
		,CONCAT(U.mobile1, U.mobile2)
FROM usertbl U
LEFT JOIN buytbl B ON (U.userID = B.userID)
WHERE prodName IS NULL;

SELF JOIN

같은 테이블을 alias만 달리해서 JOIN한다. 사원과 사수가 한 테이블에 들어 있는 경우다.

-- Q) 사원이름과 사수의 이름을 검색한다면?
SELECT E.EMP_NAME
		,M.EMP_NAME
		,S.EMP_NAME
FROM employee E
LEFT JOIN employee M ON (E.MGR_ID = M.MGR_ID)
LEFT JOIN employee S ON (M.MGR_ID = S.MGR_ID);

JOIN 조건이 양쪽 다 MGR_ID로 되어 있어, 사원-사수 관계가 의도대로 연결되는지는 결과 확인이 필요하다.

EXISTS

JOIN으로 붙여서 DISTINCT로 중복을 제거하는 대신, 존재 여부만 확인한다.

SELECT U.userID
		,U.`name`
		,U.addr
		,CONCAT(U.mobile1, U.mobile2)
FROM usertbl U
WHERE EXISTS ( 
			SELECT *
			FROM buytbl B
			WHERE U.userID = B.userID)

서브쿼리가 바깥 행의 userID를 참조한다. 자식 테이블 컬럼을 결과에 넣지 않아도 되므로 행이 늘어나지 않는다.

워크북 실습 — 집계 (지난 시간 스킵 문항)

Q10) 학과별 학생 수

SELECT DEPARTMENT_NO AS `학과번호`
		,COUNT(*) AS `학생수(명)`
FROM tb_student
GROUP BY DEPARTMENT_NO;

Q12) 년도 별 평점. TERM_NO에서 앞 4자리를 잘라 그룹 기준으로 쓴다.

SELECT SUBSTRING(TERM_NO, 1, 4) AS `년도`
		,ROUND(AVG(POINT), 1) AS `년도 별 평점`
FROM tb_grade
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTRING(TERM_NO, 1, 4);

Q13) 학과 별 휴학생 수. 휴학생이 0명인 학과도 출력해야 하므로 WHERE ABSENCE_YN = 'Y'로 거르지 않는다.

-- 집계함수 안에 표현식을 이용해서 일반 집계함수가 잡아내지 못하는 0도 출력
SELECT S.DEPARTMENT_NO AS `학과코드명`
		,SUM(CASE WHEN S.ABSENCE_YN = 'Y' THEN 1 ELSE 0 END) AS `휴학생 수`
FROM tb_student S
GROUP BY S.DEPARTMENT_NO;

Q14) 동명이인. 그룹으로 묶은 뒤 개수가 2 이상인 것만 남기므로 HAVING이다.

SELECT STUDENT_NAME AS `동일이름`
		,COUNT(STUDENT_NAME) AS `동명인 수`
FROM tb_student
GROUP BY STUDENT_NAME
HAVING COUNT(STUDENT_NAME) > 1;

Q15) 년도·학기 별 평점과 누적 평점. 년도 별 소계와 총계가 필요하므로 WITH ROLLUP이다.

SELECT SUBSTRING(TERM_NO, 1, 4) AS `년도`
		,SUBSTRING(TERM_NO, 5, 2) AS `학기`
		,ROUND(AVG(POINT), 1) AS `평점`
FROM tb_grade
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTRING(TERM_NO, 1, 4), SUBSTRING(TERM_NO, 5, 2) WITH ROLLUP;

워크북 실습 — Additional SELECT - Option

Q3) 주소지가 강원도나 경기도이면서 1900년대 학번인 학생

SELECT STUDENT_NAME AS `학생이름`
		,STUDENT_NO AS `학번`
		,STUDENT_ADDRESS AS `거주지 주소`
FROM tb_student
WHERE SUBSTRING(STUDENT_ADDRESS, 1, 2) IN ('경기', '강원')
AND CAST(SUBSTRING(STUDENT_NO, 1, 2) AS INT) >= 90
ORDER BY STUDENT_NAME;

Q4) 법학과 교수를 나이 많은 순으로. 학과 이름은 TB_DEPARTMENT에 있으므로 JOIN한다.

SELECT p.PROFESSOR_NAME
		,p.PROFESSOR_SSN
FROM tb_professor p
JOIN tb_department d ON (p.DEPARTMENT_NO = d.DEPARTMENT_NO)
WHERE d.DEPARTMENT_NAME = '법학과'
ORDER BY p.PROFESSOR_SSN;

Q8) 과목과 교수는 TB_CLASS_PROFESSOR를 사이에 두고 연결된다.

SELECT c.CLASS_NAME
		,p.PROFESSOR_NAME
FROM tb_class c
JOIN tb_class_professor cp ON (c.CLASS_NO = cp.CLASS_NO)
JOIN tb_professor p ON (cp.PROFESSOR_NO = p.PROFESSOR_NO)

Q9) 8번 결과에서 계열 조건이 추가되면 TB_DEPARTMENT를 하나 더 붙인다.

SELECT c.CLASS_NAME
		,p.PROFESSOR_NAME
FROM tb_class c
JOIN tb_department d ON (c.DEPARTMENT_NO = d.DEPARTMENT_NO)
JOIN tb_class_professor cp ON (c.CLASS_NO = cp.CLASS_NO)
JOIN tb_professor p ON (cp.PROFESSOR_NO = p.PROFESSOR_NO)
WHERE d.CATEGORY = '인문사회';

Q10) JOIN과 GROUP BY가 같이 들어간다.

SELECT s.STUDENT_NO AS `학번`
		,s.STUDENT_NAME AS `학생 이름`
		,ROUND(AVG(g.`POINT`), 1) AS `전체 평점`
FROM tb_student s
JOIN tb_department d ON (s.DEPARTMENT_NO = d.DEPARTMENT_NO)
JOIN tb_grade g ON (s.STUDENT_NO = g.STUDENT_NO)
WHERE d.DEPARTMENT_NAME = '음악학과'
GROUP BY s.STUDENT_NO;

Q11) 학생-학과-지도교수 세 테이블을 잇는다. 지도교수는 TB_STUDENT.COACH_PROFESSOR_NO가 TB_PROFESSOR를 참조한다.

SELECT d.DEPARTMENT_NAME AS `학과이름`
		,s.STUDENT_NAME AS `학생이름`
		,p.PROFESSOR_NAME AS `지도교수이름`
FROM tb_student s
JOIN tb_department d ON (s.DEPARTMENT_NO = d.DEPARTMENT_NO)
JOIN tb_professor p ON (s.COACH_PROFESSOR_NO = p.PROFESSOR_NO)
WHERE s.STUDENT_NO = 'A313047';

Q12) 2007년도 '인간관계론' 수강생

SELECT s.STUDENT_NAME
		,g.TERM_NO
FROM tb_student s
JOIN tb_grade g ON (s.STUDENT_NO = g.STUDENT_NO)
JOIN tb_class c ON (g.CLASS_NO = c.CLASS_NO)
WHERE SUBSTRING(g.TERM_NO, 1, 4) LIKE '2007'
AND c.CLASS_NAME = '인간관계론';
profile
W L
한량
이전 포스트

[TIL] DB-02 - 단일행 함수, 흐름제어 함수, 복수행 함수

다음 포스트

[TIL] DB-04 - 조인 수행 방식, 서브쿼리, DDL

0개의 댓글