[TIL] SQLD 준비 PIVOT/UNPIVOT, RANK() (2025-02-17)

✅ 오늘 공부한 거

PIVOT/UNPIVOT, LAG()/LEAD() 활용, 서브쿼리 종류 등을 학습했다.
이론뿐만 아니라 직접 문제를 풀어보며 RANK(), DENSE_RANK(), ROW_NUMBER(), LAG()/LEAD(), PERCENT_RANK(), FIRST_VALUE() 등의 실용적인 SQL 기법을 비교하며 학습했다.

---

📌 1. PIVOT vs UNPIVOT

• 💡 문제: 행을 열로 변환하거나, 열을 행으로 변환하는 SQL 문법은?

• 💡 비교:

  • PIVOT: 행(Row) 데이터를 열(Column)로 변환 (보고서, 데이터 요약에 유용)
  • UNPIVOT: 열(Column) 데이터를 행(Row)로 변환 (데이터 정규화에 유용)

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

-- PIVOT: 연도를 기준으로 매출을 열(Column)로 변환
SELECT * FROM (
    SELECT emp_name, sales_year, sales_amount
    FROM sales
) 
PIVOT (
    SUM(sales_amount) FOR sales_year IN (2022 AS "2022년", 2023 AS "2023년")
);

-- UNPIVOT: 열(Column)을 다시 행(Row)로 변환
SELECT emp_name, sales_year, sales_amount
FROM sales_pivoted
UNPIVOT (
    sales_amount FOR sales_year IN ("2022년", "2023년")
);

🔹 결론:

• PIVOT은 데이터를 요약할 때 유용하지만, 너무 많은 열(Column)이 생길 수 있음.
• UNPIVOT은 데이터를 정규화할 때 유용하지만, 행(Row)이 많아질 수 있음.

---

📌 2. RANK(), ROW_NUMBER(), DENSE_RANK() 차이

• 💡 문제: 순위를 매길 때 RANK(), ROW_NUMBER(), DENSE_RANK()의 차이점은?

• 💡 비교:

  • RANK(): 동일한 값이 있으면 같은 순위를 부여하지만, 다음 순위는 건너뜀.

  • DENSE_RANK(): 동일한 값이 있으면 같은 순위를 부여하지만, 다음 순위를 연속적으로 부여.

  • ROW_NUMBER(): 동일한 값이라도 무조건 새로운 순위를 부여.

SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

🔹 결론:

• RANK()는 순위 건너뜀, DENSE_RANK()는 순위 연속, ROW_NUMBER()는 고유한 순위 부여.

• 중복을 고려한 순위 처리가 필요하면 RANK() 또는 DENSE_RANK()를 사용하고, 중복 없이 고유한 순위가 필요하면 ROW_NUMBER()를 사용.

---

📌 3. GROUP BY와 윈도우 함수 충돌 문제

• 💡 문제: GROUP BY와 RANK(), ROW_NUMBER() 같은 윈도우 함수를 같이 사용하면 오류가 발생하는 경우와 해결 방법?

• 💡 오류 발생 원인:

  • GROUP BY는 데이터를 그룹화하여 각 그룹별로 한 개의 결과만 남김.

  • RANK(), ROW_NUMBER() 같은 윈도우 함수는 개별 행(Row) 기준으로 순위를 매김.

  • 따라서, GROUP BY가 데이터 개수를 줄이면 윈도우 함수가 적용될 개별 행이 사라져 오류 발생.

• 💡 해결 방법: 서브쿼리 활용

SELECT department, total_salary, 
       RANK() OVER (ORDER BY total_salary DESC) AS rank
FROM (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
) AS grouped_data;

🔹 결론:

• GROUP BY 후 서브쿼리에서 별도로 순위를 매겨야 함.
• GROUP BY에서 개별 행이 사라지므로, 직접 RANK()를 적용하면 오류 발생.

---

📌 4. LAG() vs LEAD()

• 💡 문제: 이전 행 또는 다음 행 값을 조회하는 방법은?

• 💡 비교:

  • LAG(): 현재 행을 기준으로 이전 행의 값을 가져옴

  • LEAD(): 현재 행을 기준으로 다음 행의 값을 가져옴

SELECT emp_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary,
       LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

🔹 결론:

• LAG()와 LEAD()는 시계열 분석, 변화 감지 등에 유용함.

• LAG(salary, 1, 0)처럼 세 번째 인자로 기본값을 설정 가능. (두 번째는 얼만큼 움직일지)

---

✅ 오늘의 배운 점 정리

1. PIVOT/UNPIVOT을 활용하여 데이터를 가독성 높게 변환할 수 있다.

2. RANK(), DENSE_RANK(), ROW_NUMBER()의 차이를 확실히 이해했다.

3. GROUP BY와 윈도우 함수(RANK(), ROW_NUMBER())를 함께 사용하면 오류 발생 가능 → 서브쿼리를 활용해야 한다.

4. LAG(), LEAD()를 사용하여 이전 행 및 다음 행 데이터를 조회할 수 있다.