[SQL 문법] PARTITION BY(파티션 분할)과 ROW_NUMBER() OVER() 정리

목차
1. Summary
2. PARTITION BY
2-1. 역할
2-2. 구문
2-3.사용예시
3. 그 외 순위구하는 함수 구문

1. Summary

대충 느낌은 아래와 같음

row_number + over + partition by == group by & order by & 순위 매겨줌

 

ROW_NUMBER() OVER() : ()안의 내용에서 순서/순위를 부여함

PRAITION BY : 순위를 매길 그룹을 구분해줌

 

 

2. PARTITION BY

2-1. 역할

그룹 내 순위 및 그룹 별 집계 구할 떄 사용

주로 집계함수() + over()과 함께 쓰인다.

 

2-2. 구문

SELECT 집계함수([컬럼명]) OVER (PARTITION BY [컬럼명]) FROM [테이블명]

 

2-3. 사용 예시

학급별 등수

select *, row_number() over(
			partition by class order by score desc
                            ) as score_rank
from test

- 그룹 내에서 order by를 하려면 > over(partition by ~~ order by ~~)

- 데이터셋 전체(그룹자체)에 order by를 하려면 > 기존대로 select ~ from ~ order by ~

 

학급별 총 점수 출력

select *, sum(score) over (partition by class) as score_sum
from socre_by_student

학급별 평균 점수 출력

select *, avg(score) over (partition by class) as score_avg
from score_by_student

학급별 가장 높은 점수 출력

- 학급별 == partition by class

- 가장 높은 점수 == avg(score)

- 중에 ~ == over( ~ )

select *, max(score) over (partition by class) as score_max
from score_by_student

 

 

 

3. 그 외 순위 함수 구문

score : 100, 98, 98, 95, 92, 90

ROW_NUMBER() OVER(ORDER BY 정렬할 필드)

- 순위를 구하기는 하나 같은 값에 대해 무시함

- 1등, 2등, 3등, 4등, 5등, 6등

SELECT SCORE, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS '등수'
FROM TEST_SCORE

RANK() OVER(ORDER BY 정렬할 필드)

- 같은 값은 동등한 순위로 값을 반환함

- 1등, 2등, 2등, 4등, 5등, 6등

SELECT SCORE, RANK() OVER(ORDER BY SCORE DESC) AS '등수'
FROM TEST_SCORE

DENSE_RANK() OVER(ORDER BY 정렬할 필드)

- 같은 값을 동등한 순위로 반환함 하지만 rank()와 다르게 동등한 순위의 다음 순위는 순차적으로 매겨짐

- 1등, 2등, 2등, 3등, 4등, 5등

SELECT SCORE, DENSE_RANK() OVER (ORDER BY SCORE DESC) AS '등수'
FROM TEST_SCORE

 

 

 

 

참고자료

https://m.blog.navhttps://m.blog.naver.com/seek316/222100091385

[MS-SQL] PARTITION BY - 파티션 분할

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=dlzmstks&logNo=188971992

[MS-SQL] ROW_NUMBER() OVER() 완벽?! 나름 재정리 후 재 포스팅

https://blog.naver.com/dlzmstks/168228548

[MS-SQL] mssql ROW_NUMBER() OVER() / RANK() OVER() / DENSE_RANK() OVER() - 순위구하기