[LG U+ 유레카 4기] WEEK 06 - 데이터베이스 (2)

GROUP BY / HAVING, JOIN

---

1. 오늘의 학습 흐름

• GROUP BY / HAVING
• ROLLUP, GROUPING()
• 피벗 테이블 (조건부 집계)
• JOIN 종류 (INNER, OUTER, SELF, CROSS, NATURAL)
• 벤더 전용 문법 vs ANSI 문법
• 인덱스와 쿼리 성능 비교 (1000만 건 실습)

---

2. GROUP BY

특정 컬럼의 값을 기준으로 데이터를 그룹화한다. 집계 함수와 함께 쓰면 그룹별 집계 결과를 얻는다.

-- 부서별 인원수, 평균 급여
SELECT deptno, COUNT(*), AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno;

-- 부서별 + 업무별 (다중 그룹)
SELECT deptno, job, COUNT(*), ROUND(AVG(sal), 2)
FROM emp
GROUP BY deptno, job
ORDER BY deptno, job;

주의: GROUP BY에 없는 컬럼을 SELECT에 단독으로 쓰면 의미 없는 결과가 나온다. Oracle/DB2는 에러, MySQL은 ONLY_FULL_GROUP_BY 설정 시 에러가 난다.

---

3. HAVING

GROUP BY로 집계한 결과에 조건을 거는 절이다.

WHERE  → 행 하나하나에 대한 조건 (집계 전)
HAVING → 집계 결과에 대한 조건 (집계 후)

-- 평균 급여가 2000 이상인 부서만 조회
SELECT deptno, ROUND(AVG(sal), 2) AS avgSal
FROM emp
GROUP BY deptno
HAVING avgSal >= 2000
ORDER BY deptno;

-- WHERE + GROUP BY + HAVING 조합
-- 급여 1000 이상인 사원들 중, 부서별 평균 급여가 2500 이상인 부서
SELECT deptno, ROUND(AVG(sal), 2) AS avgSal
FROM emp
WHERE sal >= 1000           -- 행 필터링 (집계 전)
GROUP BY deptno
HAVING avgSal >= 2500       -- 집계 결과 필터링 (집계 후)
ORDER BY deptno;

NULL 처리와 함께 쓰기:

-- job이 NULL인 경우 '프리랜서'로 표시하면서 집계
SELECT IFNULL(job, '프리랜서') AS job,
       COUNT(*) AS 근무인원,
       ROUND(AVG(sal), 0) AS avgSal
FROM emp
GROUP BY job
HAVING avgSal >= 2500
ORDER BY job;

---

4. ROLLUP - 소계/총계 추가

GROUP BY에 WITH ROLLUP을 붙이면 그룹별 소계와 전체 총계가 자동으로 추가된다.

-- 업무별 통계 + 전체 통계 (ROLLUP으로 마지막 행에 총계 추가)
SELECT IFNULL(job, '프리랜서') AS job,
       COUNT(*) AS 근무인원,
       ROUND(AVG(sal), 0) AS avgSal,
       MIN(sal) AS minSal,
       MAX(sal) AS maxSal
FROM emp
GROUP BY job WITH ROLLUP;

ROLLUP이 추가한 총계 행은 컬럼 값이 NULL로 나온다. GROUPING() 함수로 ROLLUP이 만든 행인지 구분한다.

-- ROLLUP이 만든 행: GROUPING(job) = 1
-- 일반 데이터 행:   GROUPING(job) = 0
SELECT IF(GROUPING(job) = 1, 'total', IFNULL(job, '프리랜서')) AS job,
       COUNT(*) AS 근무인원,
       ROUND(AVG(sal), 0) AS avgSal
FROM emp
GROUP BY job WITH ROLLUP;

---

5. 피벗 테이블 (조건부 집계)

행 데이터를 열로 펼쳐서 표시하는 방법이다. IF() + 집계 함수로 구현한다.

-- 업무별, 부서별 급여 평균을 피벗 테이블로
SELECT IF(GROUPING(job) = 1, '전체 통계', IFNULL(job, '프리랜서')) AS job,
       IFNULL(ROUND(AVG(IF(deptno IS NULL, sal, NULL)), 0), 0) AS 신입사원,
       IFNULL(ROUND(AVG(IF(deptno = 10, sal, NULL)), 0), 0) AS `10번 부서`,
       IFNULL(ROUND(AVG(IF(deptno = 20, sal, NULL)), 0), 0) AS `20번 부서`,
       IFNULL(ROUND(AVG(IF(deptno = 30, sal, NULL)), 0), 0) AS `30번 부서`,
       IFNULL(ROUND(AVG(sal), 0), 0) AS 업무별급여평균
FROM emp
GROUP BY job WITH ROLLUP;

IF(deptno = 10, sal, NULL)이 핵심이다. 해당 부서면 sal 값을 쓰고 아니면 NULL을 넣는다. AVG는 NULL을 제외하고 계산하므로 해당 부서의 평균만 자동으로 나온다.

---

6. JOIN

두 개 이상의 테이블을 연결해서 조회하는 것이다.

JOIN 종류

종류	설명
INNER JOIN	조인 조건에 맞는 데이터만 조회
LEFT OUTER JOIN	왼쪽 테이블은 전부, 오른쪽은 조건 맞는 것만
RIGHT OUTER JOIN	오른쪽 테이블은 전부, 왼쪽은 조건 맞는 것만
SELF JOIN	같은 테이블끼리 조인 (alias 필수)
CROSS JOIN	조인 조건 없이 두 테이블 모든 행 조합 (N × M)
NATURAL JOIN	동일한 컬럼명을 자동으로 조인 조건으로 사용

