ChatGPT로 데이터 분석 공부하기 #11 윈도우함수

 

윈도우 함수(Window Function)는 SQL에서 특정 범위(윈도우)에 대한 연산을 수행하며, 일반적인 집계 함수와 달리 결과 집합의 각 행에 대해 계산을 수행합니다. 즉, 행을 제거하지 않고도 집계 결과를 얻을 수 있어 데이터 분석에 매우 유용합니다.

1. 윈도우 함수의 기본 구조

---

윈도우 함수는 OVER() 절과 함께 사용되며, 특정 기준에 따라 행을 나누거나 정렬할 수 있습니다.

기본 문법:

<윈도우 함수>(열)
OVER (
    [PARTITION BY 기준열]
    [ORDER BY 정렬기준열]
)

• 윈도우 함수: SUM, AVG, COUNT, ROW_NUMBER 등
• PARTITION BY: 데이터를 그룹으로 나눕니다. (선택 사항)
• ORDER BY: 데이터를 정렬 기준에 따라 정렬합니다. (선택 사항)

2. 주요 윈도우 함수

---

(1) ROW_NUMBER()

• 행 번호를 반환합니다. 주로 순번을 매길 때 사용됩니다.

예제:

SELECT Name, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

• Employees 테이블에서 급여 순으로 행 번호를 매깁니다.

---

(2) RANK()

• 행 번호를 반환하되, 동점인 경우 동일한 순위를 부여합니다. 이후 순위는 건너뜁니다.

예제:

SELECT Name, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

• Employees 테이블에서 급여 순으로 순위를 매기되, 동일 급여인 경우 같은 순위를 부여합니다.

---

(3) DENSE_RANK()

• RANK()와 유사하지만, 동점 이후 순위를 건너뛰지 않습니다.

예제:

SELECT Name, Salary,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

• Employees 테이블에서 급여 순으로 순위를 매기되, 순위 간 간격이 없습니다.

---

(4) NTILE(n)

• 데이터를 n개의 그룹으로 나누고, 각 그룹에 번호를 부여합니다.

예제:

SELECT Name, Salary,
       NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;

• Employees 테이블을 급여 순으로 정렬한 후, 4개의 그룹으로 나누고 그룹 번호를 부여합니다.

---

(5) SUM(), AVG(), COUNT(), MIN(), MAX()

• 윈도우를 기준으로 집계 연산을 수행합니다.

예제:

SELECT Name, DepartmentID, Salary,
       SUM(Salary) OVER (PARTITION BY DepartmentID) AS TotalDeptSalary,
       AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDeptSalary
FROM Employees;

• 각 부서(DepartmentID)별로 급여의 합계와 평균을 계산합니다.

---

(6) LAG()

• 이전 행의 값을 반환합니다.

예제:

SELECT Name, Salary,
       LAG(Salary) OVER (ORDER BY Salary DESC) AS PrevSalary
FROM Employees;

• Employees 테이블에서 급여 순으로 정렬 후 이전 행의 급여를 반환합니다.

---

(7) LEAD()

• 다음 행의 값을 반환합니다.

예제:

SELECT Name, Salary,
       LEAD(Salary) OVER (ORDER BY Salary DESC) AS NextSalary
FROM Employees;

• Employees 테이블에서 급여 순으로 정렬 후 다음 행의 급여를 반환합니다.

3. 윈도우 함수 활용 예제

---

(1) 부서별 직원 급여 순위

SELECT Name, DepartmentID, Salary,
       RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank
FROM Employees;

• 부서(DepartmentID)별로 직원의 급여 순위를 매깁니다.

---

(2) 월별 누적 매출 계산

SELECT Month, Revenue,
       SUM(Revenue) OVER (ORDER BY Month) AS CumulativeRevenue
FROM Sales;

• 월별 매출 데이터를 누적합으로 계산합니다.

---

(3) 특정 기준으로 변화 추적 (전월 대비 매출 증감)

SELECT Month, Revenue,
       Revenue - LAG(Revenue) OVER (ORDER BY Month) AS RevenueChange
FROM Sales;

• 전월 대비 매출 증감을 계산합니다.

4. 윈도우 함수와 집계 함수 비교

---

특징	윈도우 함수	집계 함수
결과 반환 방식	행마다 계산 결과를 반환	그룹별로 하나의 결과를 반환
데이터 유지	원본 행을 유지	원본 행이 축약
사용 사례	행별 추가 계산, 누적합, 순위 계산	전체 합계, 평균, 그룹별 통계

 

예제:

 

윈도우 함수:

각 행에 부서별 급여 합계를 표시합니다.

SELECT Name, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS DeptTotal FROM Employees;

 

집계 함수:

부서별로 하나의 합계만 반환합니다.

SELECT DepartmentID, SUM(Salary) AS DeptTotal FROM Employees GROUP BY DepartmentID;

 

 

5. 윈도우 함수의 실무 활용

---

1. 데이터 분석:
   • 매출 데이터의 누적합, 평균 매출, 전월 대비 증감 계산.
2. 순위 및 랭킹:
   • 고객별 구매 순위, 제품별 판매 순위를 계산하여 인사이트 도출.
3. 변화 추적:
   • 고객의 구매 행동 변화, 주식 가격 변동 등 과거 데이터를 기반으로 현재 데이터를 비교.

윈도우 함수는 데이터의 정교한 분석과 인사이트 도출에 필수적인 도구로, 특히 비즈니스 분석과 데이터 과학에서 매우 유용합니다.