_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
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

螻手碓螳 朱 覩碁螳 .