[MSSQL] partition by

partition by 는 전제집합을 기준에 의해 소그룹으로(SUBSET) 나눈다.

아래의 로직은 다음과 같다 TBLINSA 테이블에서 buseo 컬럼을 기준으로 

 

GROUP BY 시켜 파티션을 나눠 서브셋을 생성한 다음, 

 

SELECT 절에 존재하는 buseo값과 일치하는 서브셋에 내용들을 가져와서 집계함수를 통해 계산한다.

 

예시를 위해 사용된 TBLINSA 테이블 조회

SELECT * FROM dbo.TBLINSA WITH(NOLOCK)

 

 

PARTITION BY 를 통해서 소그릅화 시켜 계산해주는 쿼리

SELECT 
	num 
,	name 
,	buseo 
,	SUM(basicPay) OVER(PARTITION BY buseo) AS buseoPaySum 
FROM dbo.TBLINSA WITH(NOLOCK)

아래에는 buseo 이름이 기획부일때만 로직을 그림으로 표현한 것이다. 

 

실제로는 모든부서별로 아래와 같은 로직을 적용해야 한다.

 

 

물론 PARTITION BY 구문 없이 위와같은 결과를 똑같이 만들어 낼 수 있다. 

 

하지만 성능에서 약간의 차이가 나는걸 볼 수 있다.

 

-- 1. 서브쿼리로 처리하는 방법

SELECT 
	num 
,	name 
,	buseo 
,	(SELECT SUM(basicPay) FROM dbo.TBLINSA t2 WHERE t1.buseo = t2.buseo GROUP BY buseo) AS buseoPaySum 
FROM dbo.TBLINSA t1 WITH(NOLOCK) 
ORDER BY buseo


-- 2. CTE 절로 처리하는 방법(INNER JOIN)

WITH CTE_BUSEOPATSUM 
AS 
( 
    SELECT 
	buseo 
,	SUM(basicPay) AS buseoPay  
    FROM dbo.TBLINSA WITH(NOLOCK) 
    GROUP BY buseo 
) 
SELECT 
	t.num 
,	t.name 
,	t.buseo 
,	c.buseoPay  
FROM dbo.TBLINSA t WITH(NOLOCK) 
INNER JOIN CTE_BUSEOPATSUM c WITH(NOLOCK) ON t.buseo = c.buseo 
ORDER BY buseo


-- 3. WINDOW 함수로 처리하는 방법

SELECT 
	num 
,	name 
,	buseo 
,	SUM(basicPay) OVER(PARTITION BY buseo) AS buseoPaySum 
FROM dbo.TBLINSA WITH(NOLOCK) 
ORDER BY buseo

 

1.서브쿼리로 실행했을 경우의 실행계획 및 I/O

 

 

2.CTE 절로 실행했을 경우의 실행계획 및 I/O

 

 

3.WINDOW 함수로 실행했을 경우의 실행계획 및 I/O