_대문 | 방명록 | 최근글 | 홈피소개 | 주인놈
FrontPage › 점을선분으로
Data Analysis(R)
Data Analysis(Python)
Statistics
Data Warehouse
Database System
SQL Server
Trino
Hive
Hadoop
Regular Expressions
Tools
Money
Working Smart
Blog
Booklog
Philosophy
Link
Miscellaneous
__ edit




Contents

[-]
1 테스트 데이터
2 SSIS 꾸미기
3 SQL로 간단히 푸는 예제1
4 SQL로 간단히 푸는 예제2


세상 많이 좋아졌다. DB분야에서는 이제까지 SQL로 어케 잘 버텨왔다. 하지만 요즘은 좋은 툴들이 많이 나왔다. 특히나 '행의 무순서'를 지향하는 관계형 DB에서는 앞/뒤 Row에 접근하거나 값 자체가 앞/뒤 Row에 종속적인 경우가 된다면 머리가 살짝 아펐다. 어쨌든 점을 선분으로 바꾸는 SQL은 찾아보면 많이 있다. 여기에서는 SSIS 파이프라이닝의 파워를 느껴보도록 하자. 뭐.. 대략 초당 10만건 정도?

[edit]

1 테스트 데이터 #

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

[edit]

2 SSIS 꾸미기 #

아래와 같이 꾸밀 것이다. 어떻게 꾸몄는지는 SSIS 패키지 파일을 보도록 하자.
ssis01.jpg

핵심은 이전 Row를 접근하는 것이고, 나머지는 점을 선분으로 바꾸는 것이다. 아래는 이전 Row를 접근하는 소스이다.
  1. ' Microsoft SQL Server Integration Services user script component
  2. ' This is your new script component in Microsoft Visual Basic .NET
  3. ' ScriptMain is the entrypoint class for script components
  4.  
  5. Imports System
  6. Imports System.Data
  7. Imports System.Math
  8. Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
  9. Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
  10.  
  11. Public Class ScriptMain
  12.     Inherits UserComponent
  13.  
  14.     Dim CurrDT As String
  15.     Dim NextDT As String
  16.     Dim MinDT As String
  17.     Dim MaxDT As String
  18.     Dim Seq As Int64
  19.     Dim RowNum As Int64
  20.     Dim id As String
  21.     Dim Oldid As String
  22.     Dim PKey As Int16
  23.     Dim OldPKey As Int16
  24.  
  25.  
  26.     Public Sub New()
  27.         CurrDT = ""
  28.         NextDT = ""
  29.         id = ""
  30.         Seq = 0
  31.         RowNum = 0
  32.         Oldid = ""
  33.     End Sub
  34.  
  35.     Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)
  36.         '
  37.         ' Add your code here
  38.         NextDT = Row.exedt
  39.  
  40.         With ResultBuffer
  41.             .AddRow()
  42.             .id = Row.id
  43.             .ProductKey = CShort(Row.productkey)
  44.             .BeginDT = CurrDT
  45.             .EndDT = NextDT
  46.             .Oldid = Oldid
  47.             .OldProductKey = OldPKey
  48.         End With
  49.  
  50.         OldPKey = CShort(Row.productkey)
  51.         Oldid = Row.id
  52.         CurrDT = NextDT
  53.  
  54.  
  55.     End Sub
  56.  
  57. End Class
  58.  
* IE���� �ҽ� ����� �ٹٲ� �ȵ�. MS-Word � �ٿ� ��������. �ٹٲ� �Ǵ� ������: Chrome, Opera, Safari

두 번째 스크립트 구성요소인 점을 선분으로 바꾸는 소스다.
  1. ' Microsoft SQL Server Integration Services user script component
  2. ' This is your new script component in Microsoft Visual Basic .NET
  3. ' ScriptMain is the entrypoint class for script components
  4.  
  5. Imports System
  6. Imports System.Data
  7. Imports System.Math
  8. Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
  9. Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
  10.  
  11. Public Class ScriptMain
  12.     Inherits UserComponent
  13.  
  14.     Dim Seq As Int64
  15.     Dim MinDT As String
  16.     Dim MaxDT As String
  17.     Dim AddRowYN As Boolean
  18.     Dim NM As String '에이.. ㅡㅡ;; 변수이름 id로 바꿔야 하는데.. 귀찮다.. 그냥 그렇다 치자..
  19.     Dim PKey As Int16
  20.     Dim Diff As Long
  21.  
  22.     Public Sub New()
  23.         MinDT = ""
  24.         MaxDT = ""
  25.         Seq = 0
  26.         AddRowYN = False
  27.     End Sub
  28.  
  29.     Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)
  30.         '
  31.         ' Add your code here
  32.         '
  33.         Diff = DateDiff(DateInterval.Day, Row.BeginDTdate, Row.EndDTdate)
  34.  
  35.         If Row.id = Row.Oldid And Row.ProductKey = Row.OldProductKey And Diff = 1 Then
  36.             If Seq = 0 Then
  37.                 MinDT = Row.BeginDT
  38.             End If
  39.             MaxDT = Row.EndDT
  40.             NM = Row.id
  41.             PKey = Row.ProductKey
  42.             Seq = Seq + 1
  43.         Else
  44.             With ResultBuffer
  45.                 .AddRow()
  46.                 .id = NM
  47.                 .ProductKey = PKey
  48.                 .BeginDT = MinDT
  49.                 .EndDT = MaxDT
  50.             End With
  51.             Seq = 0
  52.         End If
  53.  
  54.     End Sub
  55.  
  56. End Class
  57.  
* IE���� �ҽ� ����� �ٹٲ� �ȵ�. MS-Word � �ٿ� ��������. �ٹٲ� �Ǵ� ������: Chrome, Opera, Safari

[edit]

3 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개 행이 영향을 받음)
*/

[edit]

4 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

댓글 남기기..
이름: : 오른쪽의 새로고침을 클릭해 주세요. 새로고침
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

좋은 책을 읽는 것은 과거의 가장 뛰어난 사람들과 대화를 나누는 것과 같다. (데카르트)