[MSSQL] 써보면 편리한 Split 함수 세 개

 

프로그래밍 언어에는 문자열 관련 함수가 많이 존재하지요. 그만큼 많이 쓰이기도 합니다.

언어마다 약간의 차이는 있지만 대표적인 함수들은 함수명과 매개변수까지 똑같지요.
MS-SQL에도 마찬가지로 기본적으로 문자열 함수가 지원됩니다.


Right, Left, SubString(Mid), Trim, Replace, CharIndex(InStr) ...


그런데 자주 쓰게 되는 문자열함수인 Split 만은 찾아볼 수가 없군요. 저는 Split 을 좋아라하는데..
언어에서 Split 함수는 어떤 문자열을 특정 구분자로 분리해서 배열변수에 담아주는 역할을 합니다.
그런데 MS-SQL에서는 '배열변수' 개념이 없기 때문에 배열변수 대신 테이블변수로 대체해 사용하게 되죠.


사정이 이렇다보니 SQL에서 문자열 Split을 한다면 리턴되는 결과값 변수로 나올수가 없고 테이블이 되어 나올 수밖에 없겠지요.
다른 문자열 함수들처럼 SELECT문에 그냥 갖다쓸수가 없게 됩니다. 그래서 없는가 봅니다.
 
그렇지만 Split은 없으면 아쉬운 편리한 함수 중 하나입니다.
그래서 사용자정의 함수를 만들어 보았습니다. 우리가 일반적으로 흔히 사용하는(알고있는) Split함수의 용법과 다르지 않습니다.
 


 

1. 문자열을 특정 구분자로 단순히 쪼갠다 : FN_SPLIT

    SELECT * FROM FN_SPLIT(@문자열,@구분자)

 

CREATE FUNCTION [dbo].[FN_SPLIT]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(10)
)
RETURNS @TB TABLE
( 
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(200)
)
AS
/*
- '' 값도 반환한다
- 마지막은 분리자로 끝낸다
SELECT * FROM  dbo.[FN_SPLIT]('^1^^333^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999','^') A
SELECT * FROM  dbo.[FN_SPLIT]('1^^22^^^^^^444444','^^') A
*/
BEGIN
 DECLARE
  @시작위치 SMALLINT
 ,@마지막위치 SMALLINT
 ,@카운터 SMALLINT
 ,@분리자크기 SMALLINT
 SELECT @분리자크기 = LEN(@분리자)

 IF RIGHT(@리스트,@분리자크기)!=@분리자
 BEGIN
  SET @리스트=@리스트+@분리자
 END
 SET @리스트=@분리자+@리스트
 
 SET @시작위치 = 1
 SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+@분리자크기)
 SET @카운터 = 0
 WHILE (1=1)
 BEGIN
  SET @시작위치 = CHARINDEX (@분리자,@리스트 )
  SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+@분리자크기)
        IF @마지막위치 <= 0 BREAK
  INSERT INTO @TB(VAL1) VALUES (SUBSTRING(@리스트,@시작위치+@분리자크기,@마지막위치-@시작위치-@분리자크기))
  SELECT @리스트 = STUFF(@리스트,@시작위치,@분리자크기,'')
  SET @카운터 = @카운터 + 1
 END
 RETURN
END

 

사용 예시

 SELECT * FROM FN_SPLIT('1^2^3^^5^444444','^')

 결과

   

 

테이블형 반환 함수이므로 SELECT * FROM 절에  하나의 테이블처럼 사용하면 됩니다.

클라이언트단에서 특정 순서를 가지고 데이터를 문자열로 이어붙여서 파라메터로 넘기고,
이걸 서버단에서 Split으로 쪼개서 사용할 수 있습니다.


물론 2단 이상 트리구조를 가진 데이터라면 XML로 주고받고 하는게 더 좋겠죠.
하지만 간단한 부분을 처리함에 있어서는 상당히 유용함이 분명합니다.

다만, 성능이 그리 뛰어난 함수는 아니므로 수천개 이상 되는 문자열이라면 이 FN_SPLIT함수 외에 다른방법을 사용하는 것을 추천드립니다.
사실 요즘 서버 성능이 워낙 좋아져서,, 이런 함수 좀 돌린다고 해서 무리가 가거나 부하가 많이 걸릴 일은 별로 없습니다만.. ㅎㅎ

 

 

 

 

2. 문자열을 SPLIT한 후 그룹핑하자 #1:  FN_SPLIT_GRP

   SELECT * FROM FN_SPLIT_GRP(@문자열,@구분자,@그룹수)


