分享解决连续时间问题的方法

Question

--最近在论坛看到过很多关于解决连续时间问题的帖子。关于连续时间问题其实也可以归于孤岛问题。
关于孤岛问题的解决方案我之前发表过一篇帖子，链接如下：http://topic.csdn.net/u/20120325/17/5a53bd46-8870-450a-a9ca-7ef8661e638d.html--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案，下面我以一种方法实现
/*
name logindate
a1 2011-1-2
a1 2011-1-3
a1 2011-1-4
a1 2011-1-7
a1 2011-1-12
a1 2011-1-13
a1 2011-1-16
a2 2011-1-7
a2 2011-1-8
a2 2011-1-10
a2 2011-1-11
a2 2011-1-13
a2 2011-1-24
---------------------------------------------
我需要的结果是：
name start_day end_day logindays
a1 2011-1-2 2011-1-4 3
a2 2011-1-7 2011-1-8 2
a2 2011-1-10 2011-1-11 2
*/
--------------------------------------------
--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(2),[logindate] date)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'with t as(
select [name],[logindate],(select min(b.[logindate]) from tbl b 
where b.[logindate]>=a.[logindate] and b.name=a.name
and not exists (select * from tbl c
where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp
from tbl a
),m
as(
select [name],MIN([logindate]) as start_day,MAX(grp) as end_day
from t group by grp,name
)
select *,(DATEDIFF(DD,start_day,end_day)+1) as logindays from m a 
where (DATEDIFF(DD,start_day,end_day)+1) in(
select max(DATEDIFF(DD,start_day,end_day)+1) from m b
where a.name=b.name)
-------------------------------------------------------------
/*
name start_day end_day logindays
a1 2011-01-02 2011-01-04 3
a2 2011-01-07 2011-01-08 2
a2 2011-01-10 2011-01-11 2
*/
-----------------------------------------------------------------
希望能看到有人写出给多的方法哦。谢谢阅读

Answer 1

create table tb1([name] varchar(2),[logindate] datetime)
insert tb1
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'
godeclare @date datetime
select @date = min(logindate) from tb1;with ach as
(
select [name],logindate,id=row_number() over (partition by [name] order by logindate)
from tb1
)select [name],min(logindate) mindate,max(logindate) maxdate,
datediff(dd,min(logindate),max(logindate)) dddate
from ach
group by [name],datediff(dd,@date,logindate)-id
order by [name],mindatedrop table tb1/******************************name mindate                 maxdate                 dddate
---- ----------------------- ----------------------- -----------
a1   2011-01-02 00:00:00.000 2011-01-04 00:00:00.000 2
a1   2011-01-07 00:00:00.000 2011-01-07 00:00:00.000 0
a1   2011-01-12 00:00:00.000 2011-01-13 00:00:00.000 1
a1   2011-01-16 00:00:00.000 2011-01-16 00:00:00.000 0
a2   2011-01-07 00:00:00.000 2011-01-08 00:00:00.000 1
a2   2011-01-10 00:00:00.000 2011-01-11 00:00:00.000 1
a2   2011-01-13 00:00:00.000 2011-01-13 00:00:00.000 0
a2   2011-01-24 00:00:00.000 2011-01-24 00:00:00.000 0(8 行受影响)

Answer 2

--楼主的结果确实少一条？
declare @T table (name varchar(2),logindate datetime)
insert into @T
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24';with maco as
(
select 
row_number () over (partition by name order by logindate-getdate()) as no,
datediff(d,getdate(),logindate) as num,* 
from @T
)select 
name,
convert(varchar(10),min(logindate),120) as start_day,
convert(varchar(10),max(logindate),120) as end_day,
datediff(d,min(logindate),max(logindate)) +1 as logindays
from maco
group by name,num-no having(min(logindate)<>max(logindate))
order by 1
/*
name start_day  end_day    logindays
---- ---------- ---------- -----------
a1   2011-01-02 2011-01-04 3
a1   2011-01-12 2011-01-13 2
a2   2011-01-07 2011-01-08 2
a2   2011-01-10 2011-01-11 2
*/

Answer 3

叶子哥哥，不是我的少了一条，而是你的多了一条，因为最后的结果是每个name下的最大连续，所以a1就只有3，为2那个将会被排除

Answer 4

;with t as
(
select name,[logindate],dateadd(dd,
-row_number()over(partition by name 
order by [logindate]),[logindate]) as diff from tbl
),
m as(
select name,min([logindate]) as start_day,max([logindate]) as end_day, 
(datediff(dd,min([logindate]),max([logindate]))+1) as logindays
from t
group by name,diff
)
select * from m a 
where logindays in(select MAX(logindays) from m b 
where a.name=b.name)
/*
name start_day end_day logindays
a1 2011-01-02 2011-01-04 3
a2 2011-01-10 2011-01-11 2
a2 2011-01-07 2011-01-08 2
*/

Answer 5

--承接‘孤岛方法’
;with  
A
as
(
select name,logindate,dateadd(dd,-row_number()over(partition by name order by logindate),logindate) as diffday  
from logintable
),
B  
as
(
select name,min(logindate) as start_day,max(logindate) as end_day,(datediff(dd,min(logindate),max(logindate))+1) as logindays
from A
group by name,diffday
)select name,start_day,end_day,logindays
into tb
from
(
select name,start_day,end_day,logindays,row_number()over(partition by name order by logindays desc) as rin
from B  
 ) fin
  where fin.rin=1
  
 select * from tb/*
name start_day end_day logindays
a0 2011-01-29 00:00:00 2011-01-30 00:00:00 2
a1 2011-01-27 00:00:00 2011-01-28 00:00:00 2
a10 2011-01-19 00:00:00 2011-01-20 00:00:00 2
a100 2011-01-27 00:00:00 2011-01-29 00:00:00 3
*/

Answer 6

有关孤岛、间断问题，包括时间，大家可以看下《Microsoft SQL Server 2008技术内幕:T-SQL查询
》这本书，讲解的非常详细。

Answer 7

CREATE TABLE A
(
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL
)GOINSERT INTO A 
SELECT 1,'haha' UNION
SELECT 2,'hoho' UNION
SELECT 3,'hehe' UNION
SELECT 4,'hihi'
CREATE TABLE B
(
ID INT NOT NULL,
ShoppingDate VARCHAR(100)
)GOINSERT INTO B
SELECT 1,'2012-04-01 14:00:00' UNION
SELECT 1,'2012-04-01 13:00:00' UNION
SELECT 1,'2012-04-03 12:00:00' UNION
SELECT 1,'2012-04-04 12:00:00' UNION
SELECT 1,'2012-04-05 12:00:00' UNION
SELECT 1,'2012-04-06 12:00:00' UNION
SELECT 1,'2012-04-07 12:00:00' UNION
SELECT 1,'2012-04-08 12:00:00' UNION
SELECT 1,'2012-04-10 12:00:00' UNION
SELECT 1,'2012-04-11 12:00:00' UNION
SELECT 1,'2012-04-13 12:00:00' UNION
SELECT 1,'2012-04-14 12:00:00' UNION
SELECT 1,'2012-04-15 12:00:00' UNION
SELECT 1,'2012-04-16 12:00:00' UNION
SELECT 1,'2012-04-17 12:00:00' UNION
SELECT 1,'2012-04-18 12:00:00' UNION
SELECT 1,'2012-04-19 12:00:00' UNION
SELECT 1,'2012-04-20 12:00:00' UNION
SELECT 1,'2012-04-21 12:00:00' UNION
SELECT 1,'2012-04-22 12:00:00' UNION
SELECT 1,'2012-04-23 12:00:00' UNION
SELECT 1,'2012-04-24 12:00:00' UNION
SELECT 1,'2012-04-25 12:00:00' UNION
SELECT 1,'2012-04-26 12:00:00' UNION
SELECT 1,'2012-04-27 12:00:00' UNION
SELECT 3,'2012-04-01 12:00:00' UNION
SELECT 3,'2012-04-02 12:00:00' UNION
SELECT 3,'2012-04-03 12:00:00' UNION
SELECT 3,'2012-04-04 12:00:00' UNION
SELECT 3,'2012-04-05 12:00:00' UNION
SELECT 3,'2012-04-06 12:00:00' UNION
SELECT 3,'2012-04-07 12:00:00' UNION
SELECT 3,'2012-04-08 12:00:00' UNION
SELECT 2,'2012-04-10 12:00:00' UNION
SELECT 2,'2012-04-11 12:00:00' UNION
SELECT 4,'2012-04-13 12:00:00' UNION
SELECT 4,'2012-04-14 12:00:00' UNION
SELECT 4,'2012-04-15 12:00:00' UNION
SELECT 4,'2012-04-16 12:00:00' UNION
SELECT 4,'2012-04-17 12:00:00' UNION
SELECT 4,'2012-04-18 12:00:00' UNION
SELECT 4,'2012-04-19 12:00:00' UNION
SELECT 4,'2012-04-20 12:00:00' UNION
SELECT 4,'2012-04-21 12:00:00' UNION
SELECT 4,'2012-04-22 12:00:00' UNION
SELECT 4,'2012-04-23 12:00:00' UNION
SELECT 4,'2012-04-24 12:00:00' UNION
SELECT 4,'2012-04-25 12:00:00' UNION
SELECT 4,'2012-04-26 12:00:00' UNION
SELECT 4,'2012-04-27 12:00:00' SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID)
FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM (SELECT DISTINCT ID,CONVERT(VARCHAR(10),shoppingdate,23) AS shoppingdate FROM B) AS D) AS C,A
WHERE C.ID = A.ID
GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate)
HAVING COUNT(C.ID) > 6
ORDER BY C.ID

Answer 8

原本的要求没有求起止时间。是XBOX数据分析中的一小部分。。

Answer 9

原本的要求没有求起止时间。是XBOX数据分析中的一小部分。。