#title 고립문제와 간격문제
[[TableOfContents]]

==== 기본 ====
예제데이터
{{{
create table #temp(num int)
insert #temp values
    (2),(3),(11),(12),(13),(31),(33),(34),(35),(42);

select * from #temp
/*
num
-----------
2
3
11
12
13
31
33
34
35
42
*/
}}}

고립문제의 원하는 결과
||begin_num||end_num||
||2||3||
||11||13||
||31||31||
||33||35||
||42||42||

간격문제의 원하는 결과
||begin_num||end_num||
||4||10||
||14||30||
||32||32||
||36||41||

고립문제
{{{
--고립문제
select
    min(num) begin_num
,   max(num) end_num
from (
    select
        num
    ,   row_number() over(order by num) - num grp
    from #temp 
) t
group by
    grp
order by 1

/*
begin_num   end_num
----------- -----------
2           3
11          13
31          31
33          35
42          42
*/
}}}

간격문제
{{{
--간격문제
;with temp
as
(
    select
        num
    ,   row_number() over(order by num) rownum
    from #temp 
)
select 
    a.num + 1 begin_num
,   b.num - 1 end_num
from temp a
    inner join temp b
        on a.rownum = b.rownum - 1
where b.num - a.num > 1

/*
begin_num   end_num
----------- -----------
4           10
14          30
32          32
36          41
*/
}}}

==== 예제1 ====
문제출처: http://www.sqler.com/476031#0

{{{
use tempdb
go

create table [dbo].[t](
 [rowseq_num] [bigint] null,
 [mynjuk] [decimal](25, 2) null,
 [dnum] [int] null
) 


insert t(rowseq_num,mynjuk,dnum) values('11','630.00','18')
insert t(rowseq_num,mynjuk,dnum) values('12','315.00','0')
insert t(rowseq_num,mynjuk,dnum) values('13','945.00','0')
insert t(rowseq_num,mynjuk,dnum) values('14','162.00','0')
insert t(rowseq_num,mynjuk,dnum) values('15','71.50','19')
insert t(rowseq_num,mynjuk,dnum) values('16','39.00','0')
insert t(rowseq_num,mynjuk,dnum) values('17','156.00','0')
insert t(rowseq_num,mynjuk,dnum) values('18','1036.00','20')
insert t(rowseq_num,mynjuk,dnum) values('19','396.00','21')

select 
    b.min_seq
,   b.max_seq
,   sum(a.mynjuk) sum_mynjuk
from t a
    inner join (
        select 
            min(rowseq_num) min_seq
        ,   case when min(rowseq_num) = max(rowseq_num) then max(rowseq_num) else max(rowseq_num)-1 end max_seq
        from t
            cross join (select 1 seq union all select 2) b
        where dnum <> 0
        group by
            case when b.seq = 2 then dnum - 1 else dnum end
        having min(case when b.seq = 1 then rowseq_num end) is not null
    ) b
        on a.rowseq_num between b.min_seq and b.max_seq
group by
    b.min_seq
,   b.max_seq
}}}

--이렇게 하는게 더 좋다.
{{{

select
    min(rowseq_num) min_seq
,   max(rowseq_num) max_seq
,   sum(mynjuk) sum_val
from (
    select
        rowseq_num
    ,   mynjuk
    ,   dnum 
    ,   case 
            when grp = 1 then row_number() over(partition by grp order by rownum)
            else rownum - row_number() over(partition by grp order by rownum)
        end grp
    from (
        select
            rowseq_num
        ,   mynjuk
        ,   dnum    
        ,   case when dnum = 0 then 0 else 1 end grp
        ,   row_number() over(order by (select 1)) rownum
        from t
    ) t    
) t
group by
    grp    
order by 1
}}}

==== 예제2 ====
{{{
with data(val)
as
(
    select 0 val union all
    select -1 union all
    select -1 union all
    select -2 union all
    select 0 union all
    select -300 union all
    select 0 union all
    select -1 union all
    select -500 union all
    select 0 union all
    select -1 union all
    select -100 union all
    select 0 union all
    select -1 union all
    select -1
), rs
as
(
    select
        val
    ,   case when val <= -30 then 1 else 0 end grp
    ,   row_number() over(order by (select 1)) seq
    from data
)
select
    val
,   grp
,   seq
,   row_number() over(partition by grp order by seq) grp_seq
,   seq - row_number() over(partition by grp order by seq) [seq - grp_seq]
,   case 
        when grp = 1 then row_number() over(partition by grp order by seq)
        else seq - row_number() over(partition by grp order by seq) 
    end grp
from rs
order by seq

/*
val         grp         seq                  grp_seq              seq - grp_seq        grp
----------- ----------- -------------------- -------------------- -------------------- --------------------
0           0           1                    1                    0                    0
-1          0           2                    2                    0                    0
-1          0           3                    3                    0                    0
-2          0           4                    4                    0                    0
0           0           5                    5                    0                    0
-300        1           6                    1                    5                    1 --> 여기는 포함 안함
0           0           7                    6                    1                    1
-1          0           8                    7                    1                    1
-500        1           9                    2                    7                    2
0           0           10                   8                    2                    2
-1          0           11                   9                    2                    2
-100        1           12                   3                    9                    3
0           0           13                   10                   3                    3
-1          0           14                   11                   3                    3
-1          0           15                   12                   3                    3
*/
}}}

{{{
;with data(val)
as
(
    select 0 val union all
    select -1 union all
    select -1 union all
    select -2 union all
    select 0 union all
    select -300 union all
    select 0 union all
    select -1 union all
    select -500 union all
    select 0 union all
    select -1 union all
    select -100 union all
    select 0 union all
    select -1 union all
    select -1
), rs
as
(
    select
        val
    ,   case when val <= -30 then 1 else 0 end grp
    ,   row_number() over(order by (select 1)) seq
    from data
)
select
    val
,   grp
,   seq
,   row_number() over(partition by grp order by seq) grp_seq
,   seq - row_number() over(partition by grp order by seq) [seq - grp_seq]
,   case 
        when grp = 1 then row_number() over(partition by grp order by seq)-1   --요기가 다른 부분
        else seq - row_number() over(partition by grp order by seq) 
    end grp
from rs
order by seq

/*
val         grp         seq                  grp_seq              seq - grp_seq        grp
----------- ----------- -------------------- -------------------- -------------------- --------------------
0           0           1                    1                    0                    0
-1          0           2                    2                    0                    0
-1          0           3                    3                    0                    0
-2          0           4                    4                    0                    0
0           0           5                    5                    0                    0
-300        1           6                    1                    5                    0   ---> 여기까지 포함
0           0           7                    6                    1                    1
-1          0           8                    7                    1                    1
-500        1           9                    2                    7                    1
0           0           10                   8                    2                    2
-1          0           11                   9                    2                    2
-100        1           12                   3                    9                    2
0           0           13                   10                   3                    3
-1          0           14                   11                   3                    3
-1          0           15                   12                   3                    3
*/
}}}