[MySQL] Window Function를 활용한 별도 계산 출력

1. Window Function?

쿼리의 각 행에 대해 해당 행과 관련된 행을 사용하여 계산을 수행하는 비집계 함수 함수입니다.
각 행에 대해 누적값, 순위, 평균 등을 계산하는데에 활용하는데 집계함수와 달리 결과를 묶지 않고 계산 결과를 유지합니다.
(행을 그대로 유지하면서 계산한다는 차이점이 있습니다.)

• 일반 집계 함수: group by
  • 그룹별로 하나의 결과만 반환
• window 함수: over()
  • 행을 그대로 유지하면서 별도의 결과를 반환

---

2. 예제

아래와 같은 데이터가 들어있는 user_contract 테이블에서

user_contract_id,user_id,contract_id
1,sherry,C00437
2,jini,C00437
3,lily,C00542
4,jane,C01113
5,jini,C00001
6,lily,C00100
7,lily,C00005

아래 두 예제를 window함수와 일반 집계함수를 활용하여 출력해봅니다.

1. 기존 row를 그대로 출력하면서 user_id별로 user_contract_id 가 가장 큰것을 조회하기
2. user_id 로 그룹핑하여 user_contract_id가 가장 큰 것을 조회하기

OVER (PARTITION BY ...) 는 window function 를 활용하면 기존 row를 유지하면서 user_id별 max(user_contract_id)를 함께 출력할 수 있습니다.

select user_contract_id, user_id, contract_id, 
    max(user_contract_id) over (partition by user_id) max_user_contract_id
from user_contract
where user_id in ('jini', 'sherry', 'lily', 'jane')
order by user_id;

• partition by user_id: 결과값을 user_id 별로 그룹핑하여
• max(user_contract_id) over: 위에서 그룹화된 그룹들 내에서 max(track_contract_id)를 계산합니다.

조회결과는 아래와 같습니다.
원본의 row를 그대로 유지하면서, 최대값을 계산하여 컬럼에 보여줍니다.

user_contract_id,user_id,contract_id,max_user_contract_id
4,jane,C01113,4
5,jini,C00001,5
2,jini,C00437,5
7,lily,C00005,7
6,lily,C00100,7
3,lily,C00542,7
1,sherry,C00437,1

만일 user_id별로 가장큰 user_contract_id를 조회하고 싶음거라면 group by 한 후, max 함수를 적용하면 됩니다.

select user_id, max(user_contract_id) max_user_contract_id
from user_contract
where user_id in ('jini', 'sherry', 'lily', 'jane')
group by user_id;

user_id,max_user_contract_id
jane,4
jini,5
lily,7
sherry,1