[MSSQL] mybatis에서 동적쿼리+pivot 사용하기+ 행을 열로 바꾸기

* 쿼리 사용하는 케이스 가정

Apparel ERP시스템에서 아래의 테이블들을 가지고
각 주문의 스타일/컬러별 사이즈를 행이 아닌 열의 형태로
동적으로 표현하려고 한다.

1) 발주확정 전 샘플스타일 테이블
2) 발주확정 후 스타일 테이블
3) 스타일별 사이즈 정보 테이블
4) 주문테이블
(주문seq에 따라 스타일, 컬러, 사이즈 컬럼을 가진 테이블)

원하는 결과 구조

주문정보	샘플스타일	스타일	사이즈1	사이즈2	...	사이즈N
		스타일1	(사이즈1의 수량)			(사이즈N의 수량)
	샘플스타일1			(사이즈2의 수량)		(사이즈N의 수량)
		스타일2	(사이즈1의 수량)	(사이즈2의 수량)

 

(방법 1) PIVOT 사용

<select id=“~~” resultType=“HashMap”>
	DECLARE @COLUMNS NVARCHAR(MAX)
	DECLARE @SQL NVARCHAR(MAX)

	SET @COLUMNS = ‘’

	/* 스타일별 사이즈를 뽑아내어 PIVOT항목으로 사용하기 위한 쿼리 */
	SELECT @COLUMNS = @COLUMNS + ‘[‘ + SIZECD + ‘],’
	FROM 	(
			SELECT DISTINCT A.SIZE_CD
			FROM	사이즈테이블 A
			JOIN 	스타일테이블 B
			ON 		(join 조건)
			WHERE	B.STYLE_CD IN (java단에서 넘긴 스타일 목록)
			UNION
			SELECT DISTINCT A.SIZE_CD
			FROM	사이즈테이블 A
			JOIN 	샘플스타일테이블 B
			ON 		(join 조건)
			WHERE	B.SMPL_STYLE_CD IN (java단에서 넘긴 샘플스타일 목록)
			) AS SIZES
	ORDER BY SIZE_CD

	/* 1) 만약 사이즈 목록이 비어있다면 PIVOT쿼리에서 오류가 발생하므로 
		사이즈 목록이 없는 경우에 대해 바로 종료할 수 있는 방어코드 
		2) 사이즈 목록이 존재한다면 마지막 , 문자를 삭제하는 LEFT함수 처리 */
	SELECT @COLUMNS = CASE WHEN @COLUMNS = ‘’ THEN ‘NaN’
				ELSE LEFT(@COLUMNS, LEN(@COLUMNS)-1) END

	SET @SQL = ‘SELECT A.*
		FROM (
			SELECT M.주문정보(주문차수 등)
				, M.SMPL_STYLE_CD
				, M.STYLE_CD
				, M.SIZE_CD AS sizeCd
				, M.REQ_QTY AS reqQty						
            		FROM	주문테이블 M
			WHERE 	M.BRND_CD = ‘’’ + #{변수} + ‘’‘
			AND	M.ORDER_DT = REPLACE(‘ + #{주문일자} + ‘,’’-‘’, ‘’’’)
			AND	M.ORDER_SN = ‘ + #{주문차수} + ’
			) AS RESULT
			PIVOT (
			SUM(reqQty) FOR sizeCd IN (‘ + @COLUMNS +’)
		) AS PIVOT_RESULT’

	EXEC(@SQL)
</select>

쿼리 중간중간에 코멘트를 달았지만
추가로 더 자세하게 작성하면

PIVOT의 경우 사용할 컬럼명을 [사이즈1], [사이즈2]…의 형태로 쿼리에 넣어주어야 하므로
이를 동적으로 만들기 위해

DECLARE문으로
@COLUMNS : ‘[사이즈1], [사이즈2]…’ 의 형태로 텍스트로 만들 변수
@SQL : EXEC함수에 넣어 동적으로 실행할 PIVOT쿼리가 저장되는 변수

@COLUMNS 변수에
스타일/샘플스타일들의 모든 사이즈 목록을
‘[사이즈1], [사이즈2]…’ 이 형태로 추출하여 저장한다

만약 데이터 정합성 문제로
사이즈 정보가 전혀 없는 스타일이라면
맨아래 PIVOT 쿼리실행시 오류가 발생하기 때문에
방어로직으로 @COLUMNS이
초기 선언값인 ‘’(empty string)과 동일하다면
예외케이스로 사이즈정보가 아닐만한 텍스트(나는 NaN으로)로 임시치환하여
PIVOT 쿼리에서 오류가 발생하지 않도록 처리해준다

아래 PIVOT 쿼리에서

AND ORDER_DT = REPLACE(‘ + #{주문일자} + ‘,’’-‘’, ‘’’’)

코드블럭과 같이
single quotation이 많이 있는 이유는
@SQL 변수에 쿼리를 텍스트로 만드는 것이기 때문에

변수삽입을 위한 single quotation으로 생각해야한다
ex) 일반적인 쿼리 실행때는 이렇게 사용

AND ORDER_DT = REPLACE(#{주문일자}, ‘-‘, ‘’)

(방법 2) java단에서 사이즈 목록을 parameter에 직접 전달해준다면

WITH SIZES AS (
	SELECT M.주문정보
			, M.SMPL_STYLE_CD
			, M.STYLE_CD
			, M.SIZE_CD
			, M.REQ_QTY
	FROM 	주문테이블 M
	WHERE 	M.BRND_CD = #{brndCd}
	AND		M.ORDER_DT = REPLACE(#{주문일자}, ‘-’, ‘’)
	AND 	M.ORDER_SN = #{주문차수}
	)
SELECT		A.*
FROM		(
			SELECT M.주문정보
					, M.SMPL_STYLE_CD
					, M.STYLE_CD
					, M.SIZE_CD
					<foreach item=“item” index=“index” collection=“sizeList”>
						, SUM(M.S${index}) AS ‘${item}’
					</foreach>
			FROM (
					 SELECT M1.주문정보
							, M1.SMPL_STYLE_CD
							, M1.STYLE_CD
							<foreach item=“i” index=“index” collection”sizeList”>
								, CASE WHEN SZ.SIZE_CD = #{i} THEN SZ.REQ_QTY END AS ‘S${index}’
							</foreach>
					FROM 	주문테이블 M1
					LEFT JOIN SIZES SZ /*주문 수량이 존재하는 스타일별 사이즈 목록 */
					ON		(join 조건)
					WHERE M1.BRND_CD = #{brndCd}
					AND		M1.ORDER_DT = REPLACE(#{주문일자}, ‘-’, ‘’)
					AND 	M1.ORDER_SN = #{주문차수}
			) M
			GROUP BY M.주문정보, M.SMPL_STYLE_CD, M.STYLE_CD
) A
					

사이즈목록이 숫자, 특수문자(ex 01(XS), 02(S), 03(M) ~ ) 등의 여러 케이스가 있기때문에

S를 붙여서 텍스트로 만들어주거나
리스트에서 항목값 자체를 텍스트로 사용하는
${item} 등을 사용하였다.

PIVOT 처럼 행을 열로 변경하기 위해
SizeList 를 foreach 문으로 반복하면서
CASE 문을 사용하면 PIVOT 과 동일한 형태의 결과를 얻을 수 있다.

나는 내가 사용할 컬럼리스트를 만든다는 관점에서 PIVOT이 더 직관적으로 느껴졌으나,
확실히 동적쿼리를 사용하니
유지보수와 변수처리에서 한계가 많이 느껴졌다
ex) 조회 컬럼이 늘어나거나, 사이즈값에서 예외케이스가 발생하거나, 조회조건을 추가할 때
MSSQL에서는 어떤 부분에서 에러가 났는지 정확히 파악이 어려웠다,,,,

처음에 사이즈가 단순히 신발 사이즈처럼
220, 225, 230~ 이렇게 있다고 생각했는데
중간에 발생했던 특수문자나
해외 사이즈인 소수점 사이즈(7.5, 8, 9.5 ~)
에서는 DECLARE, EXEC를 활용한 쿼리는
에러 처리로직이 많이 필요하기때문에
결국 방법2로 변경하게 되었다
물론 방법2에서도 소수점 이슈가 발생해서 ㅎ
위 코드블록에서는 생략했지만
Java단에서 sizeList를 넘길때
REPLACE(사이즈값, ‘.’, ‘@‘) 처럼 문자를 치환해서 넘겨야하고
방법2쿼리에서도  REPLACE(사이즈값, ‘.’, ‘@‘) 처리하여 비교하는 로직이 필요했지만
일단 행과 열을 변형하는 포스팅이라 생략했다