oracle 如何统计时间间隔都小于3个月的记录数？

Question

表结构A， 字段A1为用户姓名，A2为操作时间

要求：统计操作时间间隔都小于3个月的用户数量，只要有一条间隔大于3个月就不满足

用这个语句搞定：

select A1 from INFECTIOUS_DISEASE_MARKERS a where A1 not in(
       select A1 from(
              select A1,
              months_between(A2,Lag(A2, 1) over(partition by x.A1 order by A2)) mon
              from A x 
       )  where mon>3
)

Answer 1

rownumber按用户姓名分组排序

按排序计算本次与上一次的时间间隔

取出所有有间隔大于3个月的用户

取出所有用户并排除上表中的用户

;with t(a1,a2) as (
	select 'AAAA',getdate()
	union all select 'a',dateadd(day,-1,getdate())
	union all select 'b',dateadd(day,-1,getdate())
	union all select 'a',dateadd(day,-10,getdate())
	union all select 'b',dateadd(day,-31,getdate())
	union all select 'a',dateadd(day,-51,getdate())
	union all select 'b',dateadd(day,-14,getdate())
	union all select 'a',dateadd(day,-13,getdate())
	union all select 'b',dateadd(day,-14,getdate())
	union all select 'a',dateadd(day,-12,getdate())
	union all select 'b',dateadd(day,-11,getdate())
	union all select 'a',dateadd(day,-121,getdate())
	union all select 'b',dateadd(day,-31,getdate())
	union all select 'a',dateadd(day,-551,getdate())
	union all select 'b',dateadd(day,-321,getdate())
),t1 as (
	
	select *
		-- 按姓名分组，各自按照时间排列并编号
		,row_number() over(partition by a1 order by a2) as rid 
	from t
),t2 as (
	select *,0 as diff 
	from t1 
	where rid=1
	union all
	select a.*,datediff(day,b.a2,a.a2) 
	from t1 a,t2 b
	where a.rid=b.rid+1 and a.a1=b.a1
)
select * 
from t2 
order by a1,rid