查找过去4个月内不存在的当月唯一值的计数

Question

我想统计过去4个月没有注册的特定月份的唯一ID。目前，我正在使用这个查询，它没有考虑某些子情况，对于大型数据库来说效率很低

select month(dd/mm/yyyy), (count(distinct ID) - sum(l)) 
from
(select x.ID, x.dd/mm/yyyy, y.ID as ID2, y.dd/mm/yyyy as Date2, (case when x.ID = y.ID then 1 else 0 end as l) from table1 x
left join table1 y 
on y.dd/mm/yyyy between dateadd(month,-4, x.dd/mm/yyyy) and dateadd(month,-1, x.dd/mm/yyyy))
group by month(dd/mm/yyyy) 
order by month(dd/mm/yyyy);
(dd/mm/yyyy) ID  
01/01/2018  A  
01/02/2018  B  
02/02/2018  C  
01/03/2018  A    
02/03/2018  D  
01/04/2018  D  
02/04/2018  E        
01/05/2018  E         
02/05/2018  A    
01/05/2018  F        
01/05/2018  G    

预期结果-

Month Count
1      1    --(A)
2      2    --(B,C)
3      1    --(D)
4      1    --(E)
5      2    --(F,G)

Answer 1

您可以按月聚合，然后使用lag()查看上一个值的位置：

select yyyymm,
sum(case when prev_yyyymm is null or
prev_yyyymm < dateadd(month, -3, yyyymm)
then 1 else 0
end) as cnt
from (select t.id, v.yyyymm,
count(*) as cnt,  -- not needed but perhaps fun to have
lag(v.yyyymm) over (partition by t.id order by v.yyyymm) as prev_yyyymm
from t cross apply
(values (datefromparts(year(date_filled), month(date_filled), 1)
) v(yyyymm)
) t
group by yyyymm
order by yyyymm;