다음은 자주사용되지는 않지만 Split많으로 뭔가 살짝 아쉬울 때 사용할 수 있는 함수입니다.
예제를 보시는게 가장 이해가 쉬울 것 같습니다.

 

CREATE  FUNCTION [dbo].[FN_SPLIT_GRP]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(1)
,@그룹수 SMALLINT
)

RETURNS @TB TABLE
( 
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(500) default '',
VAL2 varchar(500) default '',
VAL3 varchar(500) default '',
VAL4 varchar(500) default '',
VAL5 varchar(500) default '',
VAL6 varchar(500) default '',
VAL7 varchar(500) default '',
VAL8 varchar(500) default '',
VAL9 varchar(500) default ''
)
AS
/*
1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^:한 요소의 집합들로 이루어짐( GROUP형)
====>
1^111^
2^222^
3^333^
4^444^

마지막은 항상 분리자
SELECT * FROM  dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^','^',1) A
SELECT * FROM  dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^A^AA^AAA^AAAA^AAAAA^AAAAAA^AAAAAAA^AAAAAAAA^AAAAAAAAA^','^',2) A
SELECT * FROM  dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^1111^2222^3333^4444^5555^6666^7777^8888^9999^','^',3) A
*/
BEGIN


	DECLARE @RETURNSTR VARCHAR(MAX)
	

	DECLARE @TEM_STR1 varchar(500)
	DECLARE @TEM_STR2 varchar(500)
	DECLARE @TEM_STR3 varchar(500)
	DECLARE @TEM_STR4 varchar(500)
	DECLARE @TEM_STR5 varchar(500)
	DECLARE @TEM_STR6 varchar(500)
	DECLARE @TEM_STR7 varchar(500)
	DECLARE @TEM_STR8 varchar(500)
	DECLARE @TEM_STR9 varchar(500)

	SET @TEM_STR1=''
	SET @TEM_STR2=''
	SET @TEM_STR3=''
	SET @TEM_STR4=''
	SET @TEM_STR5=''
	SET @TEM_STR6=''
	SET @TEM_STR7=''
	SET @TEM_STR8=''
	SET @TEM_STR9=''

	---- 마지막 분리자가 없으면 분리자를 추가한다
	IF RIGHT(@리스트,1)!=@분리자
	BEGIN
		SET @리스트=@리스트+@분리자
	END


	DECLARE
	 @시작위치 SMALLINT
	,@마지막위치 SMALLINT
	,@카운터 SMALLINT

	SET @시작위치 = 0
	SET @마지막위치 = -1
	SET @카운터 = 0
	
	IF (@그룹수=1)
	BEGIN
	    WHILE  CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
	    BEGIN

			    SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)
    	
			    SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
		        INSERT INTO @TB(VAL1) VALUES (@TEM_STR1)

			    SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)

			    SET @카운터 = @카운터+1

	    END
	END




	IF (@그룹수>1)
	BEGIN
/*
@TT에 일단 모든 데이터를 입력한 후
===> 그룹별로 한 컬럼씩 순서대로 배치한다
*/

        --테이블 변수 선언
        Declare @TT table
        (
        POS int IDENTITY (1,1) PRIMARY KEY,
        VAL1 varchar(500)
        )

        Declare @TTL_ARRAYNUM INT
        Declare @GROUP_ARRAYNUM INT

	    WHILE  CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
	    BEGIN

			    SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)

			    SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
		        INSERT INTO @TT(VAL1) VALUES (@TEM_STR1)

			    SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)

			    SET @카운터 = @카운터+1
	    END

        SELECT @TTL_ARRAYNUM=COUNT(*) FROM @TT
        SET @GROUP_ARRAYNUM=@TTL_ARRAYNUM/@그룹수


        Declare @CURR_NUM INT

        IF (@그룹수>=2)
	    BEGIN
	    /*
	    일단 그룹갯수 만큼 VAL1 컬럼 데이터 입력
	    */
	    
			INSERT INTO @TB(VAL1) 
			SELECT VAL1 
			FROM @TT A
			WHERE
            A.POS <= (@GROUP_ARRAYNUM)*1



			SET @CURR_NUM=2
			UPDATE AA SET VAL2=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END







        IF (@그룹수>=3)
	    BEGIN
	    /*
	    VAL3 컬럼 데이터 업뎃
	    */

			SET @CURR_NUM=3
			UPDATE AA SET VAL3=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=4)
	    BEGIN
	    /*
	    VAL4 컬럼 데이터 업뎃
	    */

			SET @CURR_NUM=4
			UPDATE AA SET VAL4=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=5)
	    BEGIN

			SET @CURR_NUM=5
			UPDATE AA SET VAL5=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=6)
	    BEGIN

			SET @CURR_NUM=6
			UPDATE AA SET VAL6=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=7)
	    BEGIN

			SET @CURR_NUM=7
			UPDATE AA SET VAL7=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=8)
	    BEGIN

			SET @CURR_NUM=8
			UPDATE AA SET VAL8=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


        IF (@그룹수>=9)
	    BEGIN

			SET @CURR_NUM=9
			UPDATE AA SET VAL9=A.VAL1 
            FROM (
					SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1 
					FROM @TT A
					WHERE
						A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
						AND
						A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
					) A,@TB AA
            WHERE
            A.POS =AA.POS

	    END


	END



	RETURN

