CLR-이전행가기 udf_lag

Udf_Lag.cs

udf_lag.sql

김종열

MS SQL Server MVP

 

정말 오랜만에 글을 올리네요. ^^

쿼리를 다루시다가 보면 이전행의 데이터를 가져오는 부분을 짜는 일이 있을 겁니다.
그 하나의 데이터를 가져오기 위해 다시 join을 사용하면서 이를 지원해주는 함수가 없을까 생각해봤을 겁니다. oracle에는 lag(), lead()라는 window Function을 제공해줍니다.

역시 이런 함수를 mssql에서 다룰 수 있도록 해달라는군요..
이런 메시지를 받으면 고민하고 시도를 해봐야 겠다는 맘이 생기지요..

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections.Generic;

 

public partial class UserDefinedFunctions

{

    public readonly static Dictionary<string, List<object>> _dicMemory = new Dictionary<string, List<object>>();

   

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString Udf_Lag(object objValue, int rowNo, int key, string guid)

    {

        //Return 할 변수 선언

        string retValue = string.Empty;

 

        //Guid가 존재 하지 않을 경우 새로운 시작

        if (_dicMemory.ContainsKey(guid) == false)

        {

            //Dictionary에 guid를 키로 하여 새로운 List를 생성 한다.

            _dicMemory.Add(guid, new List<object>());

        }

 

        //Guid에 따른 저장 된 List 값을 가져 온다.

        List<object> tempList = _dicMemory[guid];

 

        //현재 저장된 리스트의 개수

        int valueCount = tempList.Count;

 

        if (valueCount - rowNo -1  < 0)

        {

            //부모 값이 없는 경우

            retValue = string.Empty;

        }

        else

        {

            //부모의 값이 있는 경우 반환 값을 넘긴다.

            retValue = Convert.ToString(tempList[valueCount - rowNo - 1]);

        }

 

        tempList.Add(objValue);

 

        //key -> 1 시작

        //key -> 0 진행중

        //key -> -1 종료

        if (key == -1)

        {

            //종료일 경우 메모리에서 값을 삭제 한다.

            _dicMemory.Remove(guid);

        }

 

        return retValue;

    }

 

};

 

만들며 데이터가 들어온 순서를 메모리에 담기때문에 동시에 두곳에서 실행을 하면 메모리에서
중복이 일어나 제대로 된 결과를 얻을 수 없게 됩니다. 해서 newid를 이용해서 고유한 스티링을 이용해
메모리 충돌이 일어나지 않게 변수를 생성하여 그곳에 데이터를 담아 각자의 session에 대한
제대로 된 결과를 제공해주자는 것입니다.

물론 메모리에 들어갔으므로 실행을 마쳤다면 종료를 시켜줘야 하는데 그게 endbit가 필요한 이유입니다.

여튼 실행의 예시는 다음과 같습니다.

CREATE TABLE tempdb.dbo.t (a INT IDENTITY(1,1))

GO

 

 

INSERT INTO tempdb.dbo.t DEFAULT VALUES

GO 40000

 

 

 

DECLARE @endBit INT = 0

DECLARE @guid NVARCHAR(50) = NEWID()

 

SELECT @endBit = COUNT(*) FROM tempdb.dbo.t

 

/*

udf_lag(데이터열, 몇행앞으로(1, 2, 3.), 시작중간끝(1,0,-1), guid)

 

*/

SELECT

       a

       , dbo.udf_lag(a, 3, CASE ROW_NUMBER() OVER (ORDER BY a) 

                                              WHEN 1 THEN 1

                                              WHEN @endbit THEN -1

                                              ELSE 0 END , @guid)                    

FROM

       tempdb.dbo.t

첨부 : Udf_Lag.cs
        udf_lag.sql