[TIL] WHERE 절, ROLLUP/CUBE/GROUPING SETS, 계층형 쿼리(START WITH, CONNECT BY), JOIN의 차이, 윈도우 함수(Window Function) (2025-02-14)

✅ 오늘 공부한 거

SQL의 WHERE 절, ROLLUP/CUBE/GROUPING SETS, 계층형 쿼리(START WITH, CONNECT BY), JOIN의 차이, 윈도우 함수(Window Function) 등을 학습했다.
이론만이 아니라 직접 문제를 풀면서 ORDER SIBLINGS BY, SUM() OVER(), LAG()/LEAD(), RATIO_TO_REPORT() 같은 실용적인 SQL 기법을 비교하며 체감했다.

---

📌 1. WHERE 절과 IS NULL / IS NOT NULL

• 💡 문제: 특정 조건을 만족하는 데이터를 필터링할 때 WHERE 절을 어떻게 사용할까?

• 💡 비교: = 연산자는 NULL과 비교할 수 없으며, NULL 값을 찾을 때는 IS NULL, NULL이 아닌 값을 찾을 때는 IS NOT NULL을 사용해야 한다.

• 💡 경험: WHERE manager_id IS NULL을 사용하여 최상위 노드를 찾는 계층형 쿼리를 작성할 때 유용했다.

SELECT * FROM employees WHERE manager_id IS NULL; -- 매니저가 없는 직원 찾기
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'; -- 날짜 필터링

🔹 결론: WHERE 절에서 NULL 값을 비교할 때는 반드시 IS NULL을 사용해야 한다.

---

📌 2. ROLLUP, CUBE, GROUPING SETS 차이점

• 💡 문제: 그룹별 합계를 구할 때 GROUP BY를 확장하는 방법에는 어떤 것들이 있을까?

• 💡 비교:

  • ROLLUP: 계층적(단계적) 그룹 합계를 계산 (예: 카테고리 → 서브카테고리 → 전체 합계)
  • CUBE: 모든 가능한 조합에 대해 그룹 합계를 계산
  • GROUPING SETS: 지정한 그룹 조합에 대해서만 집계

SELECT category, product, SUM(sales) 
FROM sales_data
GROUP BY ROLLUP(category, product); -- 계층적 그룹 합계

🔹 결론:

• ROLLUP은 계층적 집계를 수행할 때 유용하다.
• CUBE는 다차원 분석이 필요할 때 사용한다.
• GROUPING SETS는 특정 그룹 조합만 계산할 때 효율적이다.

---

📌 3. CONNECT BY PRIOR를 활용한 계층형 쿼리

• 💡 문제: 계층 구조(트리 구조)를 가진 데이터를 SQL에서 어떻게 표현할까?

• 💡 비교: START WITH 절을 사용하여 루트 노드를 설정하고, CONNECT BY PRIOR을 사용하여 부모-자식 관계를 정의한다.

• 💡 경험: ORDER SIBLINGS BY를 활용하면 계층 내 정렬도 가능했다.

SELECT employee_id, employee_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

🔹 결론: 계층형 데이터를 다룰 때 CONNECT BY PRIOR과 ORDER SIBLINGS BY를 활용하면 트리 구조를 쉽게 표현할 수 있다.

---

📌 4. INNER JOIN vs NATURAL JOIN

• 💡 문제: 테이블을 조인할 때 INNER JOIN과 NATURAL JOIN의 차이점은?

• 💡 비교:

  • INNER JOIN: 명확한 ON 조건을 지정하여 두 테이블을 조인
  • NATURAL JOIN: 공통된 컬럼명을 자동으로 찾아서 조인 (예측이 어려울 수 있음)

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id; -- 명확한 조인

🔹 결론:

• INNER JOIN은 명확한 기준을 정할 수 있어 실무에서 선호됨.
• NATURAL JOIN은 컬럼명이 자동 매칭되지만, 예측이 어렵기 때문에 잘 사용하지 않음.

---

📌 5. 윈도우 함수(Window Function) 종류

• 💡 문제: GROUP BY 없이도 개별 행을 유지하면서 집계 연산을 수행하려면?

• 💡 비교:

  • 집계 함수: SUM(), AVG(), COUNT(), MAX(), MIN() → OVER() 사용 시 개별 행 유지
  • 순위 함수: RANK(), DENSE_RANK(), ROW_NUMBER() → 정렬 기준에 따라 순위 부여
  • 비율 함수: RATIO_TO_REPORT() → 그룹 내 값이 전체에서 차지하는 비율 계산
  • 행 이동 함수: LAG(), LEAD() → 이전/다음 행 데이터 조회

SELECT emp_name, dept_name, 
       RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS salary_rank
FROM employees;

🔹 결론:

• 집계 연산을 개별 행을 유지하면서 수행할 때 윈도우 함수가 필수적이다.
• 순위(rank), 이동(offset) 연산이 필요할 때 RANK(), LAG(), LEAD() 등을 활용하면 된다.

---

✅ 오늘의 배운 점 정리

1. WHERE 절에서 IS NULL을 사용해야 NULL 값을 정확히 비교할 수 있다.

2. ROLLUP / CUBE / GROUPING SETS를 활용하면 다양한 그룹별 집계가 가능하다.

3. 계층형 데이터를 다룰 때 CONNECT BY PRIOR과 ORDER SIBLINGS BY가 유용하다.

4. INNER JOIN은 명확한 기준을 설정할 수 있어 NATURAL JOIN보다 안전하다.

5. 윈도우 함수를 활용하면 GROUP BY 없이도 개별 행을 유지하면서 집계 연산이 가능하다.