END

 

사용 예시

SELECT * FROM FN_SPLIT_GRP
('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999
^1111^2222^3333^4444^5555^6666 ^7777^8888^9999^','^',3)

결과

 

 

FN_SPLIT_GRP 에는 매개변수가 하나 더 추가됩니다.

 

‘그룹수’  라는 매개변수로, 1~9까지 쓸 수 있습니다.(그 이상이 필요하다면 다른 방법을 강구하세요.. ㅎㅎ)


 

‘그룹수’가 들어감으로 인해서 이 함수는 최대 9 x n 형태의 테이블을 반환할 수 있게 됩니다.
위 예시에서 보시다시피 문자열은 다음과 같은 3 그룹으로 나눌 수 있습니다.
1^2^3^4^5^6^7^8^9^
111^222^333^444^555^666^777^888^999^
1111^2222^3333^4444^5555^6666^7777^8888^9999^

 

그리고 이것을 각각 SPLIT 해서 하나의 열(Column)로 추가해 주는 것이지요.


물론 성능을 고려하고 만들어진 함수는 아닙니다. 어디에 응용하는가는 각자에게 달려 있음을…ㅋㅋ

 

 

 

3. 문자열을 SPLIT한 후 그룹핑하자 #2: FN_SPLIT_SET

SELECT * FROM FN_SPLIT_SET(@문자열,@구분자,@그룹수)

 

이 함수 역시 문자열 단순 쪼개기에서 한단계 더 나아갑니다.
개인적으로는 FN_SPLIT FN_SPLIT_SET을 가장 많이 사용하게 되더군요.
이것도 역시 사용예시를 보시면 이해가 되실 것 같네요.

 

CREATE  FUNCTION [dbo].[FN_SPLIT_SET]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(1)
,@그룹수 SMALLINT
)

RETURNS @TB TABLE
( 
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(400),
VAL2 varchar(400),
VAL3 varchar(400),
VAL4 varchar(400),
VAL5 varchar(400),
VAL6 varchar(400),
VAL7 varchar(400),
VAL8 varchar(400),
VAL9 varchar(400)
)
AS
/*
1^111^2^222^3^333^4^444^:앞에서 부터 완결된 셋( SET형)
====> 
1^111^
2^222^
3^333^
4^444^

마지막은 항상 분리자

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',1) A

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^AAA^2^BBB^3^CCC^4^DDD^5^EEE^6^FFF^7^GGG^8^HHH^9^III^','^',2) A

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',3) A

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',4) A

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',4) A

SELECT * FROM  dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',5) A

*/
BEGIN


	DECLARE @RETURNSTR VARCHAR(400)
	

	DECLARE @TEM_STR1 varchar(400)
	DECLARE @TEM_STR2 varchar(400)
	DECLARE @TEM_STR3 varchar(400)
	DECLARE @TEM_STR4 varchar(400)
	DECLARE @TEM_STR5 varchar(400)
	DECLARE @TEM_STR6 varchar(400)
	DECLARE @TEM_STR7 varchar(400)
	DECLARE @TEM_STR8 varchar(400)
	DECLARE @TEM_STR9 varchar(400)

	SET @TEM_STR1=''
	SET @TEM_STR2=''
	SET @TEM_STR3=''
	SET @TEM_STR4=''
	SET @TEM_STR5=''
	SET @TEM_STR6=''
	SET @TEM_STR7=''
	SET @TEM_STR8=''
	SET @TEM_STR9=''

	---- 마지막 분리자가 없으면 분리자를 추가한다
	IF RIGHT(@리스트,1)!=@분리자
	BEGIN
		SET @리스트=@리스트+@분리자
	END


	DECLARE
	 @시작위치 INT
	,@마지막위치 INT
	,@카운터 INT

	SET @시작위치 = 0
	SET @마지막위치 = -1
	SET @카운터 = 0
	

	WHILE  CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
	BEGIN

			SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)
	
			IF (@카운터 % @그룹수=0)
			BEGIN
				SET @TEM_STR1=''
				SET @TEM_STR2=''
				SET @TEM_STR3=''
				SET @TEM_STR4=''
				SET @TEM_STR5=''
				SET @TEM_STR6=''
				SET @TEM_STR7=''
				SET @TEM_STR8=''
				SET @TEM_STR9=''

			END

			IF (@카운터 % @그룹수=0)
			BEGIN
				SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=1)
			BEGIN
				SELECT @TEM_STR2=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
			END

			IF (@카운터 % @그룹수=2)
			BEGIN
				SELECT @TEM_STR3=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
			END

			IF (@카운터 % @그룹수=3)
			BEGIN
				SELECT @TEM_STR4=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=4)
			BEGIN
				SELECT @TEM_STR5=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=5)
			BEGIN
				SELECT @TEM_STR6=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=6)
			BEGIN
				SELECT @TEM_STR7=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=7)
			BEGIN
				SELECT @TEM_STR8=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF (@카운터 % @그룹수=8)
			BEGIN
				SELECT @TEM_STR9=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)

			END

			IF ((@카운터 % @그룹수)=@그룹수-1)
			BEGIN
			    INSERT INTO @TB(VAL1,VAL2,VAL3,VAL4,VAL5,VAL6,VAL7,VAL8,VAL9) VALUES (@TEM_STR1,@TEM_STR2,@TEM_STR3,@TEM_STR4,@TEM_STR5,@TEM_STR6,@TEM_STR7,@TEM_STR8,@TEM_STR9)

			END

			SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)

			SET @카운터 = @카운터+1

	END

	RETURN

