按日期范围划分的SQL分区

你可以用 lag 功能:

select *, row_number() over (partition by number, grp order by id) as [ROWNUMBER]
from (select *, (case when datediff(day, lag(date,1,date) over (partition by number order by id), date) <= 1 
                      then 1 else 2 
                 end) as grp
      from table
     ) t;

我想你想确定 组 ,使用 lag() 和 datediff() 一笔累积的金额。 那么 使用 row_number() :

select t.*,
       row_number() over (partition by number, grp order by date) as rownumber
from (select t.*,
             sum(grp_start) over (partition by number order by date) as grp
      from (select t.*,
                   (case when lag(date) over (partition by number order by date) < dateadd(day, 5, date)
                            then 1 else 0
                    end) as grp_start
            from t
           ) t
     ) t;

通过使用 lag 和 datediff 功能

select * from
    (
    select t.*,
           datediff(day,
                    lag(DATE) over (partition by NUMBER order by id),
                    DATE
                   ) as diff
    from  t 
      ) as TT where  diff>5

http://sqlfiddle.com/#!18/130ae/11