벤더 전용 vs ANSI 문법

같은 결과를 내는 두 가지 방법이다. ANSI가 표준이라 다른 DB에서도 동작한다.

-- 벤더 전용 (MySQL, Oracle 등 각 DB 전용)
SELECT gno, brand, price, g.cno, name
FROM goods g, category c
WHERE g.cno = c.cno;

-- ANSI (표준, 모든 DB 호환)
SELECT gno, brand, price, g.cno, name
FROM goods g
JOIN category c ON g.cno = c.cno;

-- USING: 조인 컬럼명이 같을 때 사용 (alias 없이)
SELECT gno, brand, price, cno, name
FROM goods
JOIN category USING (cno);

INNER JOIN

조인 조건에 맞는 행만 결과에 나온다. 조건에 맞지 않으면 양쪽 다 조회되지 않는다.

-- 상품번호, 상품명, 가격, 분류번호, 분류명 조회
SELECT gno, brand, price, g.cno, name
FROM goods g
JOIN category c ON g.cno = c.cno
ORDER BY gno;

-- 3개 테이블 조인
SELECT c.name, b.bookname
FROM Customer c
JOIN Orders o ON c.custid = o.custid
JOIN Book b ON o.bookid = b.bookid;

-- Non-Equi JOIN: = 가 아닌 조건으로 조인 (BETWEEN 등)
SELECT empno, ename, sal, grade
FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal;

LEFT OUTER JOIN

왼쪽 테이블의 모든 행이 결과에 나온다. 오른쪽에 매칭되는 게 없으면 NULL로 채워진다.

-- 주문하지 않은 고객도 포함해서 조회
SELECT Customer.name, saleprice
FROM Customer
LEFT JOIN Orders ON Customer.custid = Orders.custid;

-- 상품 중 주문이 없는 상품도 포함, 상품별 총 주문 수량
SELECT gno, brand, IFNULL(SUM(quantity), 0) AS total_quantity
FROM goods
LEFT JOIN orders USING (gno)
GROUP BY gno;

SELF JOIN

같은 테이블을 두 번 사용해서 조인한다. alias로 구별한다.

-- 사원과 상사 이름을 함께 조회
SELECT e.empno, e.ename, e.job, e.sal, e.mgr, m.ename AS manager
FROM emp e
JOIN emp m ON e.mgr = m.empno;

-- 상사가 없는 사원도 포함 (LEFT JOIN)
SELECT e.empno, e.ename, e.mgr, m.ename AS manager
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;

---

7. 인덱스와 쿼리 성능

1000만 건 orders 데이터로 성능 차이를 직접 비교했다.

-- 느린 쿼리 (4.7초): 컬럼에 함수를 적용 → 인덱스 못 탐
SELECT COUNT(*)
FROM orders
WHERE DATE_FORMAT(odate, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 빠른 쿼리 (0.14초): 범위 비교 → 인덱스 탐
SELECT COUNT(*)
FROM orders
WHERE odate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND odate <  DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

약 33배 차이다. 컬럼에 함수를 적용하면 인덱스를 쓸 수 없어서 전체 행을 스캔해야 한다. 범위 비교로 바꾸면 인덱스를 타서 필요한 행만 찾는다.

인덱스 생성:

CREATE INDEX idx_orders_odate_gno ON orders(odate, gno);
CREATE INDEX idx_orders_gno_odate ON orders(gno, odate);
CREATE INDEX idx_orders_id ON orders(id);

---

8. 면접 문제 (SELF JOIN 응용)

weather 테이블에 (ymd, temp, city)가 있을 때, 전날보다 온도가 높아진 날이 가장 많았던 도시를 출력하시오.

SELECT city, COUNT(*) AS hitCount
FROM weather tw
JOIN weather yw
     ON tw.ymd = DATE_ADD(yw.ymd, INTERVAL 1 DAY)
     AND tw.temp > yw.temp
     AND tw.city = yw.city
GROUP BY city
ORDER BY hitCount DESC
LIMIT 1;

같은 테이블을 "오늘"과 "어제"로 나눠서 SELF JOIN하는 패턴이다. tw.ymd = yw.ymd + 1일이 핵심이다.

---

9. 오늘 리뷰

대면 면접 보고 오느라 수업 못들었지만 올라온 자료로 구성해봤다.

오늘 1000만 건 데이터로 쿼리 성능 차이를 직접 봤다. 4.7초 vs 0.14초는 서비스 환경에서 엄청난 차이다. 실무에서 "왜 쿼리가 느리냐"는 질문의 답이 대부분 인덱스 문제라는 걸 체감했다.

피벗 테이블 구현에서 IF(deptno = 10, sal, NULL)이 신선했다. 집계 함수가 NULL을 제외한다는 특성을 역으로 이용한 거다. 특정 조건의 행만 집계하고 싶을 때 NULL을 전략적으로 활용하는 패턴이다.

JOIN에서 벤더 전용과 ANSI 문법을 둘 다 배웠는데, 현업에서는 ANSI를 쓰는 게 DB를 바꿔도 코드가 그대로 동작하니까 유리하다.

---

10. 키워드 정리

GROUP BY HAVING

---

11. 내일의 목표

• 서브쿼리 (Subquery)