_대문 | 방명록 | 최근글 | 홈피소개 | 주인놈 |
FrontPage › 점을선분으로
|
|
세상 많이 좋아졌다. 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 꾸미기 #핵심은 이전 Row를 접근하는 것이고, 나머지는 점을 선분으로 바꾸는 것이다. 아래는 이전 Row를 접근하는 소스이다.
* IE���� �ҽ� ����� �ٹٲ� �ȵ�. MS-Word � �ٿ� ��������. �ٹٲ� �Ǵ� ������: Chrome, Opera, Safari 두 번째 스크립트 구성요소인 점을 선분으로 바꾸는 소스다.
* 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
|
좋은 책을 읽는 것은 과거의 가장 뛰어난 사람들과 대화를 나누는 것과 같다. (데카르트) |