MSSQL PARTITION

 

📌 PARTITION BY 

1️⃣ PARTITION BY란?

PARTITION BY는 하나의 테이블을 특정 기준으로 그룹(파티션)으로 나누는 기능입니다.
엑셀로 치면, 필터(Filter)를 적용하여 그룹별로 데이터를 정렬하는 것과 비슷합니다.

---

2️⃣ PARTITION BY 없이 ROW_NUMBER() 사용하면?

사원번호 부서 입사일

1001	영업	2023-03-01
1002	영업	2022-08-15
1003	IT	2021-06-10
1004	IT	2023-05-20
1005	IT	2019-12-01

만약 ROW_NUMBER()를 사용하여 입사일이 빠른 순서대로 일련번호를 부여하면, 아래처럼 전체 데이터에 대해 번호가 매겨집니다.

SELECT 
    사원번호, 부서, 입사일, 
    ROW_NUMBER() OVER(ORDER BY 입사일 ASC) AS 순번
FROM 직원테이블;

 

🔽 결과 (모든 데이터를 하나의 그룹으로 보고 순번 부여)

사원번호 부서 입사일 순번

1005	IT	2019-12-01	1
1003	IT	2021-06-10	2
1002	영업	2022-08-15	3
1001	영업	2023-03-01	4
1004	IT	2023-05-20	5

➡ 부서별 그룹이 없이 전체 데이터가 하나로 정렬되었음

---

3️⃣ PARTITION BY를 사용하면?

이번에는 PARTITION BY 부서를 추가

SELECT 
    사원번호, 부서, 입사일, 
    ROW_NUMBER() OVER(PARTITION BY 부서 ORDER BY 입사일 ASC) AS 순번
FROM 직원테이블;

🔽 결과 (부서별로 그룹을 나누고, 그 안에서 순번 부여)

 

사원번호 부서 입사일 순번

1005	IT	2019-12-01	1
1003	IT	2021-06-10	2
1004	IT	2023-05-20	3
1002	영업	2022-08-15	1
1001	영업	2023-03-01	2

➡ 부서별로 따로 그룹을 만들고, 그 안에서 정렬 후 순번을 부여함

---

4️⃣ PARTITION BY를 왜 쓰나요?

• 그룹별로 순번을 부여할 때 유용
• 최신 데이터 1건씩 가져올 때 효과적
• 중복 데이터를 정리하거나, 특정 그룹 내에서 순위를 매길 때 활용

---

 

 

 ROW_NUMBER()와 PARTITION BY를 활용한 데이터 그룹화 및 정렬

특정 기준(ORGN_CD, BCD_CD)으로 데이터를 그룹화한 후, 최신 날짜 기준으로 정렬하여 순번을 부여
이를 활용하면 각 그룹별 최신 데이터 조회, 중복 제거, 페이징 처리 등의 작업을 효율적으로 수행 가능

---

1️⃣ SQL 코드 분석

ROW_NUMBER() OVER(PARTITION BY MP.ORGN_CD, MP.BCD_CD ORDER BY ISNULL(MP.MDATE, MP.CDATE) DESC)

📌 코드 해석

1️⃣ PARTITION BY MP.ORGN_CD, MP.BCD_CD

• 데이터를 MP.ORGN_CD(조직 코드)와 MP.BCD_CD(카테고리 코드)별로 그룹화(파티션) 
• 즉, 같은 ORGN_CD + BCD_CD 값을 가진 행끼리 하나의 그룹을 형성

2️⃣ ORDER BY ISNULL(MP.MDATE, MP.CDATE) DESC

• MP.MDATE가 있으면 이를 기준으로 정렬, NULL이면 MP.CDATE를 대신 사용하여 정렬
• 정렬 기준은 내림차순(최신 날짜 우선) 

3️⃣ ROW_NUMBER() 함수 적용

• 각 그룹 내에서 정렬된 순서대로 1부터 시작하는 일련번호(RowNumber)를 부여
• 즉, 각 그룹별 최신 데이터가 RowNumber = 1이 

---

2️⃣ 예제 데이터

ORGN_CD BCD_CD MDATE CDATE

A	X	2023-01-01	2023-02-01
A	X	2023-03-01	NULL
A	Y	2023-02-15	2023-03-15
B	Z	NULL	2023-01-15

이 데이터를 기준으로 ROW_NUMBER()를 적용하면?

---

3️⃣ SQL 실행 결과

ORGN_CD BCD_CD MDATE CDATE RowNumber

A	X	2023-03-01	NULL	1
A	X	2023-01-01	2023-02-01	2
A	Y	2023-02-15	2023-03-15	1
B	Z	NULL	2023-01-15	1

---

4️⃣ 결과 분석

• PARTITION BY ORGN_CD, BCD_CD
  • A, X 그룹: 두 개의 데이터 존재 → 최신 데이터(2023-03-01)에 RowNumber=1
  • A, Y 그룹: 하나의 데이터만 존재 → RowNumber=1
  • B, Z 그룹: 하나의 데이터만 존재 → RowNumber=1
• ORDER BY ISNULL(MDATE, CDATE) DESC
  • NULL을 방지하기 위해 ISNULL()을 활용하여 MDATE가 NULL이면 CDATE를 대신 사용
  • 가장 최신 날짜가 RowNumber = 1을 받도록 내림차순 정렬

---

5️⃣ 활용 사례

✅ 1. 각 그룹별 최신 데이터만 가져오기

WITH LatestData AS (
    SELECT 
        ORGN_CD, 
        BCD_CD, 
        MDATE, 
        CDATE, 
        ROW_NUMBER() OVER(PARTITION BY ORGN_CD, BCD_CD ORDER BY ISNULL(MDATE, CDATE) DESC) AS RowNumber
    FROM 테이블명
)
SELECT * FROM LatestData WHERE RowNumber = 1;

📌 이렇게 하면, 각 그룹의 최신 데이터만 조회 가능!

✅ 2. 중복 데이터 정리

WITH DupData AS (
    SELECT 
        ORGN_CD, 
        BCD_CD, 
        MDATE, 
        CDATE, 
        ROW_NUMBER() OVER(PARTITION BY ORGN_CD, BCD_CD ORDER BY ISNULL(MDATE, CDATE) DESC) AS RowNumber
    FROM 테이블명
)
DELETE FROM DupData WHERE RowNumber > 1;

📌 각 그룹 내에서 가장 최신 데이터(RowNumber = 1)만 남기고 나머지 중복 데이터 삭제!

---

6️⃣ 정리

개념 설명

PARTITION BY	특정 컬럼을 기준으로 데이터를 그룹화
ORDER BY	그룹 내에서 데이터를 정렬
ROW_NUMBER()	각 그룹 내에서 1부터 순차적으로 번호 부여
ISNULL() 활용	NULL 값이 있는 경우 대체 값을 지정하여 정렬