#title 점을 선분으로 [[TableOfContents]] 세상 많이 좋아졌다. DB분야에서는 이제까지 SQL로 어케 잘 버텨왔다. 하지만 요즘은 좋은 툴들이 많이 나왔다. 특히나 '행의 무순서'를 지향하는 관계형 DB에서는 앞/뒤 Row에 접근하거나 값 자체가 앞/뒤 Row에 종속적인 경우가 된다면 머리가 살짝 아펐다. 어쨌든 점을 선분으로 바꾸는 SQL은 찾아보면 많이 있다. 여기에서는 SSIS 파이프라이닝의 파워를 느껴보도록 하자. 뭐.. 대략 초당 10만건 정도? ==== 테스트 데이터 ==== {{{ uselect * into dbo.이재학 from ( select '00434' id, 66 productkey, '2009-04-28' exe_dt union all select '00434' id, 66 productkey, '2009-04-29' exe_dt union all select '00434' id, 66 productkey, '2009-04-30' exe_dt union all select '00434' id, 66 productkey, '2009-05-01' exe_dt union all select '00434' id, 66 productkey, '2009-05-02' exe_dt union all select '00434' id, 66 productkey, '2009-05-03' exe_dt union all select '00434' id, 66 productkey, '2009-05-05' exe_dt union all select '00434' id, 66 productkey, '2009-05-06' exe_dt union all select '00434' id, 66 productkey, '2009-05-07' exe_dt union all select '00434' id, 66 productkey, '2009-05-08' exe_dt union all select '00434' id, 66 productkey, '2009-05-09' exe_dt union all select '00434' id, 66 productkey, '2009-05-10' exe_dt union all select '00434' id, 66 productkey, '2009-05-11' exe_dt union all select '00434' id, 66 productkey, '2009-05-12' exe_dt union all select '00434' id, 66 productkey, '2009-05-13' exe_dt union all select '00434' id, 66 productkey, '2009-05-14' exe_dt union all select '00434' id, 66 productkey, '2009-05-15' exe_dt union all select '00434' id, 66 productkey, '2009-05-16' exe_dt union all select '00434' id, 66 productkey, '2009-05-17' exe_dt union all select '00434' id, 66 productkey, '2009-05-18' exe_dt union all select '00434' id, 109 productkey, '2009-05-19' exe_dt union all select '00434' id, 109 productkey, '2009-05-20' exe_dt union all select '00434' id, 109 productkey, '2009-05-21' exe_dt union all select '00434' id, 109 productkey, '2009-05-22' exe_dt union all select '00434' id, 109 productkey, '2009-05-23' exe_dt union all select '00434' id, 109 productkey, '2009-05-24' exe_dt union all select '00434' id, 109 productkey, '2009-05-25' exe_dt union all select '00434' id, 66 productkey, '2009-05-26' exe_dt union all select '00434' id, 66 productkey, '2009-05-27' exe_dt union all select '00434' id, 66 productkey, '2009-05-28' exe_dt union all select '00434' id, 66 productkey, '2009-05-29' exe_dt union all select '00434' id, 66 productkey, '2009-05-30' exe_dt union all select '00434' id, 66 productkey, '2009-05-31' exe_dt union all select '01009' id, 109 productkey, '2009-04-28' exe_dt union all select '01009' id, 109 productkey, '2009-04-29' exe_dt union all select '01009' id, 109 productkey, '2009-04-30' exe_dt union all select '01009' id, 109 productkey, '2009-05-01' exe_dt union all select '01519' id, 113 productkey, '2009-05-25' exe_dt union all select '01519' id, 113 productkey, '2009-05-26' exe_dt union all select '01519' id, 113 productkey, '2009-05-27' exe_dt union all select '01519' id, 113 productkey, '2009-05-28' exe_dt union all select '01519' id, 113 productkey, '2009-05-29' exe_dt union all select '01519' id, 113 productkey, '2009-05-30' exe_dt union all select '01519' id, 113 productkey, '2009-05-31' exe_dt union all select '01519' id, 113 productkey, '2009-06-01' exe_dt union all select '01519' id, 113 productkey, '2009-06-02' exe_dt union all select '01519' id, 113 productkey, '2009-06-03' exe_dt union all select '01519' id, 113 productkey, '2009-06-04' exe_dt union all select '01519' id, 113 productkey, '2009-06-05' exe_dt union all select '01519' id, 113 productkey, '2009-06-06' exe_dt union all select '01519' id, 113 productkey, '2009-06-07' exe_dt union all select '01519' id, 113 productkey, '2009-06-08' exe_dt union all select '01519' id, 113 productkey, '2009-06-09' exe_dt union all select '01519' id, 113 productkey, '2009-06-10' exe_dt union all select '02811' id, 109 productkey, '2009-04-28' exe_dt union all select '02811' id, 109 productkey, '2009-04-29' exe_dt union all select '02811' id, 109 productkey, '2009-04-30' exe_dt union all select '02811' id, 109 productkey, '2009-05-01' exe_dt union all select '02811' id, 109 productkey, '2009-05-02' exe_dt union all select '02811' id, 109 productkey, '2009-05-03' exe_dt union all select '02811' id, 109 productkey, '2009-05-04' exe_dt union all select '02811' id, 109 productkey, '2009-05-05' exe_dt union all select '02811' id, 109 productkey, '2009-05-06' exe_dt union all select '02811' id, 109 productkey, '2009-05-07' exe_dt union all select '02811' id, 109 productkey, '2009-05-08' exe_dt union all select '02811' id, 109 productkey, '2009-05-09' exe_dt union all select '02811' id, 109 productkey, '2009-05-10' exe_dt union all select '02811' id, 109 productkey, '2009-05-11' exe_dt union all select '02811' id, 109 productkey, '2009-05-12' exe_dt union all select '02811' id, 109 productkey, '2009-05-13' exe_dt union all select '02811' id, 109 productkey, '2009-05-14' exe_dt union all select '02811' id, 109 productkey, '2009-05-15' exe_dt union all select '02811' id, 109 productkey, '2009-05-16' exe_dt union all select '02811' id, 109 productkey, '2009-05-17' exe_dt union all select '02811' id, 109 productkey, '2009-05-18' exe_dt union all select '02811' id, 109 productkey, '2009-05-19' exe_dt union all select '02811' id, 109 productkey, '2009-05-20' exe_dt union all select '02811' id, 109 productkey, '2009-05-21' exe_dt union all select '02811' id, 109 productkey, '2009-05-22' exe_dt union all select '02811' id, 109 productkey, '2009-05-23' exe_dt union all select '02811' id, 109 productkey, '2009-05-24' exe_dt union all select '02811' id, 109 productkey, '2009-05-25' exe_dt union all select '02811' id, 109 productkey, '2009-05-26' exe_dt union all select '02811' id, 109 productkey, '2009-05-27' exe_dt union all select '02811' id, 109 productkey, '2009-05-28' exe_dt union all select '02811' id, 109 productkey, '2009-05-29' exe_dt union all select '02811' id, 109 productkey, '2009-05-30' exe_dt union all select '02811' id, 109 productkey, '2009-05-31' exe_dt union all select '02811' id, 109 productkey, '2009-06-01' exe_dt union all select '02811' id, 109 productkey, '2009-06-02' exe_dt union all select '02811' id, 109 productkey, '2009-06-03' exe_dt union all select '02811' id, 109 productkey, '2009-06-04' exe_dt union all select '02811' id, 109 productkey, '2009-06-05' exe_dt union all select '02811' id, 109 productkey, '2009-06-06' exe_dt union all select '02811' id, 109 productkey, '2009-06-07' exe_dt union all select '02811' id, 109 productkey, '2009-06-08' exe_dt union all select '02811' id, 109 productkey, '2009-06-09' exe_dt union all select '02811' id, 109 productkey, '2009-06-10' exe_dt union all select '05583' id, 120 productkey, '2009-05-26' exe_dt ) t }}} ==== SSIS 꾸미기 ==== 아래와 같이 꾸밀 것이다. 어떻게 꾸몄는지는 [attachment:Package1.dtsx SSIS 패키지 파일]을 보도록 하자. attachment:ssis01.jpg 핵심은 이전 Row를 접근하는 것이고, 나머지는 점을 선분으로 바꾸는 것이다. 아래는 이전 Row를 접근하는 소스이다. {{{#!geshi vbnet ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Dim CurrDT As String Dim NextDT As String Dim MinDT As String Dim MaxDT As String Dim Seq As Int64 Dim RowNum As Int64 Dim id As String Dim Oldid As String Dim PKey As Int16 Dim OldPKey As Int16 Public Sub New() CurrDT = "" NextDT = "" id = "" Seq = 0 RowNum = 0 Oldid = "" End Sub Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer) ' ' Add your code here NextDT = Row.exedt With ResultBuffer .AddRow() .id = Row.id .ProductKey = CShort(Row.productkey) .BeginDT = CurrDT .EndDT = NextDT .Oldid = Oldid .OldProductKey = OldPKey End With OldPKey = CShort(Row.productkey) Oldid = Row.id CurrDT = NextDT End Sub End Class }}} 두 번째 스크립트 구성요소인 점을 선분으로 바꾸는 소스다. {{{#!geshi vbnet ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Dim Seq As Int64 Dim MinDT As String Dim MaxDT As String Dim AddRowYN As Boolean Dim NM As String '에이.. ㅡㅡ;; 변수이름 id로 바꿔야 하는데.. 귀찮다.. 그냥 그렇다 치자.. Dim PKey As Int16 Dim Diff As Long Public Sub New() MinDT = "" MaxDT = "" Seq = 0 AddRowYN = False End Sub Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer) ' ' Add your code here ' Diff = DateDiff(DateInterval.Day, Row.BeginDTdate, Row.EndDTdate) If Row.id = Row.Oldid And Row.ProductKey = Row.OldProductKey And Diff = 1 Then If Seq = 0 Then MinDT = Row.BeginDT End If MaxDT = Row.EndDT NM = Row.id PKey = Row.ProductKey Seq = Seq + 1 Else With ResultBuffer .AddRow() .id = NM .ProductKey = PKey .BeginDT = MinDT .EndDT = MaxDT End With Seq = 0 End If End Sub End Class }}} ==== SQL로 간단히 푸는 예제1 ==== http://sqler.pe.kr/web_board/view_list.asp?id=91080&read=0&pagec=1&gotopage=1&block=0&part=myboard1&tip= MVP이신 최석준 형님께서 아주 친절하게 답변을 달아주셨다. (참.. 귀찮지도 않은 가벼~) {{{ CREATE TABLE test(type INT,serial INT) INSERT test VALUES(1,1) INSERT test VALUES(1,2) INSERT test VALUES(1,3) INSERT test VALUES(2,4) INSERT test VALUES(2,5) INSERT test VALUES(1,6) INSERT test VALUES(1,7) INSERT test VALUES(2,8) INSERT test VALUES(1,9) -- MSSQL 2005 Higher ;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY serial) d FROM test ) SELECT type type,MIN(serial) [from],MAX(serial) [to],COUNT(*) [count] FROM CTE GROUP BY type,serial-d /* type from to count ----------- ----------- ----------- ----------- 1 1 3 3 1 6 7 2 1 9 9 1 2 4 5 2 2 8 8 1 (5개 행이 영향을 받음) */ -- MSSQL 2000 SELECT type type,MIN(serial) [from],MAX(serial) [to],COUNT(*) [count] FROM (SELECT *,(SELECT COUNT(*) FROM test b WHERE b.type = a.type AND b.serial <= a.serial) d FROM test a) t GROUP BY type,serial-d /* type from to count ----------- ----------- ----------- ----------- 1 1 3 3 1 6 7 2 1 9 9 1 2 4 5 2 2 8 8 1 (5개 행이 영향을 받음) */ }}} ==== SQL로 간단히 푸는 예제2 ==== {{{ /* 데이터 Date code Rate 2009-12-01 AA 100 2009-12-02 AA 100 2009-12-03 100 2009-12-04 AA 100 2009-12-05 AA 100 2009-12-06 CC 200 2009-12-07 CC 200 2009-12-08 100 2009-12-09 200 2009-12-10 AA 200 원하는 결과 Date Code Rate 2009-12-01 ~2009-12-02 AA 100 2009-12-03 100 2009-12-04 ~2009-12-05 AA 100 2009-12-06 ~2009-12-07 CC 200 2009-12-08 100 2009-12-09 200 2009-12-10 AA 200 */ }}} * 질문 원본: http://www.sqler.com/bSQLQA/130402 풀이 {{{ --drop table #temp create table #temp ( date char(10) , code char(2) , rate int ) insert #temp values('2009-12-01', 'AA',100) insert #temp values('2009-12-02', 'AA',100) insert #temp values('2009-12-03', '',100) insert #temp values('2009-12-04', 'AA',100) insert #temp values('2009-12-05', 'AA',100) insert #temp values('2009-12-06', 'CC',200) insert #temp values('2009-12-07', 'CC',200) insert #temp values('2009-12-08', '',100) insert #temp values('2009-12-09', '',200) insert #temp values('2009-12-10', 'AA',200) select min(date) + case when max(date) = min(date) then '' else '~' + max(date) end date , max(code) code , max(rate) rate from ( select row_number() over(order by date) date_seq , row_number() over(order by code, date) code_seq --, row_number() over(partition by code order by date) code_seq -- 이렇게 해도 된다. , date , code , rate from #temp ) t group by case when code = '' then date end , code , date_seq - code_seq order by 1 --또다른 방식 select min(date) + case when max(date) = min(date) then '' else '~' + max(date) end date , min(code) , min(rate) from ( select row_number() over(order by code, date) seq , date , code , rate from #temp ) t group by case when code = '' then date end , dateadd(dd, -seq, date) , code order by 1 }}}