[SQL 공부] WINDOW_FUNCTION 윈도우 함수 OVER 완벽 이해 정리

 

윈도우 함수에 항상 쓰이는 OVER의 용도는 무엇인가 ❓❔❓❔❓

 

윈도우 함수 형태 

<윈도우 함수>(<컬럼 또는 표현식>) 
OVER ([PARTITION BY <컬럼>] [ORDER BY <컬럼>] )

 

 

• <윈도우 함수>:
  • 집계 함수(Aggregate Function): SUM, AVG, COUNT, MAX, MIN 등
  • 순위 함수(Ranking Function): ROW_NUMBER, RANK, DENSE_RANK 등
  • 행 관련 함수(Value Function): LAG, LEAD, FIRST_VALUE, LAST_VALUE 등

차례대로 이해해보자!

 

 

1. OVER 없이 집계함수를 사용할 때 

SELECT SUM(weight) AS total_weight
FROM Queue;

 

OUTPUT 

total_weight

500

 

- 테이블의 모든 weight 값 합산

- 1개의 값으로 반환

 

 

2. OVER를 포함해서 집계함수를 사용할 때 

SELECT person_id, SUM(weight) OVER() AS total_weight
FROM Queue;

 

OUTPUT

person_id	total_weight
1	500
2	500
3	500

 

- 각 행에 전체 합계 컬럼을 붙여서 테이블로 만들어줌

- SUM(weight) = 500이라는 값을 모든 행에 붙인 느낌 

 

 

3. PARTITION BY로 그룹화 해서 계산하기 

SELECT person_name, group_id, SUM(weight) OVER(PARTITION BY group_id) AS group_weight
FROM Queue;

 

OUTPUT

person_name	group_id	group_weight
A	1	230
B	0	270
C	1	230
D	0	270
E	1	230
F	0	270

 

- group_id로 그룹화해서 SUM 집계 >> group_id =1 의 weight는 230, group_id = 2 의 weight는 270이 나왔을 때

- group_id로 그룹화해서 SUM 한 결과를 group_id에 맞게 붙이는 컬럼 생성

- 그룹별 합계를 적어주는 컬럼 완성 🍀

 

 

4. ORDER BY 로 계산 순서를 정하여 누적 집계하기 

SELECT person_id, turn, weight, SUM(weight) OVER(ORDER BY turn) AS total_weight
FROM Queue;

 

OUTPUT

person_id	turn	weight	total_weight
1	1	30	30
2	2	60	90
3	3	90	180
4	4	50	230

 

- turn 컬럼을 순서대로 weight를 SUM 해라

- turn = 1 일때 weight 1

  turn = 2 일때 weight 1 + weight 2

  turn = 3 일때 weight 1 + weight 2 + weight 3

....

 

-  ORDER BY는 누적을 쉽게 해주는 윈도우 함수 맞음! ⭐⭐⭐

 

만약 SUM이 아니고 COUNT라면 ?

- turn 컬럼을 순서대로 weight를 COUNT 해라  >> count는 행 수를 계산하는거니까~ 

- turn = 1 일때 1

  turn = 2 일때 1 + 1

  turn = 3 일때 1 + 1  + 1

 

만약 AVG라면?

- turn = 1 일때 AVG(weight 1)

  turn = 2 일때 AVG (weight 1 + weight 2)

  turn = 3 일때 AVG (weight 1 + weight 2 + weight 3)

 

 

5. 순위함수와 OVER

SELECT person_id, weight, ROW_NUMBER() OVER(ORDER BY weight) AS rank
FROM Queue;

 

OUTPUT

person_id	weight	rank
1	50	3
2	20	4
3	100	1
4	70	2

 

- weight 컬럼을 순서대로 순위를 매기는 컬럼을 만들어라 

 

 

6. LAG, LEAD 함수와 OVER

SELECT person_name, turn, weight, LAG(weight) OVER (ORDER BY turn) AS previous_weight
FROM Queue;

 

OUTPUT

percon_name	turn	weight	previous_weight
alice	1	60	NULL
bob	2	70	60
charlie	3	80	70
david	4	100	80

 

- turn 컬럼을 순서대로 weight의 이전행을 가져와라