END

 

 

사용 예시

SELECT * FROM FN_SPLIT_SET
('1^AAA^2^BBB^3^CCC^4^DDD^5^EEE^6^FFF^7^GGG^8^HHH^9^III^','^',2)

결과

 


FN_SPLIT_GRP와 마찬가지로 ‘그룹수’ 매개변수가 추가되고, 역시 1~9까지만 사용 가능합니다.
문자열과 결과를 보시면 쉽게 이해가 가시리라 사료됩니다.


웹이던 어플리케이션이던,, 9컬럼 이내의 테이블 형태를 DB에 고스란히 집어 넣으려 할때에 사용하면 매우 편리하죠.

 

 

예를 하나 들어볼께요.

 

 

이런 모양의 데이터를 DB에 고대~~로 집어넣을 일이 있을 때, SPLIT_SETSPLIT_GRP 를 각각 이용한다면

 

FN_SPLIT_GRP

 

SELECT * FROM FN_SPLIT_GRP('언어,수리,외국어,사회탐구,사회탐구,사회탐구,언어,수리나,외국어,한국지리,경제지리,세계사,95,90,85,45,40,35,123,120,100,60,55,50,95,91,80,80,70,50,1,2,3,2,4,6',',',6)

 


 

FN_SPLIT_SET


SELECT * FROM FN_SPLIT_SET('언어,언어,95,123,95,1,수리,수리나,90,120,91,2,외국어,외국어,85,100,80,3,사회탐구,한국지리,45,60,80,2,사회탐구,경제지리,40,55,70,4,사회탐구,세계사,35,50,50,6',',',6)

 

둘 다 결과값은 동일하지만 문자열을 이어 붙이는 순서가 다릅니다.

GRPSET 둘 중 어느걸 써도 상관이 없다는 말이죠.


테이블에 바로 인서트 하려면 이렇게 하면 되겠죠.

 

INSERT INTO TBL
SELECT
VAL1 AS 교과
,VAL2 AS 과목
,VAL3 AS 원점수
,VAL4 AS 표준점수
,VAL5 AS 백분위
,VAL6 AS 등급
FROM FN_SPLIT_SET('언어,언어,95,123,95,1,수리,수리나,90,120,91,2,외국어,외국어,85,100,80,3,사회탐구,한국지리,45,60,80,2,사회탐구,경제지리,40,55,70,4,사회탐구,세계사,35,50,50,6',',',6)
ORDER BY POS

 

실제로는 점수테이블을 저런식으로 넣진 않겠지만 예를들기 위해서 써봤습니다.
언제나 응용은 하기 나름이지요..ㅎ

잘 사용하시길...

김정선님의 쿼리 튜닝 글을 보고  몇가지 실제사례에 넣어본 결과.

