SQL | 누적합 구하기 - WHILE문, 셀프조인

 

 

 

 

 

 

 

 

WHILE 문과 셀프조인을 활용하여 누적합 구하기

 

 

 

 

위와 같은 간단한 누적합을 구하는 프로시저를 두 가지 방법을 통해 생성한다.

나는 MSSQL을 사용하였으나 로직은 같으므로 어떤 언어든 상관없다.

 

 

1. WHILE 문 활용

-- 프로시저 생성
CREATE PROC TestA
AS
	-- 변수 선언
	DECLARE @Num INT = 1, @Sum INT = 0
	
	-- 값을 넣을 임시 테이블 생성
	CREATE TABLE #TempA
	(
		num		INT,
		nSum	INT
	)
	WHILE(@Num <= 10)
	BEGIN
		-- 누적합 계산
		SELECT @Sum += @Num
		
		-- 임시 테이블에 입력		
		INSERT INTO #TempA (num, nSum)
		SELECT @Num, @Sum
		
		-- 수 증가
		SELECT @Num = @Num + 1	
	END	
	-- 조회
	SELECT num	AS 'Num'
		   nSum AS 'Sum'
      FROM #TempA
	-- 임시 테이블 삭제
	DROP TABLE #TempA
	
RETURN
-- 프로시저 실행문
EXEC TestA

 

 

 

 

2. 셀프조인 활용

-- 프로시저 생성
CREATE PROC TestB
AS
	-- 변수 선언 및 초기화
	DECLARE @Num INT = 1
	
	-- 1부터 10까지 값을 넣을 임시 테이블 생성
	CREATE TABLE #TempB
	(
		num		INT
	)
	WHILE(@Num <= 10)
	BEGIN
		-- 임시 테이블에 1 ~ 10 숫자 입력
		INSERT INTO #TempB(num)
		SELECT @Num
		
		-- 수 증가
		SELECT @Num = @Num + 1
	END	
	-- 조회
	
	--### 셀프조인 ###
     -- 자신을 포함하여 자신보다 작은 숫자들과 조인
     -- 자신을 기준으로 그룹바이 하여 합계 구하기
	SELECT A.num	  AS 'Num',
           SUM(B.num) AS 'Sum'		
      FROM #TempB                AS A 
           LEFT OUTER JOIN #TempB AS B ON A.num >= B.num
     GROUP BY A.num
    
    -- 임시 테이블 삭제
    DROP TABLE #TempB
    
RETURN
-- 프로시저 실행문
EXEC TestB

 

아무리 수정해도 코드블럭 줄이 안 맞아... 스트레스

 

 

셀프조인 접근

 

1. 같은 #TempB 테이블에 각각 Alias를 붙여 두 개의 테이블이 있다고 가정한다.

 

 

 

 

 

 

2. A 테이블의 num 값을 기준으로 A 테이블의 num 값과 같거나 작은 B 테이블의 num 값을 구한다.

   --> A.num >= B.num

 

 

 

3. B 테이블의 num을 A 테이블의 num 값을 기준으로 그룹으로 묶으면 A 테이블의 num 과 같거나 작은 num 들이 해당된다. 그러므로 A 테이블의 num 값을 기준으로 B 테이블의 num 값들을 한 그룹으로 묶는다. 각 그룹 별 B 테이블의 num 들을 합한다.

    --> GROUP BY A.num

    --> SUM(B.num)

 

 

 

 

 

 

잔액합 등을 도출하는 데에 쓰이는 누적합을 WHILE문과 셀프조인을 활용하여 구해보았다.

 

주의할 점은,

WHILE문이 짜기 쉽고 간단하지만 한 행 한 행 계산하며 루프를 도는 방식이므로 데이터의 양이 많을수록 성능이 저하된다.

그러므로 데이터 양이 많은 경우일수록 셀프조인으로 해결하는 것이 좋다. 

 

누적합을 구해야겠다 생각한 순간 WHILE문이 바로 떠오른다. 그래서 늘 WHILE문으로 문제를 해결해왔는데

성능 문제로 셀프조인을 활용하는 경우가 생기기 때문에 셀프조인으로 누적합을 구하는 방법을 알아두는 것이 좋을 것 같아서 포스팅한다.

 

 

 

+ INNER JOIN 추가

LEFT OUTER JOIN이 아닌 INNER JOIN을 활용할 경우 같은 결과를 도출할 수 있지만 

GROUP BY 기준이 달라진다.

 

다음 쿼리문을 실행한 결과는 아래와 같다.

SELECT A.num    AS 'Num',            
       B.num    AS 'Sum'       
  FROM #TempB     AS A             
       JOIN #Temp AS B ON A.num >= B.num

 

--==>> 결과

 

 

때문에 이 때는 GROUP BY B.num 을 해주고 SUM(A.num)을 해야 한다.

바람직한 답은 LEFT OUTER JOIN을 사용한 것인데, 이유는 모르겠다.

INNER JOIN의 경우보다 명시적이고 때문에 실수할 일이 적은 건 분명한데, 다른 이유가 더 있는 건지 모르겠다.