[TIL] SQL복습&심화_28일차(Window 함수 over)

✍🏻 목차 🌞

1. Window Function 이란?

2. Window 함수와 집계 함수의 차이점

3. Window 함수 종류

---

1. Window 함수란?

Window 함수는 한 번에 여러 행을 묶어서 처리 할 수 있는 함수로,

(GROUP BY 구문과 매우 비슷하지만,) 원형의 데이터를 변형시키지 않고도, 다양한 함수를 원형의 데이터셋 행에 동시에 사용할 수 있습니다. (새로운 열(컬럼)을 생성한다)

 

GROUP BY 구문                  Window 함수 구문

---

2. Window 함수와 집계 함수의 차이점

1) 결과 행 수의 차이: 집계 함수는 그룹 별로 묶어서 하나의 요약된 값을 반환하는 반면,

  윈도우 함수는 데이터의 행 수를 유지하며 각 행에 대해 계산된 값을 반환합니다.

 

2) 윈도우 함수는 집계 함수와 비슷하지만, 그룹별로 데이터를 집계하는 대신 각 행을 개별적으로 처리하면서도 결과를 반환하는데, 이는 일반 집계 함수와의 주요 차이점입니다.

 

3) 윈도우 함수는 OVER 절과 함께 사용되며, 이는 함수가 적용될 데이터의 "윈도우"를 정의합니다.

    OVER 절: 윈도우 함수가 적용될 범위를 정의

<윈도우 함수> OVER (
    [PARTITION BY <분할할 열>]
    [ORDER BY <정렬할 열>] # ORDER를 넣으면 누적이 된다.
    [ROWS or RANGE <윈도우 프레임>]
)

 

4) 사용하는 문법: 집계 함수는 주로 GROUP BY 절과 함께 사용되며, 데이터의 그룹별 요약을 제공하지만, window 함수는 OVER 절과 함께 사용되어 각 행에 대해 추가적인 계산 정보를 제공합니다.

 

5) 사용하는 경우: 집계 함수는 전체 그룹 또는 서브 그룹의 요약된 값을 필요로 할 때 사용됩니다.

반면 윈도우 함수는 각 행을 유지하는 범위 내에서 계산된 값을 제공하여 순위, 이동 평균, 누적 합계 등을 계산할 때 유용합니다.

---

3. Window 함수 종류

1)⭐순위 매기기 : RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, FIRST_VALUE() & LAST_VALUE() 

RANK & DENSE_RANK 개념: 순위를 부여하는 함수로, 동순위 발생 시 RANK는 순위 건너뛰고[1,1,3,4], DENSE_RANK[1,1,2,3]는 건너뛰지 않습니다.

실습: employees 테이블에서 급여 순으로 순위 매기기

SELECT name,
       RANK() OVER (ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

 

ROW_NUMBER() 개념: 각 행에 고유 번호를 부여합니다.

실습: employees 테이블에서 급여가 큰 순으로 각 행에 번호 매기기

SELECT name,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

 

LAG() & LEAD() 개념: 현재 행을 기준으로 이전(뒤) LAG() 또는 다음 행(앞)의 값 LEAD() 참조

실습: employees 테이블에서 현재 행을 기준으로 이전 및 다음 행의 급여 값 참조하기

SELECT name,
       salary,
       LAG(salary, 1) OVER (ORDER BY salary DESC) as prev_salary,
       # 현재보다 1단계 작은 salary, 만약 LAG(salary,2)면 2단계 작은 salary
       LEAD(salary, 1) OVER (ORDER BY salary DESC) as next_salary
       # 현재보다 1단계 높은 salary, 만약 LEAD(salary,2)면 2단계 높은 salary
FROM employees;

 

FIRST_VALUE() & LAST_VALUE() 개념: 윈도우 내 첫 번째 및 마지막 값 반환

실습: employees 테이블에서 윈도우 내 첫 번째 및 마지막 급여 값 참조하기

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING는 범위를 지정해서 겹치지 않게 지정

SELECT name,
       salary,
       FIRST_VALUE(salary) 
       OVER (ORDER BY salary DESC) as highest_salary,
       LAST_VALUE(salary) 
       OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
# RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING는 범위를 지정해서 겹치지 않게 지정
FROM employees;

 

2) 이동 계산 [AVG]

시계열 데이터에서 이동 평균을 계산하여 데이터의 추세를 파악하는 데 사용됩니다. 이동 평균은 데이터의 불규칙한 요소를 보다 부드럽게 표현하는 데 도움이 됩니다.

 

3) 누적 합계 또는 누적 평균 [SUM, AVG]

일정 기간 동안의 누적 합계나 평균을 계산하는 데 사용됩니다. 예를 들어, 매일 증가하는 재고 수량을 누적 합산하여 전체 재고의 추이를 파악할 수 있습니다.

예제: 각 그룹 별 누적 합계 계산

SELECT department,
       employee,
       salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) as cumulative_salary
FROM employees;

 

4) 그룹별 집계 [SUM, MAX, MIN]

특정 그룹에 대한 집계를 계산할 때 window 함수가 유용합니다. 예를 들어, 부서별로 최고 월급을 찾는 등의 작업에 활용됩니다.

예제: 각 그룹 별 누적 합계 계산

 

 

5) 백분위수 계산 [PERCENT_RANK, NTILE]

데이터의 분포를 이해하고 이상치를 탐지하기 위해 백분위수를 계산할 때 window 함수가 사용됩니다.

특정 백분위수보다 큰 값이나 작은 값들을 확인하여 데이터의 특성을 분석할 수 있습니다.

NTILE() 개념: 데이터를 N개의 구간으로 나누기

실습: employees 테이블에서 급여를 4개 구간으로 나누기

SELECT name,
       NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;

---

💡[ Tip ] SQL생각 흐름 🧠 

1. 기본구조를 적기 : select from where

2. 어떤 데이터를 가져올 테이블을 적기 : from 테이블

 → 만약 조건이 잘 파악이 안 되면 (전체) 테이블만 우선 실행하여 결과를 보고 조건 찾기

3. 어떤 컬럼을 이용할 것인지 선택 : select 컬럼

4. 어떤 조건을 지정할 지 적기 : where 조건, group by / order by

5. 어떤 함수(수식) 을 이용해야 하는가 → 갯수 구하는 수식

 

 

---

 

ADsP 시험 정보가 궁금하신 분들은 여기를 클릭 💨

이전 내용이 궁금하다면 여기를 클릭 💨

[🔎복습 ] replace, substr, concat, if/case에 대해 더 알고싶다면 여기를 클릭🔥

 

🌸 오늘 하루도 즐거운 하루 되세요🌄