select 号码,时间 into #t from tablename where 指令='702' order by 号码,时间 select distinct a.号码 from #t as a left join #t as b on a.号码=b.号码 and datediff(dd,a.时间,b.时间)=1 left join #t as c on a.号码=c.号码 and datediff(dd,a.时间,c.时间)=2 where b.号码 is not null and c.号码 is not null drop table #t 不知道上面的sql可不可以。
WITH a1 (號碼,區號,指令,時間) AS ( SELECT '13688888888','0769','702','20140903 08:37:44' UNION ALL SELECT '13688888888','0769','702','20140904 08:37:44' UNION ALL SELECT '13688888888','0769','702','20140905 08:37:44' UNION ALL SELECT '13666666666','0769','702','20140903 08:37:44' ) ,a2 AS ( SELECT DISTINCT 號碼,LEFT(時間,8) 時間 FROM a1 WHERE 時間>='20140901' AND 時間<'20141001' AND 指令='702' ) ,a3 AS ( SELECT 號碼,時間,DATEADD(day,-ROW_NUMBER() OVER(PARTITION BY 號碼 ORDER BY 時間),時間) 時間2 FROM a2 ) ,a4 AS ( SELECT 號碼,時間2,COUNT(*) n FROM a3 GROUP BY 號碼,時間2 HAVING COUNT(*)>=3 ) SELECT 號碼,DATEADD(day,1,時間2) 開始時間,DATEADD(day,n,時間2) 結束時間,n 连续n天重复出现的号码 FROM a4
我想到的一个思路是用GROUP BY 号码安装时间升序加ROW_NUMBER, 然后用个递归CTE ,里面用CASE 判断。如果时间递增1ROW_number+1 反之社会自为1.这样可以标记他连续出洗几次。with t as (select 号码,时间 , row_number() over (partition by 号码 order by 时间) as n from tablename where 指令=702)with cte as (select 号码,时间,n ,1 as level from t where n=1 union all select t1.号码,t1.时间,t1.n , case when t1.时间=t.时间+1 then t1.level=t.level+1 else 1 end as level from t join cte as t1 on t.号码=t1.号码 t.n=t1.n+1) --用LEVEL 判断。如果下一个时间是递增1,就把LEVEL+1 否则重置为1. 最后CTE的结果 每个号码 对于的LEVEL 最大值 就表示这个号码联系天数 出现的次数。 select * from cte where level>=3.
也可以通过如下查询,当前数据在提前天和后一条有没有对应的数据。当然如果连续天数太多要换种写法。 如果仅是查对应的WITH a1 (PhoneNo,RegionCode,ServiceNo,CallDate) AS ( SELECT '13688888888','0769','702',dateadd(d,-1, GETDATE()) UNION ALL SELECT '13688888888','0769','702',dateadd(d,-2, GETDATE()) UNION ALL SELECT '13688888888','0769','702',dateadd(d,0, GETDATE()) UNION ALL SELECT '13666666666','0769','702',dateadd(d,+1, GETDATE()) ) SELECT * INTo PhoneCall FROM a1 SELECT distinct * FROM PhoneCall AS pc WHERE EXISTS (SELECT 0 FROM PhoneCall AS p WHERE p.PhoneNo=pc.PhoneNo AND p.ServiceNo=pc.ServiceNo AND DATEDIFF(d,p.CallDate,pc.CallDate)=1) AND EXISTS (SELECT 0 FROM PhoneCall AS p WHERE p.PhoneNo=pc.PhoneNo AND p.ServiceNo=pc.ServiceNo AND DATEDIFF(d,p.CallDate,pc.CallDate)=-1)
如果要统计连续天数不固定,还有另外一种办法: 以某个日期常量为参照点,计算每笔记录和此日期的间隔天数,然后在减去行号,如果天数是连续的,那么两行之间行号的增加量和计算出的时间间隔数增量是相同的,则说明两者是连续的,如:SELECT t.d-t.rid, t.PhoneNo FROM ( SELECT DATEDIFF(d,GETDATE(),calldate) AS d,ROW_NUMBER () OVER (ORDER BY calldate) AS rid, * FROM PhoneCall ) t GROUP BY t.d-t.rid,t.PhoneNo HAVING count(0)>2统计的连续天数条件HAVING count(0)>2
select distinct a.号码 from #t as a
left join #t as b on a.号码=b.号码 and datediff(dd,a.时间,b.时间)=1
left join #t as c on a.号码=c.号码 and datediff(dd,a.时间,c.时间)=2
where b.号码 is not null and c.号码 is not null
drop table #t
不知道上面的sql可不可以。
WITH a1 (號碼,區號,指令,時間) AS
(
SELECT '13688888888','0769','702','20140903 08:37:44' UNION ALL
SELECT '13688888888','0769','702','20140904 08:37:44' UNION ALL
SELECT '13688888888','0769','702','20140905 08:37:44' UNION ALL
SELECT '13666666666','0769','702','20140903 08:37:44'
)
,a2 AS
(
SELECT DISTINCT 號碼,LEFT(時間,8) 時間
FROM a1
WHERE 時間>='20140901' AND 時間<'20141001' AND 指令='702'
)
,a3 AS
(
SELECT 號碼,時間,DATEADD(day,-ROW_NUMBER() OVER(PARTITION BY 號碼 ORDER BY 時間),時間) 時間2
FROM a2
)
,a4 AS
(
SELECT 號碼,時間2,COUNT(*) n
FROM a3
GROUP BY 號碼,時間2
HAVING COUNT(*)>=3
)
SELECT 號碼,DATEADD(day,1,時間2) 開始時間,DATEADD(day,n,時間2) 結束時間,n 连续n天重复出现的号码
FROM a4
然后用个递归CTE ,里面用CASE 判断。如果时间递增1ROW_number+1 反之社会自为1.这样可以标记他连续出洗几次。with t as
(select 号码,时间 , row_number() over (partition by 号码 order by 时间) as n from tablename where 指令=702)with cte as
(select 号码,时间,n ,1 as level from t where n=1
union all
select t1.号码,t1.时间,t1.n ,
case when t1.时间=t.时间+1 then t1.level=t.level+1
else 1 end as level from t join cte as t1 on t.号码=t1.号码 t.n=t1.n+1)
--用LEVEL 判断。如果下一个时间是递增1,就把LEVEL+1 否则重置为1. 最后CTE的结果
每个号码 对于的LEVEL 最大值 就表示这个号码联系天数 出现的次数。
select * from cte where level>=3.
如果仅是查对应的WITH a1 (PhoneNo,RegionCode,ServiceNo,CallDate) AS
(
SELECT '13688888888','0769','702',dateadd(d,-1, GETDATE()) UNION ALL
SELECT '13688888888','0769','702',dateadd(d,-2, GETDATE()) UNION ALL
SELECT '13688888888','0769','702',dateadd(d,0, GETDATE()) UNION ALL
SELECT '13666666666','0769','702',dateadd(d,+1, GETDATE())
)
SELECT * INTo PhoneCall FROM a1
SELECT distinct * FROM PhoneCall AS pc WHERE EXISTS (SELECT 0 FROM PhoneCall AS p WHERE p.PhoneNo=pc.PhoneNo AND p.ServiceNo=pc.ServiceNo AND
DATEDIFF(d,p.CallDate,pc.CallDate)=1)
AND EXISTS (SELECT 0 FROM PhoneCall AS p WHERE p.PhoneNo=pc.PhoneNo AND p.ServiceNo=pc.ServiceNo AND DATEDIFF(d,p.CallDate,pc.CallDate)=-1)
以某个日期常量为参照点,计算每笔记录和此日期的间隔天数,然后在减去行号,如果天数是连续的,那么两行之间行号的增加量和计算出的时间间隔数增量是相同的,则说明两者是连续的,如:SELECT t.d-t.rid, t.PhoneNo FROM (
SELECT DATEDIFF(d,GETDATE(),calldate) AS d,ROW_NUMBER () OVER (ORDER BY calldate) AS rid, * FROM PhoneCall
) t GROUP BY t.d-t.rid,t.PhoneNo HAVING count(0)>2统计的连续天数条件HAVING count(0)>2