자료 : 5천ROW정도의 문제테이블 (MQ_Q_INFO)
         300ROW정도의 유형테이블_카테고리  (MQ_Q_TYPE)
         600ROW정도의  기존출제문제 히스토리테이블 (MQ_Q_HST)
         200ROW정도의 회원테이블(MQ_MEM)
        ※ FN_SPLIT은 문자열을 분리해서 POS값과 VALUE값을 테이블로 반환해주는 사용자함수.

액션 : 문제테이블에서 이번에 출제할 대상문항을 여러 조건에 의해 추려내야한다.
조건1. 정해진 유형에 소속된 문항만 추출. (20010^20210^ .... 문자열을 SPLIT 처리)
          유형은 10개를 선택할수도 있고 100개를 선택할수도 있다..
조건2. 과거 출제경력을 조회해서 최근 5회차에 출제된 문제를 배제시킨다.(TIMES로 관리됨)
조건3. 조건2와 더불어서  기존에 3회이상 출제됐었던 문제를 배제시킨다. (SETTIMES로 누적관리됨)

조건1(선택유형 내에서만 뽑는부분)은 걍 SPLIT해서 해당키값을 유형테이블에서 Q_TYPE_CD로 바꿔온다음 문제테이블에서 쪼인해버리는걸로 처리했다.
이렇게 해서 뽑았을때 5천ROW중 708문제가 대상문제로 추출됨.

조건2와 3은 UNION으로 묶고 DISTINCT해준다음 (90개문항이 배제대상문항으로 나옴)
그걸  김정선님의 차집합 스킬로 처리했다.. 최종 산출 row는 618개.

한번에 만들어진게 아니고 계속 조립식으로 만들다보니 너저분해지긴 했지만..
요점은  김정선님의 차집합 추천스킬의 결과를 보는게 목적이므로......


참고블로그 : http://blog.naver.com/visualdb/50028645471
여기서 3,4,5번만 가지고 테스트.


3. NOT IN + 상관서브쿼리+ TOP 1  (평가 : (Very) Good)

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND TPB1.Q_NUM NOT IN (SELECT TOP 1 TPC1.Q_NUM FROM
       (
        SELECT DISTINCT(YY.Q_NUM) FROM 
         (
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND SETTIMES > 3
          UNION
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
         ) YY
         ) TPC1
         WHERE TPC1.Q_NUM = TPB1.Q_NUM
         )
총실행시간
시도횟수 1 : 2220 ms
시도횟수 2~10 :  1087 ~ 1165  (평균 1097)



4. NOT EXISTS + 상관서브쿼리+ TOP 1 (평가 : Not bad )

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND NOT EXISTS (SELECT TOP 1 TPC1.Q_NUM FROM
       (
        SELECT DISTINCT(YY.Q_NUM) FROM 
         (
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND SETTIMES > 3
          UNION
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
         ) YY
         ) TPC1
         WHERE TPC1.Q_NUM = TPB1.Q_NUM
         )


총실행시간
시도횟수 1 : 145 ms
시도횟수 2~10 : 17ms ~ 60ms (평균 57.7ms)

 엄청빠르다 +_+

 

 

5. 상관 서브쿼리+ TOP 1 + IS NULL (평가 : 김정선의 서브쿼리 튜닝 필살기 (Very) Good)

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND (SELECT TOP 1 TPC1.Q_NUM FROM
  (
   SELECT DISTINCT(YY.Q_NUM) FROM 
    (
     SELECT Q_NUM FROM MQ_Q_HST
     WHERE STU_ID = '8701290008'
     AND SETTIMES > 3
     UNION
     SELECT Q_NUM FROM MQ_Q_HST
     WHERE STU_ID = '8701290008'
     AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
    ) YY
    ) TPC1
    WHERE TPC1.Q_NUM = TPB1.Q_NUM
    ) IS NULL

총실행시간
시도횟수 1 : 202 ms
시도횟수 2~10 : 175ms ~ 217ms (평균 186.5ms)









결론 : 다른부분은 전혀 튜닝도 안되고 구조도 제멋대로인 부분이지만   차집합구하기 부분만 놓고 봤을때는 어떤 형태의 쿼리에 얹히느냐에 따라  성능의 우위가 바뀔수도 있음을 확인할수 있다.

똑같은작업을 할때 NOT IN이냐 NOT EXISTS냐에 따라 20배에 가까운 속도를 단축할수 있다는데에 새삼 놀랍다....



쓸데없는 DISTINCT를 뺏더니 57.7ms에서 36ms로  또 급 단축되네..ㅎㅎㅎ

+ Recent posts