20230323_(SQL Server)_PARTITION BY 함수 및 순위 함수

MSSQL – PARTITION BY 함수 및 순위 함수

> 로 나누다

파티션 테이블 기능그룹 내 순위 및 그룹별 집계를 얻는 데 유용할 수 있습니다.

SELECT 순위_함수() OVER (PARTITION BY 칼럼_명 ORDER BY 칼럼명 (ASC | DESC)
FROM 테이블_명;


SELECT 집계_함수(칼럼_명) OVER (PARTITION BY 칼럼_명)
FROM 테이블_명;

---

> 순위 및 집계 함수

---

> PARTITION BY 및 순위 함수 또는 집계 함수의 예

먼저 tempDB에 다음과 같은 임시 테이블을 생성합니다.

Use tempDB;

CREATE TABLE #tmpTbl (
	F_SEQ INT IDENTITY(1,1)
	, F_NAME VARCHAR(10)
	, F_CLASS VARCHAR(10)
	, F_SCORE INT
	);

그리고 임의의 날짜를 입력합니다.

(1) 학생 순위

순위 함수 ROW_NUMBER()를 사용하면 쉽게 순위를 매길 수 있습니다.

SELECT ROW_NUMBER() OVER (ORDER BY F_SCORE DESC) 'F_GRADE', F_CLASS, F_NAME, F_SCORE
FROM #tmpTbl;

이때, LINENUMBER() 기능은 같은 점수를 받은 학생은 같은 등급을 받지 않습니다.

(즉, 중복 순위 무시, 동일한 값이 있는 경우 동일한 순위가 존재하지 않도록 순차적으로 정렬)

다른 결과를 보고 싶다면 나머지 순위 기능을 사용할 수 있습니다.

계급()

SELECT RANK() OVER (ORDER BY F_SCORE DESC) 'F_GRADE', F_CLASS, F_NAME, F_SCORE
FROM #tempTbl;

순위() 함수~이다 점수가 같으면 같은 등급을 받습니다.

(그리고 더블랭킹 적용 후, 변화있다)

DENSE_RANK()

SELECT DENSE_RANK() OVER (ORDER BY F_SCORE DESC) 'F_GRADE', F_CLASS, F_NAME, F_SCORE 
FROM #tmpTbl;

DENSE_RANK() 일도 한다 점수가 같으면 같은 등급이 부여됩니다.

(단, 다음 순위는 더 나아가 어때 계속)

---

(2) 반별 학생 순위

앞선 예시에서는 반과 상관없이 성적이 좋은 학생들이 먼저 나열되었으나 이제는 반으로 그것들을 나열합시다.

이 경우 PARTITION BY 함수를 사용합니다.

— 순위를 F_CLASS로 나누는 쿼리문을 생성합니다.

SELECT F_CLASS, ROW_NUMBER() OVER(PARTITION BY F_CLASS ORDER BY F_SCORE DESC) 'F_GRADE', F_NAME, F_SCORE
FROM #tmpTbl;

---

(3) 각 반의 총점을 결정

학생들이 속한 수업의 총점을 구해봅시다.

SELECT F_CLASS, F_NAME, F_SCORE, SUM(F_SCORE) OVER(PARTITION BY F_CLASS) 'CLASS TOTAL'
FROM #tmpTbl;