CLR 강좌 - GREATEST_LEAST 구현하기

GREATEST_LEAST.CS

GREATEST_LEAST.SQL

김종열

MS SQL Server MVP

엔트리브 소프트

CLR에서 몇 개의 함수를 만들어 보며 CLR을 느꼈으면 합니다.

이번에 해볼건 얼마전부터 만들어보고 싶었던 것인데요.

칼럼의 최대값과 최소값은 MAX, MIN이라는 함수를 이용해서 쉽게 알수 있습니다.

그런데 칼럼간의 최대,  최소를 알고 싶을 때는 어떻게 해야 할까요?

ORACLE에서는 GREATEST, LEAST라는 함수를 제공합니다. 문법도 아주 단순합니다.

GREATEST(A1, A2, A3, …., An) 이라고 하면 그 최대값을 리턴하게 됩니다.

ORACLE에서는 이 함수가 문자열과 수치형 모두 사용가능한 것으로 되어 있습니다.

LEAST라는 함수는 이와 반대로 최소값을 RETURN해주는 것인데요.

 

이번에 제가 만들고자 하는 함수는 GREATEST, LEAST 함수를 만들어보는 것이구요.

저는 단순히 입력은 STRING으로 받고 RETURN은 INT로 받는 걸로 해서 만들었구요.

그리고 실행계획을 비교해보려고 합니다.

 

1.     기존 프로젝트인 PRJ_CLR를 OPEN합니다.

2.     사용자 정의 함수를 추가시킵니다. (PROJECT에서 오른 마우스를 클릭 > ADD > NEW ITEM)

3.     NEW ITEM에서 사용자 정의 함수를 선택하고 이름을 “GREATEST_LEAST.CS”라고 입력

4.     여기까지 하고 아래와 같은 소스를 입력합니다. (소스는 첨부하였습니다.)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt32 GREATEST(string strNum)

    {

        // 여기에 코드를 입력합니다.

        string[] sNum;

        Int32 _maxNum = 0;

 

        try

        {

            sNum = strNum.Split(' ');

 

            for (int i = 0; i <= sNum.Length - 1; i++)

            {

                if (_maxNum < Convert.ToInt32(sNum[i]))

                {

                    _maxNum = Convert.ToInt32(sNum[i]);

                }

            }

 

            return (SqlInt32)_maxNum;

        }

        catch

        {

            return 0;

 

        }

 

    }

 

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt32 LEAST(string strNum)

    {

        // 여기에 코드를 입력합니다.

        string[] sNum;

        Int32 _minNum = 999999999;

 

        try

        {

            sNum = strNum.Split(' ');

 

            for (int i = 0; i <= sNum.Length - 1; i++)

            {

                if (_minNum > Convert.ToInt32(sNum[i]))

                {

                    _minNum = Convert.ToInt32(sNum[i]);

                }

            }

 

            return (SqlInt32)_minNum;

        }

        catch

        {

            return 0;

 

        }

 

    }

 

};

 

 

    간단히 함수에 대해 설명하면 GREATEST는 문자열을 받아서

구분자로 된 문자열을 SPLIT하여 배열로 만들고 MAXNUM의 초기값을 0으로 한 다음

순환을 하며 값을 비교하여 최대값을 리턴하게 했습니다.

5     Visual Studio의 컴파일 및 배포를 해줍니다.

 

6.     위의 쿼리에 대한 것을 검증하기 위한 T-SQL문을 만들고 CLR과 비교하겠습니다.

--준비용테이블

create table test(이름varchar(3),성적int,성적int,성적int,성적int,성적int)

GO

 

--TEST DATA 입력

insert test

        select 'AAA',52,78,34,0,43 union all

        select 'BBB',46,54,75,65,24 union all

        select 'CCC',78,86,45,56,12 union all

        select 'DDD',53,77,13,44,67 union all

        select 'EEE',34,24,63,23,83

GO

 

 

 

 

--T-SQL (MADE BY 최석준)

select row_number() over(order by 성적desc) 순위,*

  from (select 이름,

               max(case b.id when 1 then 성적

                             when 2 then 성적

                             when 3 then 성적

                             when 4 then 성적

                             when 5 then 성적end) 성적       

              from test a cross join

                  (select 1 id union all

                   select 2 union all

                   select 3 union all

                   select 4 union all

                   select 5) b

            group by 이름) t

 order by 성적desc

GO

 

--QUERY WITH CLR

select

        이름

        , 성적

        , 성적

        , 성적

        , 성적

        , 성적

        ,  MAX_SCORE = dbo.GREATEST(cast(성적as varchar) + ' ' +

               cast(성적as varchar) + ' ' +

               cast(성적as varchar) + ' ' +

               cast(성적as varchar) + ' ' +

               cast(성적as varchar) )

from test

order by max_score desc

GO

※     실행계획 비교 (HOT_KEY : CTRL + L, CTRL + K)

SET SHOWPLAN_TEXT ON

T-SQL (MADE BY 최석준)

 

/*

  |--Sequence Project(DEFINE:([Expr1010]=row_number))

       |--Compute Scalar(DEFINE:([Expr1013]=(1)))

            |--Segment

                 |--Sort(ORDER BY:([Expr1009] DESC))

                      |--Stream Aggregate(GROUP BY:([a].[이름]) DEFINE:([Expr1009]=MAX(CASE WHEN [Union1008]=(1) THEN [youlyDB].[dbo].[test].[성적] as [a].[성적] ELSE CASE WHEN [Union1008]=(2) THEN [youlyDB].[dbo].[test].[성적] as [a].[성적] ELSE CASE WHEN [Union1008]=(3) THEN [youlyDB].[dbo].[test].[성적] as [a].[성적] ELSE CASE WHEN [Union1008]=(4) THEN [youlyDB].[dbo].[test].[성적] as [a].[성적] ELSE CASE WHEN [Union1008]=(5) THEN [youlyDB].[dbo].[test].[성적] as [a].[성적] ELSE NULL END END END END END)))

                           |--Nested Loops(Inner Join)

                                |--Sort(ORDER BY:([a].[이름] ASC))

                                |    |--Table Scan(OBJECT:([youlyDB].[dbo].[test] AS [a]))

                                |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))

 

*/

 

QUERY WITH CLR

 

/*

  |--Sort(ORDER BY:([Expr1004] DESC))

       |--Compute Scalar(DEFINE:([Expr1004]=[youlyDB].[dbo].[GREATEST](CONVERT_IMPLICIT(nvarchar(4000),(((((((CONVERT(varchar(30),[youlyDB].[dbo].[test].[성적],0)+' ')+CONVERT(varchar(30),[youlyDB].[dbo].[test].[성적],0))+' ')+CONVERT(varchar(30),[youlyDB].[dbo].[test].[성적],0))+' ')+CONVERT(varchar(30),[youlyDB].[dbo].[test].[성적],0))+' ')+CONVERT(varchar(30),[youlyDB].[dbo].[test].[성적],0),0))))

            |--Table Scan(OBJECT:([youlyDB].[dbo].[test]))

*/

 

SET SHOWPLAN_TEXT OFF

※     SET SHOWPLAN_ALL ON / OFF를 이용해서 세부적인 사항도 한번 비교해보십시오.

도움파일 : GREATEST_LEAST.CS
 GREATEST_LEAST.SQL